python csv to json using column as key

Question

Please I need help, I don't want to print header from the CSV file.

csvfile = open('testdata.csv', 'rU')
jsonfile = open('brofile.json', 'w')

fieldnames = {'Matric':[("studentName","dept","status")]}

reader = csv.DictReader( csvfile, fieldnames)

for row in reader:
    json.dump(row, jsonfile)
    jsonfile.write('\n')

The expected output

[2010CS01: { "Jones Doe", "Computer science", "paid"}]
[2010CS02: {"James Rug", "Computer Science", "Paid"}]
[2010AC01: {"Curtis Payne", "Accounting", "unpaid"}]

CSV file:

Matric         Name            Department            Status
2010CS01       Jones Doe       Computer Science      Paid 
2010CS02       James Rug       Computer Science      Paid 
2010AC01       Curtis Payne    Accounting            Unpaid

I'm voting to close this question as off-topic because SO is not a code writing service — bruno desthuilliers
– bruno desthuilliers, Commented Jan 14, 2016 at 8:54

andreas-hofmann · Accepted Answer · 2016-01-15 17:21:06Z

1

You can read the first line in the file with csvfile.readline() before feeding it to the csv-reader. This advances the file-descriptor to the second line (you can check the position with csvfile.tell()) and thus should omit the header in your output.

csvfile.readline()
reader = csv.DictReader(csvfile, fieldnames)

Update:

As mentioned in my comment, your input data has a problem: There is no distinct separator character, which marks the boundaries between the columns (at least in the data you posted - there are only spaces). If you use commas as separator, everything goes smoothly:

Data:

Matric,Name,Department,Status
2010CS01,Jones Doe,Computer Science,Paid
2010CS02,James Rug,Computer Science,Paid
2010AC01,Curtis Payne,Accounting,Unpaid

Code:

import csv, json

csvfile = open('testdata.csv', 'rU')
jsonfile = open('testdata.json', 'w')

reader = csv.DictReader(csvfile)

for row in reader:
    json.dump({ row['Matric'] : (row['Name'], row['Department'], row['Status']) }, jsonfile)
    jsonfile.write('\n')

Output:

{"2010CS01": ["Jones Doe", "Computer Science", "Paid"]}
{"2010CS02": ["James Rug", "Computer Science", "Paid"]}
{"2010AC01": ["Curtis Payne", "Accounting", "Unpaid"]}

Like the other comments stated, your expected output is not valid JSon. This output is.

Convert the input data

Suppose your input data is actually separated by tabs, which have been converted to spaces when you posted them here, you can simply specify tabs as separator when parsing the csv:

reader = csv.DictReader(csvfile, delimiter='\t')

If your input data really has only spaces, you can convert multiple spaces to commas with a simple sed:

sed -e 's/\ \{2,\}/,/g' -i testdata.csv

No guarantee this will work - It will fuck things up if you have multiple spaces anywhere in your data fields.

edited Jan 15, 2016 at 17:21

answered Jan 14, 2016 at 8:55

andreas-hofmann

2,54815 silver badges16 bronze badges

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

3 Comments

Payne Over a year ago

Having the same thing.. it didn't give me the actual format I want.

andreas-hofmann Over a year ago

I see... Tried running it myself, the header is stripped, but you're getting wrong output because your input is wrong. You're parsing a csv (comma separated values), wich does not actually contain commas. You need to specify a separator, which separates the single columns. Does your input data contain tabs or is it all spaces?

Payne Over a year ago

Thanks so much... I appreciate you. You have contributed to my development. You shall be blessed

arainone · Accepted Answer · 2016-01-14 09:04:05Z

1

To ignore the first line of your csv file call next() on your reader:

reader = next(reader, None)

answered Jan 14, 2016 at 9:04

arainone

2,00819 silver badges28 bronze badges

3 Comments

Payne Over a year ago

Thanks though, it doesn't solve the issue. The format is still irregular

arainone Over a year ago

As said in a previous comment, your expected output is not valid json. And whad do you mean by format is still irregular? You said you don't want to print header, code I gave does just that. Edit your question or ask another

Payne Over a year ago

Thanks I have structured it. I appreciate

Collectives™ on Stack Overflow

python csv to json using column as key

2 Answers 2

Update:

Data:

Code:

Output:

Convert the input data

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update:

Data:

Code:

Output:

Convert the input data

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related