Home > OS >  How to convert API stock data to table using pandas?
How to convert API stock data to table using pandas?

Time:03-06

my API response is like this

{
'1644205500': {'date': '06-02-2022', 'open': 501.85, 'high': 504.2, 'low': 500.75, 'close': 503.05}, 
'1644205800': {'date': '06-02-2022', 'open': 502.85, 'high': 503.05, 'low': 497.45, 'close': 498.55},
'1644206100': {'date': '06-02-2022', 'open': 498.85, 'high': 498.85, 'low': 495.3, 'close': 498.75}, 
'1644206400': {'date': '06-02-2022', 'open': 498.7, 'high': 498.75, 'low': 497.2, 'close': 498.05}, 
'1644206700': {'date': '06-02-2022', 'open': 497.95, 'high': 498.85, 'low': 496.45, 'close': 496.45}, 
'1644207000': {'date': '06-02-2022', 'open': 496.45, 'high': 498, 'low': 496.45, 'close': 497.5}
}

When I use pd.json_normalize(data) there is only one row and so many columns. Like this:

1644205500.date 1644205500.open 1644205500.high 1644205500.low  1644205500.close 1644205800.date    1644205800.open 1644205800.high 1644205800.low  1644205800.close 1644206100.date    1644206100.open 1644206100.high 1644206100.low  1644206100.close

How can I make it like a table like this?

date            open   high   low   close
06-02-2022       500    501    490    501
06-02-2022       501    502    480    470
....

CodePudding user response:

This is very easy:

df = pd.DataFrame(data.values())

EDIT (see comments)

df = pd.DataFrame(data.values()).assign(timestamp=data.keys())
  • Related