Home > Mobile >  Python pandas normalize this Json into pandas
Python pandas normalize this Json into pandas

Time:08-31

I'm trying to get this json request into df, my code is like this

    gateio = requests.get("https://data.gateapi.io/api2/1/tickers")
    e = gateio.json()
    gateio = json_normalize(e)
    print(gateio)

I get the data not correctly.

      stos_eth.highestBid stos_eth.percentChange stos_eth.baseVolume stos_eth.quoteVolume stos_eth.high24hr stos_eth.low24hr rune_eth.result rune_eth.last rune_eth.lowestAsk rune_eth.highestBid rune_eth.percentChange rune_eth.baseVolume rune_eth.quoteVolume rune_eth.high24hr rune_eth.low24hr  \
0           0.0001413                  -4.91    7.21566512024175        48985.4371475        0.00015302       0.00013892            true     0.0012396          0.0012427           0.0012348                  -3.92      8.105154274293          6404.678774         0.0012942        0.0012377   

  matic_usdt.result matic_usdt.last matic_usdt.lowestAsk matic_usdt.highestBid matic_usdt.percentChange matic_usdt.baseVolume matic_usdt.quoteVolume matic_usdt.high24hr matic_usdt.low24hr arcx_usdt.result arcx_usdt.last arcx_usdt.lowestAsk arcx_usdt.highestBid arcx_usdt.percentChange  \
0              true         0.83047              0.83065                0.8304                     6.83       4369783.0514518        5383470.6302219             0.84403            0.77834             true       0.081401            0.083318             0.079447                   19.33   

  arcx_usdt.baseVolume arcx_usdt.quoteVolume arcx_usdt.high24hr arcx_usdt.low24hr dsd_eth.result dsd_eth.last dsd_eth.lowestAsk dsd_eth.highestBid dsd_eth.percentChange dsd_eth.baseVolume dsd_eth.quoteVolume dsd_eth.high24hr dsd_eth.low24hr sis_usdt.result sis_usdt.last sis_usdt.lowestAsk  \
0      16283.714384037       207846.51634963           0.085766          0.065975           true  0.000000753       0.000012967        0.000000593                     0                  0                   0                0               0            true        0.1937             0.1936   

my expected result is:

                      symbol              lowestAsk               highestBid
0                    srk_eth            0.000000236              0.000000226
1                    isp_eth               0.008294                   0.0082
2                    mft_eth            0.000000512             0.0000005078

CodePudding user response:

Use:

gateio = pd.json_normalize(e)
gateio.columns = gateio.columns.str.split('.', expand=True)
df = gateio.rename_axis(('symbol', None), axis=1).stack(0).droplevel(0).reset_index()

print(df)
          symbol       baseVolume       high24hr     highestBid  \
0      100x_usdt                0              0                  
1      10set_eth                0              0                  
2     10set_usdt  78055.955772115          2.334         2.3189   
3      1art_usdt  84629.671759612       0.020476       0.020051   
4     1earth_eth                0              0                  
         ...              ...            ...            ...   
3023     zrx_usd      378.6665316         0.3075         0.3036   
3024    zrx_usdt  21064.601829316         0.3074         0.3038   
3025     zsc_eth     6.5764445243  0.00000006666  0.00000005859   
3026    zsc_usdt  12105.551030017    0.000099271     0.00009592   
3027    ztg_usdt  17735.456307939        0.10993         0.0993   

               last        low24hr      lowestAsk percentChange  \
0     0.00000001677              0                            0   
1                 0              0                            0   
2            2.3258           2.25         2.3315          0.54   
3          0.020139       0.019922       0.020318         -0.62   
4                 0              0                            0   
            ...            ...            ...           ...   
3023         0.3053         0.2919         0.3048          4.05   
3024         0.3046         0.2923         0.3043          4.35   
3025  0.00000006116  0.00000005942  0.00000006438         -7.91   
3026    0.000098951    0.000095918    0.000101036          2.53   
3027        0.09977        0.09929         0.1003         -7.96   

          quoteVolume result  
0                   0   true  
1                   0   true  
2      34176.76678812   true  
3     4186530.9550705   true  
4                   0   true  
              ...    ...  
3023         1250.925   true  
3024  69748.810196325   true  
3025        105661371   true  
3026   125394404.8585   true  
3027  169037.51711601   true  

[3028 rows x 10 columns]

Another idea is create DataFrame by constructor and pivoting:

gateio = requests.get("https://data.gateapi.io/api2/1/tickers")
e = gateio.json()
df = pd.DataFrame([(k,k1, v1) for k, v in e.items() for k1, v1 in v.items()]).pivot(0,1,2)
print(df)
1                baseVolume       high24hr     highestBid           last  \
0                                                                          
100x_usdt                 0              0                 0.00000001677   
10set_eth                 0              0                             0   
10set_usdt  77135.369425029          2.334         2.3189          2.324   
1art_usdt   85135.129113461       0.020476       0.020073       0.020231   
1earth_eth                0              0                             0   
                    ...            ...            ...            ...   
zrx_usd         378.7539874         0.3075         0.3031         0.3036   
zrx_usdt    20969.605384316         0.3074         0.3034         0.3048   
zsc_eth       6.54257544205  0.00000006666  0.00000005891  0.00000006175   
zsc_usdt    12071.777701317    0.000099271     0.00009592     0.00009804   
ztg_usdt    17614.164813459        0.10918         0.0993         0.0998   

1                 low24hr      lowestAsk percentChange      quoteVolume result  
0                                                                               
100x_usdt               0                            0                0   true  
10set_eth               0                            0                0   true  
10set_usdt           2.25         2.3303          0.31  33779.242174485   true  
1art_usdt        0.019922        0.02037          0.32  4211596.8280705   true  
1earth_eth              0                            0                0   true  
                  ...            ...           ...              ...    ...  
zrx_usd            0.2919         0.3046          3.47         1251.201   true  
zrx_usdt           0.2923         0.3041          4.27  69423.160196325   true  
zsc_eth     0.00000005942  0.00000006479         -7.18        105182158   true  
zsc_usdt      0.000095918    0.000100982           1.6   125041663.4785   true  
ztg_usdt          0.09929         0.1002          -8.8  167942.13011601   true  

[3028 rows x 9 columns]
    
  • Related