How to iterate through nested dictionary-CodePudding

I have the DataFrame:

[
  {
    "symbol_id": "BITSTAMP_SPOT_BTC_USD",
    "time_exchange": "2013-09-28T22:40:50.0000000Z",
    "time_coinapi": "2017-03-18T22:42:21.3763342Z",
    "ask_price": 770.000000000,
    "ask_size": 3252,
    "bid_price": 760,
    "bid_size": 124,
    "last_trade": {
        "time_exchange": "2017-03-18T22:42:21.3763342Z",
        "time_coinapi": "2017-03-18T22:42:21.3763342Z",
        "uuid": "1EA8ADC5-6459-47CA-ADBF-0C3F8C729BB2",
        "price": 770.000000000,
        "size": 0.050000000,
        "taker_side": "SELL"
    }
  },
  {
    "symbol_id": "BITSTAMP_SPOT_BTC_USD",
    "time_exchange": "2013-09-28T22:40:50.0000000Z",
    "time_coinapi": "2017-03-18T22:42:21.3763342Z",
    "ask_price": 770.000000000,
    "ask_size": 3252,
    "bid_price": 760,
    "bid_size": 124,
    "last_trade": {
        "time_exchange": "2017-03-18T22:42:21.3763342Z",
        "time_coinapi": "2017-03-18T22:42:21.3763342Z",
        "uuid": "1EA8ADC5-6459-47CA-ADBF-0C3F8C729BB2",
        "price": 770.000000000,
        "size": 0.050000000,
        "taker_side": "SELL"
    }
  }
]

I need to delete all letters 'T', 'Z' from all column values, to parse the colomn "last_trade", and change values there to format like: yyyy-mm-dd hh:mm:ss

I have the code:

import requests
import json
import pandas as pd


class List_all_current_quotes_data:

    url = "https://rest.coinapi.io/v1/quotes/current" 
    headers = {"X-CoinAPI-Key": "54BE11BF-7A18-4736-A3E6-A7EAB7689DAE"} 

    def getting_response_and_df(
        self,
    ): 
        response = requests.get(self.url, headers=self.headers)
        data = json.loads(
            response.text
        )  
        pd.set_option('display.max_columns', None) 
        pd.set_option('display.max_colwidth', None) 
        df = pd.DataFrame(data)  # Упаковываем в DataFrame
        # print(df)
        return df

    def change_colomns_format(
        self,
        colomn_in_df1,
        colomn_in_df2
    ): 
        df = self.getting_response_and_df()
        list_try = [
            colomn_in_df1,
            colomn_in_df2
        ]
        for i in list_try:
            df[i] = [str(x)[:19].replace("T", " ") for x in df[i]]
        
        print(df)
        return df

    def change_colomns_format2(self,colomn_in_df1, colomn_in_df2, colomn_in_df3, colomn_in_df4):
        df = self.change_colomns_format(colomn_in_df1, colomn_in_df2)
        df[colomn_in_df3] = (pd.json_normalize(df[colomn_in_df3])
                    .assign(time=lambda x: x.values([colomn_in_df4]).str[:19].str.replace('T', ' '))
                    .to_dict('records'))
        return df

    def get_csv(
        self,
        csv_file_name,
        colomn_in_df1,
        colomn_in_df2,
        colomn_in_df3,
        colomn_in_df4
    ):
        df = self.change_colomns_format2(
            colomn_in_df1,
            colomn_in_df2,
            colomn_in_df3,
            colomn_in_df4
        )
        df = df.to_csv(csv_file_name)  
        return df


get_file = List_all_current_quotes_data() 

# get_file.getting_response_and_df()

get_file.get_csv(
    "List_all_current_quotes_data.csv",
    "time_exchange",
    "time_coinapi",
    "last_trade",
    "time_coinapi"
)

But it doesnt work. What i need to do?

CodePudding user response：

Now that I see your code compared to your previous question, I think you should replace json.loads by pd.json_normalize to flatten your data structure. Many things could be easier after that:

# Adapt to your class which is not a big deal
url = "https://rest.coinapi.io/v1/quotes/current" 
headers = {"X-CoinAPI-Key": "54BE11BF-7A18-4736-A3E6-A7EAB7689DAE"} 
response = requests.get(url, headers=headers)

# Flatten your data
df = pd.json_normalize(response.json())
cols = df.columns[df.columns.str.contains('time')]

# Convert to datetime
df[cols] = df[cols].astype('datetime64[s]')

Output:

>>> df

                    symbol_id       time_exchange        time_coinapi  \
