joe_92 joe_92 - 3 months ago 11
Python Question

Write a CSV in Multiple rows - Python 2.7

I’m working on a csv file containing data recorders of game players. sample of csv shows 4 players (10 rows) and 13 columns:

Player_ID,Name,Age,DOB,Gender,Game1_result,Date_first_game,Game2_result,`Game3_result,Final_result,Team,Date_last_game,Finals_dates`
101,Ethan,16,1/15/2000,Male,won,3/20/2013,lost,won,lost,yellow,3/20/2013,3/20/2013
101,Ethan,16,1/16/2000,Male,won,12/6/2015,won,won,"won, full",yellow,12/6/2015,12/6/2015
101,Ethan,16,1/17/2000,Male,lost,1/6/2016,won,won,lost,yellow,1/6/2016,1/6/2016
102,Emma,19,6/17/1997,Female,won,1/9/2013,lost,lost,lost,green,1/9/2013,1/9/2013
...........
...........


I create a python script that converts dates to ages and then output the altered file. I use csv reader and writer to read and write the final output file (csv writes from a single list to which all data is appended). The final file should only include 12 columns (name not written) and all date columns converted to ages of players at that specific date.

import csv

##open data containing file

file1=open('example.csv','rb')
reader=csv.reader(file1)
####code to print headers
####age_converter() definition

final_output=[]
for col in reader:
final_output.append(col[0])#appends Player_ID
final_output.append(col[2])#appends Age
final_output.append(age_converter(col[3]))#appends value of date converted to age
for r in range(4,6):
final_output.append(col[r])#appends gender and game1 results
final_output.append(age_converter(col[6]))#appends date of first game
for r in range(7,11):
final_output.append(col[r])#appends game results (5 columns)
for r in range(11,13):
final_output.append(age_converter(col[r]))#appends last and final dates

with open('output.csv','wb')as outfile:
csv_writer=csv.writer(outfile)
csv_writer.writerow(header)
csv_writer.writerow(final_output)


file1.close()
outfile.close()


Age converter works fine, however, the output file has all data in one row. I tried to append columns one by one into a list and write it into csv, which works, but it is not practical to type each one of the columns by its index, especially that the original file I am working on has more than 50 columns!
So my question is: How to write data into multiple rows instead of only one?

output sample:

Player_ID,Age,DOB,Gender,Game1_result,Date_first_game,Game2_result,Game3_result,Final_result,Team,Date_last_game,Finals_dates
101,17,Male,won,20,lost,won,lost,yellow,20,20,101,16,16,Male,won,19,won,won,"won, full",yellow,.....................

Answer

You are appending all the data to final_output. Instead, make it list of lists as so:

for row in reader:
    new_row = []
    new_row.append(row[0])
    ...
    final_output.append(new_row)

And then when writing to file:

csv_writer.writerow(headers)
for row in final_output:
    csv_writer.writerow(row)

Two notes:

  1. Always use with open(...) as somefile. when you do, you don't need to close the file.
  2. Checkout csv DictReader class for easier manipulating.