CODEWITHSUNDEEP
pythoncsvdictionary
Parker
Parker

Reputation: 303

python write dictionary from csv file

I am now struggling with writing a dictionary from a csv file.

The format of csv file is like:

student,    Test 1, Test 2, Test 3, Final Exam
A,          9,      19,    9,       22
B,          10,     16,    9,       26
C,          11,     17,    8,       27
D,          7,      14,    9,       18
E,          8,      20,    8,       23
weight,     0.15,   0.25,  0.2,     0.4
max_points  12      20     9        30

Where the 2-6 rows are students' names, their test scores on each test. And the last two rows represent weight of each test and full score of each test seperately.

Now, I want to create a dictionary from this list that looks like:

{'Test 1': {'weight': '0.15', 'max_points': '12'}, 
'Test 2': {'weight': '0.25', 'max_points': '20'}, 
'Test 3': {'weight': '0.2',   'max_points': '9'}, 
'Final Exam': {'weight': '0.4', 'max_points': '30'}}

Where the keys are the variables of the first row in the csv file except the variable students; and in each nested dictionary, keys are the names of the first column and last two rows in the csv file: weight, max_points. The corresponding values are just values in their rows respectively.

The only thing I have come up with by now is:

reader = csv.DictReader(open('gradebook.csv'))
for row in reader:
    key = row.pop('Student')

And I have no idea about how to proceed. Thank you so much for help!!!

Upvotes: 0

Views: 495

Answers (2)

e4c5
e4c5

Reputation: 53734

Use Pandas, it's a one liner

import pandas as pd

df = pandas.read_csv('myfile.csv', delim_whitespace=True)
{ k: { 'max_points': df[k].max(), 'weight': df[k][5] } for k in df.keys()[1:] }

Edit. Opps, I see taht the OP isn't actually look for max()

{ k: { 'max_points': df[k][6], 'weight': df[k][5] } for k in df.keys()[1:] }

By the way if Pandas doesn't recognize your headers properly

df = pd.read_csv('/tmp/df.txt',delim_whitespace=True, header=1, names=['Student','Test 1','Test 2','Test 3','Final Score'])

Upvotes: 3

Josh Smeaton
Josh Smeaton

Reputation: 48710

Here's a solution not using pandas that should do what you want. Note though that my csv file is an actual csv file, so you may need to adjust the reader creation accordingly.

In [13]: reader = csv.DictReader(open('tests.csv'))

In [14]: record = defaultdict(dict)

In [15]: for row in reader:
    ...:    if row['Student'] == 'weight':
    ...:        for header in reader.fieldnames[1:]:
    ...:            record[header]['weight'] = row[header]
    ...:    if row['Student'] == 'max_points':
    ...:        for header in reader.fieldnames[1:]:
    ...:            record[header]['max_points'] = row[header]


In [17]: from pprint import pprint

In [18]: pprint(record)
defaultdict(<class 'dict'>,
            {'Final Exam': {'max_points': '30', 'weight': '0.4'},
             'Test 1': {'max_points': '12', 'weight': '0.15'},
             'Test 2': {'max_points': '20', 'weight': '0.25'},
             'Test 3': {'max_points': '9', 'weight': '0.2'}})

If you haven't seen defaultdict before, whatever you pass to the constructor is what is used as the value when you try to access a key that doesn't yet exist.

Upvotes: 2

Related Questions