0        COINEX_SPOT_SNT_USDT 2022-06-09 20:52:46 2022-06-09 20:52:46   
1         COINEX_SPOT_HNS_BTC 2022-06-09 20:32:55 2022-06-09 20:32:55   
2        COINEX_SPOT_HNS_USDT 2022-06-09 20:13:49 2022-06-09 20:13:49   
3      COINEX_SPOT_STORJ_USDT 2022-06-09 20:34:36 2022-06-09 20:34:36   
4      COINEX_SPOT_ALICE_USDC 2022-06-09 20:18:17 2022-06-09 20:18:17   
...                       ...                 ...                 ...   
37127    BITTREX_SPOT_SRN_ETH 2022-06-09 15:34:46 2022-06-09 15:34:46   
37128   BITTREX_SPOT_ANKR_BTC 2022-06-09 20:35:19 2022-06-09 20:35:19   
37129   BITTREX_SPOT_XLM_USDT 2022-06-09 20:53:41 2022-06-09 20:53:41   
37130   BITTREX_SPOT_TRAC_BTC 2022-06-09 20:38:27 2022-06-09 20:38:27   
37131    BITTREX_SPOT_CRO_BTC 2022-06-09 20:52:43 2022-06-09 20:52:43   

       ask_price      ask_size     bid_price      bid_size  \
0       0.039475    308.152491  3.919700e-02    652.020328   
1       0.000002    158.266463  2.090000e-06    475.633492   
2       0.064025    122.589490  6.339100e-02    517.347026   
3       0.565500   1068.970663  5.617000e-01   1071.254382   
4       2.890500      0.351215  2.860600e 00      1.608702   
...          ...           ...           ...           ...   
37127   0.000002   3033.426985  7.700000e-07  33333.000000   
37128   0.000001   9200.460531  1.250000e-06  12628.178282   
37129   0.139321  25905.988990  1.391591e-01    998.600000   
37130   0.000010   1162.071604  9.920000e-06   1167.283801   
37131   0.000006  22371.212609  5.800000e-06  15538.997153   

      last_trade.time_exchange last_trade.time_coinapi  \
0          2022-06-09 20:51:49     2022-06-09 20:52:45   
1          2022-06-09 20:32:51     2022-06-09 20:33:00   
2          2022-06-09 20:13:34     2022-06-09 20:13:50   
3          2022-06-09 20:33:38     2022-06-09 20:34:32   
4          2022-06-09 20:17:44     2022-06-09 20:18:16   
...                        ...                     ...   
37127                      NaT                     NaT   
37128      2022-06-09 19:05:08     2022-06-09 19:05:08   
37129      2022-06-09 20:50:12     2022-06-09 20:50:12   
37130      2022-06-09 19:45:56     2022-06-09 19:45:56   
37131      2022-06-09 20:46:11     2022-06-09 20:46:11   

                            last_trade.uuid  last_trade.price  \
0      0d5501da-18ab-43ae-9c14-835ec094dac9          0.039405   
1      35267f89-8523-4350-a8d0-9ee94d7c69c3          0.000002   
2      26cd3265-dafa-483f-a48e-0dd92e4a6eb5          0.063499   
3      4b1afa68-76b0-45ff-85b4-87618b2e2f22          0.564800   
4      59edffe8-330b-44de-a2e4-6ab22d37f19e          2.863800   
...                                     ...               ...   
37127                                   NaN               NaN   
37128  31edf304-4612-4b91-9b11-4acf8b3cf0ba          0.000001   
37129  5ea79f20-6a4b-4ac1-9110-6005597392ba          0.139250   
37130  82e9227f-ff8c-44a6-8e48-757100c8efde          0.000010   
37131  9ae20670-127a-48b1-a5bc-8d57377e5c15          0.000006   

       last_trade.size last_trade.taker_side  
0            75.380516                  SELL  
1            78.409891                  SELL  
2            74.877144                   BUY  
3             5.655736                   BUY  
4             0.547412                   BUY  
...                ...                   ...  
37127              NaN                   NaN  
37128      1562.500000                  SELL  
37129       119.380834                  SELL  
37130       158.840000                   BUY  
37131        97.499337                   BUY  

[37132 rows x 13 columns]

Update

If you want to restore your dictionary, use:

cols = df.columns[df.columns.str.match('last_trade')]
df['last_trade'] = df[cols].apply(dict, axis=1)
df = df.drop(columns=cols)

Output:

>>> df

                    symbol_id       time_exchange        time_coinapi  \
0        COINEX_SPOT_SNT_USDT 2022-06-09 20:52:46 2022-06-09 20:52:46   
1         COINEX_SPOT_HNS_BTC 2022-06-09 20:32:55 2022-06-09 20:32:55   
2        COINEX_SPOT_HNS_USDT 2022-06-09 20:13:49 2022-06-09 20:13:49   
3      COINEX_SPOT_STORJ_USDT 2022-06-09 20:34:36 2022-06-09 20:34:36   
4      COINEX_SPOT_ALICE_USDC 2022-06-09 20:18:17 2022-06-09 20:18:17   
...                       ...                 ...                 ...   
37127    BITTREX_SPOT_SRN_ETH 2022-06-09 15:34:46 2022-06-09 15:34:46   
37128   BITTREX_SPOT_ANKR_BTC 2022-06-09 20:35:19 2022-06-09 20:35:19   
37129   BITTREX_SPOT_XLM_USDT 2022-06-09 20:53:41 2022-06-09 20:53:41   
37130   BITTREX_SPOT_TRAC_BTC 2022-06-09 20:38:27 2022-06-09 20:38:27   
37131    BITTREX_SPOT_CRO_BTC 2022-06-09 20:52:43 2022-06-09 20:52:43   

       ask_price      ask_size     bid_price      bid_size  \
