Create A Dictionary Using The Row Number In A Csv File [Python]

I have a CSV file containing survey data on 60 participants. The first column is the participant's number, and for each number corresponds all the data collected from that particip

Solution 1:

You can try this?

import csv
with open('surveys.csv', 'r') as f:
    reader = csv.reader(f, delimiter=' ') 
    mydict={}
    iterreader = iter(reader)
    next(iterreader)
    for row in iterreader:
        elementsList=row[0].split("\t")
        nonEmptyElements=[]
        for element in elementsList[1:]:
            print(element)          
            if(not element.strip()==""):
                nonEmptyElements.append(element)
        valuesList=",".join(nonEmptyElements)
        mydict[elementsList[0]]=valuesList
print(mydict)  

My CSV looks like this

Participant Name    Gender
1   Rupin   Male
2   Poonam  Female
3   Jeshan  Male

The code avoids using the first row.

My output looks like this

{'1': 'Rupin,Male', '2': 'Poonam,Female', '3': 'Jeshan,Male'}

Solution 2:

From the comments, we know the first 100 bytes of the raw file are:

b'\xef\xbb\xbf"\nParticipant/Question","1. Gender\n","2. Level of study\n","3. How often visit SC\n","4. Time of vi' 

This looks like a csv export from Excel, with embedded newlines in the cells. The initial b'\xef\xbb\xbf' is a byte order mark, indicating that the bytes are encoded as 'utf-8-sig'.

Based on this information, this code should create the desired dictionary:

with open('surveys.csv', 'r', encoding='utf-8-sig') as f:
    reader = csv.reader(f, dialect='excel')
    # Advance the iterator to skip the header row
    next(reader)
    mydict = {row[0]:row for row in reader}
print(mydict)

Passing the 'utf-8-sig' encoding ensures that the byte-order-mark doesn't get treated as part of the data. It's probably a good idea to set this encoding when reading and writing csv files if you are working with Excel.

Passing dialect='excel' to the reader tells it to use the defaults associated with csv files created by Excel, such as using a comma as the delimiter.