Home/Support/Support Forum/Converting csv to json
Welcome to Digi Forum, where you can ask questions and receive answers from other members of the community.

Converting csv to json

0 votes
import pandas as pd
import datetime
from pandas_datareader import data, wb
import csv
import json
#declaring libraries for the analysis
out= open("testfile.csv", "rb") # read the .csv file
data = csv.reader(out)
data = row[0],row[1] + "_" + row[2],row[3] +"_" + row[4], row[5],row[6 for row in data] #this part is mainly to concatenate the City and country
out.close()

out=open("data.csv", "wb")
output = csv.writer(out)
for row in data:
output.writerow(row)
out.close()
#<
End of concatenation
>
#The result is written out and saved in data.csv (I don't want to override the original source)
df = pd.read_csv('data.csv') #using pandas and dataframe, I can read in data and loop it through between the Arrival Date and Departure Date

df.DateDpt = pd.to_datetime(df.DateDpt)
df.DateAr = pd.to_datetime(df.DateAr)
df = df.set_index('DateAr')
new_df = pd.DataFrame()
for i, data in df.iterrows():
data = data.to_frame().transpose()
data = data.reindex(pd.date_range(start=data.index[0], end=data.DateDpt[0])).fillna(method='ffill').reset_index().rename(columns={'index': 'DateAr'})
new_df = pd.concat([new_df, data])

new_df = new_df'AuthorID', 'ArCity_ArCountry', 'DptCity_DptCountry', 'DateAr', 'DateDpt'

print new_df
# <---- End of iteration ---->
#Json file : <
JSON
>
json_dict = {}

for arrival_date, data in new_df.groupby('DateAr'):
matching_dates = data[data.DptCity_DptCountry==arrival_date]
not_matching_dates = data[data.DptCity_DptCountry!=arrival_date]
json_dict[arrival_date.strftime('%Y-%m-%d')] = {}
if not matching_dates.empty:
for city, flights in matching_dates.groupby('ArCity_ArCountry'):
json_dict[arrival_date.strftime('%Y-%m-%d')][city] = [str(v) for v in flights.AuthorID.to_dict().values()]
if not not_matching_dates.empty:
for city, flights in not_matching_dates.groupby('DptCity_DptCountry'):
json_dict[arrival_date.strftime('%Y-%m-%d')][city] = [str(v) for v in flights.set_index('ArCity_ArCountry').to_dict().values()]


with open('json_dict.json', 'w') as f:
json.dump(json_dict, f, indent=4, sort_keys=True)

#print(json.dumps(json_dict, indent=4, sort_keys=True)) #: <
End of JSON
>


CVS file
ID Arrival Departure ArrivalDate DepatureDate
1001 New York Holland 2009-09-23 2012-07-23
1301 Florida Germany 2010-10-23 2012-10-11
1401 New York Holland 2009-09-23 2009-09-25
1301 New York Beijing 2009-09-23 2010-09-21
1201 New York Holland 2008-01-01 2009-09-23
1001 Virginia New York 2008-01-01 2009-09-22
1021 New York Holland 2009-09-23 2009-09-25
1001 New York Holland 2009-09-24 2012-07-23
1021 New York Holland 2009-09-26 2012-07-23
1001 New York Holland 2009-09-25 2012-07-23
….... ......... ........ .............. ...........

1001 New York Holland 2012-07-23 2012-07-23
1401 New York Holland 2009-09-25 2009-09-25
1301 New York Beijing 2010-09-21 2010-09-21
1201 New York Holland 2009-09-23 2009-09-23
1001 Virginia New York 2009-09-22 2009-09-22
1021 New York Holland 2009-09-25 2009-09-25
1001 New York Holland 2012-07-23 2012-07-23
1021 New York Holland 2012-07-23 2012-07-23
1001 New York Holland 2012-07-23 2012-07-23


Desired Output

{
"2008-01-01": {
"Holland": [
"1201"
],
"New York": [
"1001"
]
},
"2009-09-22": {
"Virginia": [
"1001"
]
},
"2009-09-23": {
"Beijing": [
"1301"
],
"Holland": [
"1001",
"1401",
"1021"
],
"New York": [
"1201"
]
},
"2009-09-24": {
"Holland": [
"1001"
]
},
"2009-09-25": {
"Holland": [
"1001"
],
"New York": [
"1021",
"1001",
"1401"
]
},
"2009-09-26": {
"Holland": [
"1021"
]
},
"2010-09-21": {
"New York": [
"1301"
]
},
"2010-10-23": {
"Germany": [
"1301"
]
},
"2012-07-23": {
"New York": [
"1001",
"1001",
"1021",
"1001"
]
}
}
asked Jan 28, 2016 in Python by tollycoast New to the Community (0 points)

Please log in or register to answer this question.

...