Converting csv to json

Python
, ,
1

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”
]
}
}