Python - parsed json output to CSV

Question

I have parsed a json file in python and have the results printed on screen.

However, I would also like the results to be output to a csv file, exactly as they appear on screen.

Here is my code:

import json

with open('euroinc.json') as json_data:
    d = json.load(json_data)
    for p in d['results']:
        print(p['sedol']+','+p['company']+','+p['name']+','+ p['unitType']+','+p['perf48t60m']+','+p['perf36t48m']+','+p['perf24t36m']+','+p['perf12t24m']+','+p['perf12m']+','+p['initialCharge']+','+p['netAnnualCharge'])

Any help would be much appreciated! Thanks, Craig

Update: here is the json sample:

{
  "results": [
    {
      "sector": "Europe Including UK", 
      "perf48t60m": "n/a", 
      "discountedCode": "", 
      "price_buy": "0", 
      "plusFund": false, 
      "unitType": "Accumulation", 
      "perf6m": "6.35%", 
      "perf36t48m": "11.29%", 
      "loaded": "Unbundled", 
      "fundSize": "2940.1", 
      "annualCharge": "1.07", 
      "netAnnualCharge": "1.07", 
      "sedol": "B7BW9Y0", 
      "perf24t36m": "0.25%", 
      "annualSaving": "0.00", 
      "updated": "06/09/2017", 
      "incomeFrequency": "N/a", 
      "perf60m": "n/a", 
      "perf12t24m": "12.97%", 
      "company": "BlackRock", 
      "initialCharge": "0.00", 
      "paymentType": "Dividend", 
      "perf3m": "0.32%", 
      "name": "BlackRock Global European Value (D2 GBP)", 
      "perf12m": "19.37%", 
      "price_change": "-39.00", 
      "yield": "0.00", 
      "price_sell": "6569.00", 
      "perf36m": "35.19%", 
      "numHoldings": "51"
    }, 
    {
      "sector": "Europe Including UK", 
      "perf48t60m": "22.01%", 
      "discountedCode": "", 
      "price_buy": "0", 
      "plusFund": false, 
      "unitType": "Income", 
      "perf6m": "7.81%", 
      "perf36t48m": "9.61%", 
      "loaded": "Unbundled", 
      "fundSize": "566.1", 
      "annualCharge": "0.30", 
      "netAnnualCharge": "0.30", 
      "sedol": "B76VTR5", 
      "perf24t36m": "-3.95%", 
      "annualSaving": "0.00", 
      "updated": "06/09/2017", 
      "incomeFrequency": "Quarterly", 
      "perf60m": "77.38%", 
      "perf12t24m": "15.38%", 
      "company": "Vanguard", 
      "initialCharge": "0.00", 
      "paymentType": "Dividend", 
      "perf3m": "0.74%", 
      "name": "Vanguard SRI European Stock", 
      "perf12m": "19.69%", 
      "price_change": "-21.37", 
      "yield": "2.79", 
      "price_sell": "15800.81", 
      "perf36m": "32.65%", 
      "numHoldings": "502"
    }
  ]
}

A json sample would be much appreciated to reproduce the output! — Mark Tolonen
– Mark Tolonen, Commented Sep 7, 2017 at 15:39

Mark Tolonen · Accepted Answer · 2017-09-08 01:19:58Z

1

This will write a CSV file with a header. Note fieldnames and extrasaction parameters are required to specify the order of columns and prevent an error when there are extra dictionary entries.

#!python2
import json
import csv

with open('euroinc.json') as json_data:
    d = json.load(json_data)

# with open('out.csv','w',newline='') as f:  # Python 3 version
with open('out.csv','wb') as f:
    w = csv.DictWriter(f,
                       fieldnames='sedol company name unitType perf48t60m perf36t48m perf24t36m perf12t24m perf12m initialCharge netAnnualCharge'.split(),
                       extrasaction='ignore')
    w.writeheader()
    # Ways to use a different header.
    # Note the direct write should use \r\n as that is the default 'excel' CSV dialect for line terminator.
    # f.write('A,B,C,D,E,F,G,H,I,J,K\r\n')
    # w.writerow(dict(zip(w.fieldnames,'col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11'.split())))
    w.writerows(d['results'])

Output:

sedol,company,name,unitType,perf48t60m,perf36t48m,perf24t36m,perf12t24m,perf12m,initialCharge,netAnnualCharge
B7BW9Y0,BlackRock,BlackRock Global European Value (D2 GBP),Accumulation,n/a,11.29%,0.25%,12.97%,19.37%,0.00,1.07
B76VTR5,Vanguard,Vanguard SRI European Stock,Income,22.01%,9.61%,-3.95%,15.38%,19.69%,0.00,0.30

edited Sep 8, 2017 at 1:19

answered Sep 7, 2017 at 15:59

Mark Tolonen

181k26 gold badges182 silver badges278 bronze badges

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

14 Comments

CS10 Over a year ago

Thank you - but its displaying this error. Am I missing something? Traceback (most recent call last): File "C:/Users/craig/Documents/FundRun/Test.py", line 12, in <module> w.writeheader() File "C:\Python3\lib\csv.py", line 142, in writeheader self.writerow(header) File "C:\Python3\lib\csv.py", line 153, in writerow return self.writer.writerow(self._dict_to_list(rowdict)) TypeError: a bytes-like object is required, not 'str'

Mark Tolonen Over a year ago

@Craig The code as written is for Python2, but has a commented out line that should be used for Python 3 instead. What you were missing was specifying the Python version in your question :^)

CS10 Over a year ago

Thank you Mark. Will give that a try in the morning

CS10 Over a year ago

Oh just one quick question, can I specify the header names?

Mark Tolonen Over a year ago

If they are different than the dictionary keys, then you can write them manually with w.writerow(['name1','name2', ...]) instead of w.writeheader().

|

Jorge Alberto Rueda Flores · Accepted Answer · 2017-09-07 15:47:12Z

0

I assume p is a dictionary

You could try:

for p in d['results']:
    for key in p.keys():
        result = a[i]+',',
print result

for the csv part you could try:

import csv 
csv_file = open('your_csv.csv', 'wb') 
csv_outp = csv.writer(csv_file, delimiter=',') 
csv_outp.writerow(result)

I hope this help you

edited Sep 7, 2017 at 15:47

answered Sep 7, 2017 at 15:36

Jorge Alberto Rueda Flores

1748 bronze badges

Collectives™ on Stack Overflow

Python - parsed json output to CSV

2 Answers 2

14 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related