I have a csv file, file1.csv, which has following sample data:
state,year,value
Alabama,2012,8.03333333333
Alabama,2012,8.16666666667
Alabama,2012,8.06666666667
Alabama,2012,7.7
Alabama,2013,7.36666666667
Alabama,2013,7.0
Alabama,2013,7.0
Alabama,2013,7.13333333333
Alabama,2014,7.2
Alabama,2014,6.93333333333
Alabama,2014,6.46666666667
Alabama,2014,6.13333333333
Alaska,2012,7.33333333333
Alaska,2012,7.16666666667
Alaska,2012,7.06666666667
Now what I want is to have one record for each state with all the values for that state in one row and then write the result to a new csv file. So the output that I want is something like this:
Alabama,8.0,8.1,8.0,7.7,7.3,7.0,7.0
Alaska,8.1,8.1,8.0,7.4,7.25,7.6,7.5
I wrote this code wherein I form a defaultdict
from the records in input csv file and then write that dictionary to output csv file but when I write it to output the values filed in output file are surrounded by []
and "
and each value itself is surrounded by '
. So the current output looks like:
Indiana,"['8.4', '8.2', '8.2', '8.33333333333', '8.3', '7.8', '7.2', '6.53333333333', '6.1', '5.96666666667', '5.8', '5.86666666667']"
Maryland,"['6.9', '6.9', '6.86666666667', '6.8', '6.76666666667', '6.63333333333', '6.43333333333', '6.2', '5.96666666667', '5.83333333333', '5.7', '5.53333333333']"
My current code is:
file_write = open('output.csv', 'w')
writer = csv.writer(file_write)
with open('file1.csv', 'r') as handle:
reader = csv.DictReader(handle, ['state', 'year', 'value'])
data = defaultdict(list)
for line in reader:
data[line['state']].append(line['value'])
for key, value in data.items():
print key
print value
writer.writerow([key, value])
How can I correct the output format?
To fix your problem you should replace
writer.writerow([key, value])
with
writer.writerow([key] + value)
as Mathias Ettinger wrote in comments.
When you write
[key, value]
you create a list with two elements. First element key
object and second element nested list value
.
Instead of it you need to create a list consists of key
and all elements from value
list.