How to get row-wise data from dataframe with indices in the JSON format?

user_4464045 0
Viewed 34

df=

                             open    high    low   close
timestamp                                               
2021-05-14 15:25:00+05:30  751.70  752.10  750.5  751.55
2021-05-14 15:26:00+05:30  751.55  751.95  750.5  751.00
2021-05-14 15:27:00+05:30  751.00  751.50  750.0  750.30
2021-05-14 15:28:00+05:30  751.00  751.80  750.0  751.50
2021-05-14 15:29:00+05:30  751.50  755.00  751.0  755.00

Want to convert, similar to:

[
    { time: 1486095960000, close: 1085.0, high: 1085.0, low: 1085.0, open: 1085.0 },
    { time: 1486096020000, close: 751.50, high: 750.50, low: 752.50, open: 751.50 },
]

closest I can get using json.loads(df.to_json(orient="index"))

{
   '1486099920000':{'close': 1116.0, 'high': 1116.9, 'low': 1113.65, 'open': 1113.65}
   '1486099980000':{'close': 1118.95, 'high': 1119.0, 'low': 1116.1, 'open': 1116.1}
}

How to get row-wise data with indices in the above-mentioned format?

pandas dataframe json
1 Answers

Try:

df = df.reset_index().rename(columns={"timestamp": "time"})
df["time"] = pd.to_datetime(df["time"]).astype(np.int64) // 10 ** 6

print(df.to_dict("records"))

Prints:

[{'time': 1620986100000, 'open': 751.7, 'high': 752.1, 'low': 750.5, 'close': 751.55}, 
 {'time': 1620986160000, 'open': 751.55, 'high': 751.95, 'low': 750.5, 'close': 751.0}, 
 {'time': 1620986220000, 'open': 751.0, 'high': 751.5, 'low': 750.0, 'close': 750.3}, 
 {'time': 1620986280000, 'open': 751.0, 'high': 751.8, 'low': 750.0, 'close': 751.5}, 
 {'time': 1620986340000, 'open': 751.5, 'high': 755.0, 'low': 751.0, 'close': 755.0}]

To JSON:

import json

print(json.dumps(df.to_dict("records")))
user_100359850
Related