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 — abarnert
– abarnert, Commented Jul 3, 2013 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
– jh314, Commented Jul 3, 2013 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. — user845888
– user845888, Commented Jul 4, 2013 at 14:24

jh314 · Accepted Answer · 2013-07-03 21:45:26Z

3

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)

answered Jul 3, 2013 at 21:45

jh314

27.9k16 gold badges66 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user845888 Over a year ago

This was the obvious answer that I missed on sight.

Collectives™ on Stack Overflow

Converting csv columns to rows with python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related