How to convert json into csv

Question

I am trying to convert below json output to csv.

{u'status': u'success', u'data': {u'candles': [[u'2020-05-15T09:15:00+0530', 9163.95, 9165.05, 9090.55, 9115.9, 440475, 7516350], [u'2020-05-15T09:17:00+0530', 9116.25, 9122.5, 9090, 9090.05, 266925, 7550850], [u'2020-05-15T09:19:00+0530', 9090.05, 9095, 9076.6, 9095, 290325, 7609125], [u'2020-05-15T09:21:00+0530', 9094.8, 9095.15, 9082.35, 9092.35, 179925, 7609125], [u'2020-05-15T09:23:00+0530', 9093, 9102.2, 9085, 9097.65, 132450, 7687050], [u'2020-05-15T09:25:00+0530', 9099.7, 9114.8, 9098.3, 9106.85, 165375, 7681425], [u'2020-05-15T09:27:00+0530', 9110.5, 9129.8, 9108.95, 9129.1, 205800, 7681425], [u'2020-05-15T09:29:00+0530', 9127.45, 9142.5, 9119.35, 9138.95, 189525, 7684050], [u'2020-05-15T09:31:00+0530', 9139.95, 9143.15, 9124, 9127, 150975, 7665525], [u'2020-05-15T09:33:00+0530', 9126.9, 9140, 9124, 9138, 101400, 7665525]]}}

i tried with following code but out put is not expecting as csv

datedata = json_obj3['data']['candles']
encodedUnicode = json.dumps(datedata, ensure_ascii=False).encode('utf-8')
print type(encodedUnicode)

final = encodedUnicode.replace("], [","\n").replace("T"," ").replace("+0530",".000").replace('"','').replace('[[','').replace(']]','').replace('-','.')
print type(final)

required output format

time,Open,High,Low,Close,Volume
02.09.2019 00:00:00.000,1529.405,1529.505,1528.895,1529.005,71499.9997
02.09.2019 00:03:00.000,1528.932,1529.262,1528.905,1529.185,72030.0004
02.09.2019 00:06:00.000,1529.125,1529.405,1529.015,1529.332,33129.9989

ronlut · Accepted Answer · 2020-05-15 20:43:52Z

1

The two answers provided, using pandas, are an overkill for the task as well as don't address the time and date format requested. I would go with the already included csv package:

import csv
from datetime import datetime

data = json_obj3['data']['candles']

with open('out.csv', 'w') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(["time", "Open", "High", "Low", "Close", "Volume"])
    for row in data:
        dt = datetime.strptime(row[0], "%Y-%m-%dT%H:%M:%S%z")
        formatted_dt = dt.strftime("%d.%m.%Y %H:%M:%S.000")
        writer.writerow([formatted_dt] + row[1:])

Output:

time,Open,High,Low,Close,Volume
15.05.2020 09:15:00.000,9163.95,9165.05,9090.55,9115.9,440475,7516350
15.05.2020 09:17:00.000,9116.25,9122.5,9090,9090.05,266925,7550850
...

answered May 15, 2020 at 20:43

ronlut

5925 silver badges17 bronze badges

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

Comments

Onyambu · Accepted Answer · 2020-05-15 20:24:44Z

You could consider using pandas in this case, since it seems you have a rectangular data:

import pandas as pd
df = pd.DataFrame(pd.DataFrame(json_obj3['data']).candles.tolist()).\
              rename({0:'time',1:'open',2:'high',3:'low',4:'close',5:'volume'},axis=1)

since you want to transform to csv you could do:

df.to_csv('path_to_your_file.csv', index = False)

the result should be similar to:

print(df.to_csv(index = False)

time,open,high,low,close,volume,6
2020-05-15T09:15:00+0530,9163.95,9165.05,9090.55,9115.9,440475,7516350
2020-05-15T09:17:00+0530,9116.25,9122.5,9090.0,9090.05,266925,7550850
2020-05-15T09:19:00+0530,9090.05,9095.0,9076.6,9095.0,290325,7609125
2020-05-15T09:21:00+0530,9094.8,9095.15,9082.35,9092.35,179925,7609125
2020-05-15T09:23:00+0530,9093.0,9102.2,9085.0,9097.65,132450,7687050
2020-05-15T09:25:00+0530,9099.7,9114.8,9098.3,9106.85,165375,7681425
2020-05-15T09:27:00+0530,9110.5,9129.8,9108.95,9129.1,205800,7681425
2020-05-15T09:29:00+0530,9127.45,9142.5,9119.35,9138.95,189525,7684050
2020-05-15T09:31:00+0530,9139.95,9143.15,9124.0,9127.0,150975,7665525
2020-05-15T09:33:00+0530,9126.9,9140.0,9124.0,9138.0,101400,7665525

Collectives™ on Stack Overflow

How to convert json into csv

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related