Extract values from API source (nested dictionary)-CodePudding

I'm trying to extract only the dates and the cotton values in a dataframe. My attempts with pd.json_normalize / pd.DataFrame.from_dict did not work...

Exemple :

 {'data': {'success': True,
  'timeseries': True,
  'start_date': '2021-06-01',
  'end_date': '2022-05-01',
  'base': 'USD',
  'rates': {'2021-09-22': {'USD': 1, 'COTTON': 1.0994505494505},
   '2021-09-23': {'USD': 1, 'COTTON': 1.0876304347826},
   '2021-09-24': {'USD': 1, 'COTTON': 1.0877934782609},
   '2021-09-25': {'USD': 1},
   '2021-09-26': {'USD': 1, 'COTTON': 1.0876304347826},
   '2021-09-27': {'USD': 1, 'COTTON': 1.0874347826087},
...
   '2022-05-01': {'USD': 1, 'COTTON': 0.64516129032258}},
  'unit': 'per lb'}}

Expected result :

Date	Cotton_Prices
2021-09-23	1.0876304347826
2021-09-24	1.0877934782609
...	...

CodePudding user response：

Make a function to parse your api response:

def response_to_df(response):
    column = {'date':[], 'cotton':[]}
    for date, rate in dic['data']['rates'].items():
        column['date'].append(date)
        column['cotton'].append(rate['COTTON'])
    return pd.DataFrame(column)

CodePudding user response：

df = pd.DataFrame(response['data']['rates']).T[['COTTON']].reset_index().rename({'index':'Date', 'COTTON':'Cotton_prices'}, axis=1)

Result:

   Cotton_Prices        Date
0       1.099451  2021-09-22
1       1.087630  2021-09-23
2       1.087793  2021-09-24
3            NaN  2021-09-25
4       1.087630  2021-09-26
5       1.087435  2021-09-27
6       0.645161  2022-05-01