0       0.039475    308.152491  3.919700e-02    652.020328   
1       0.000002    158.266463  2.090000e-06    475.633492   
2       0.064025    122.589490  6.339100e-02    517.347026   
3       0.565500   1068.970663  5.617000e-01   1071.254382   
4       2.890500      0.351215  2.860600e 00      1.608702   
...          ...           ...           ...           ...   
37127   0.000002   3033.426985  7.700000e-07  33333.000000   
37128   0.000001   9200.460531  1.250000e-06  12628.178282   
37129   0.139321  25905.988990  1.391591e-01    998.600000   
37130   0.000010   1162.071604  9.920000e-06   1167.283801   
37131   0.000006  22371.212609  5.800000e-06  15538.997153   

                                                                                                                                                                                                                                                          last_trade  
0        {'last_trade.time_exchange': 2022-06-09 20:51:49, 'last_trade.time_coinapi': 2022-06-09 20:52:45, 'last_trade.uuid': '0d5501da-18ab-43ae-9c14-835ec094dac9', 'last_trade.price': 0.039405, 'last_trade.size': 75.38051593, 'last_trade.taker_side': 'SELL'}  
1      {'last_trade.time_exchange': 2022-06-09 20:32:51, 'last_trade.time_coinapi': 2022-06-09 20:33:00, 'last_trade.uuid': '35267f89-8523-4350-a8d0-9ee94d7c69c3', 'last_trade.price': 2.1059e-06, 'last_trade.size': 78.40989063, 'last_trade.taker_side': 'SELL'}  
2         {'last_trade.time_exchange': 2022-06-09 20:13:34, 'last_trade.time_coinapi': 2022-06-09 20:13:50, 'last_trade.uuid': '26cd3265-dafa-483f-a48e-0dd92e4a6eb5', 'last_trade.price': 0.063499, 'last_trade.size': 74.87714414, 'last_trade.taker_side': 'BUY'}  
3            {'last_trade.time_exchange': 2022-06-09 20:33:38, 'last_trade.time_coinapi': 2022-06-09 20:34:32, 'last_trade.uuid': '4b1afa68-76b0-45ff-85b4-87618b2e2f22', 'last_trade.price': 0.5648, 'last_trade.size': 5.65573575, 'last_trade.taker_side': 'BUY'}  
4            {'last_trade.time_exchange': 2022-06-09 20:17:44, 'last_trade.time_coinapi': 2022-06-09 20:18:16, 'last_trade.uuid': '59edffe8-330b-44de-a2e4-6ab22d37f19e', 'last_trade.price': 2.8638, 'last_trade.size': 0.54741158, 'last_trade.taker_side': 'BUY'}  
...                                                                                                                                                                                                                                                              ...  
37127                                                                                       {'last_trade.time_exchange': NaT, 'last_trade.time_coinapi': NaT, 'last_trade.uuid': nan, 'last_trade.price': nan, 'last_trade.size': nan, 'last_trade.taker_side': nan}  
37128         {'last_trade.time_exchange': 2022-06-09 19:05:08, 'last_trade.time_coinapi': 2022-06-09 19:05:08, 'last_trade.uuid': '31edf304-4612-4b91-9b11-4acf8b3cf0ba', 'last_trade.price': 1.28e-06, 'last_trade.size': 1562.5, 'last_trade.taker_side': 'SELL'}  
37129    {'last_trade.time_exchange': 2022-06-09 20:50:12, 'last_trade.time_coinapi': 2022-06-09 20:50:12, 'last_trade.uuid': '5ea79f20-6a4b-4ac1-9110-6005597392ba', 'last_trade.price': 0.13925, 'last_trade.size': 119.38083418, 'last_trade.taker_side': 'SELL'}  
37130             {'last_trade.time_exchange': 2022-06-09 19:45:56, 'last_trade.time_coinapi': 2022-06-09 19:45:56, 'last_trade.uuid': '82e9227f-ff8c-44a6-8e48-757100c8efde', 'last_trade.price': 1e-05, 'last_trade.size': 158.84, 'last_trade.taker_side': 'BUY'}  
37131       {'last_trade.time_exchange': 2022-06-09 20:46:11, 'last_trade.time_coinapi': 2022-06-09 20:46:11, 'last_trade.uuid': '9ae20670-127a-48b1-a5bc-8d57377e5c15', 'last_trade.price': 5.82e-06, 'last_trade.size': 97.499337, 'last_trade.taker_side': 'BUY'}  

[37132 rows x 8 columns]