Converting csv columns to rows with python

Question

I'm trying to create a list that will take data from gradebook.csv and change it to a different format.

gradebook.csv has columns like with data:

{ studentID , assignment1 , assignment2 , . . . assignmentN }
{ 2343      ,             ,   34.2      ,                   }

empty cells are assignments with no submission. Cells with numbers represent grades. This data will go into a list of the format:

{ studentID , assignmentid , grade }
{ 2343      , assignment2  , 34.2  }

the end result will be putting this data into a sql table in sql server 2012. The code below does what I want, but only for the last row in gradebook.csv. What am I doing wrong?

import csv

with open('gradebook.csv','rb') as g:
        gr=csv.reader(g)
        for row in gr:
            if row[0]=='User ID':
                pass
            else:
                studentid=row[0]
                individualassignments=[]
                everyone=[]
                for element in row[1:]:
                    if element=='':
                        pass
                    else:
                        individualassignments.append(element)

                individualassignments.append(studentid)
                everyone.append(individualassignments)

possible duplicate of [Python, transposing a list and writing to a CSV file](http://stackoverflow.com/questions/10573915/python-transposing-a-list-and-writing-to-a-csv-file) — abarnert, Jul 03 '13 at 21:45
I don't think it is a duplicate. The other question involved writing to a csv file, while this question had a problem with loading the data into a list. — jh314, Jul 03 '13 at 22:03
I feel like this is more than just transposing because I am taking one row and turning it into multiple rows for multiple grades. At least, that is my goal. — mnky9800n, Jul 04 '13 at 14:24

score 4 · Accepted Answer · answered Jul 03 '13 at 21:45

It looks like you clear out the everyone list (by setting everyone=[]) for each iteration of your outer for loop. So only your last row in the csv file will be in the list.

To fix, you need to declare everyone outside of the loop

import csv

with open('gradebook.csv','rb') as g:
    gr=csv.reader(g)
    everyone=[]  # declare everyone here
    for row in gr:
        if row[0]=='User ID':
            pass
        else:
            studentid=row[0]
            individualassignments=[]               
            for element in row[1:]:
                if element=='':
                    pass
                else:
                    individualassignments.append(element)

            individualassignments.append(studentid)
            everyone.append(individualassignments)

This was the obvious answer that I missed on sight. – mnky9800n Jul 03 '13 at 21:48 — mnky9800n, Jul 03 '13 at 21:48

Converting csv columns to rows with python

1 Answers1