Convert two columns of a dataframe into a Multi-Index-CodePudding

I have the following dataframe:

          id    symbol          kline_start_time                 kline_close_time   open_price    high_price     low_price   close_price         base_volume  taker_by_base_asset_volume  taker_by_quote_asset_volume
0         236    BELBTC 2021-12-12 17:00:00 00:00 2021-12-12 17:59:59.999000 00:00    0.0000327    0.00003293    0.00003261    0.00003266  4.219400000000e 03          1.242200000000e 03           4.077191000000e-02
1         291   BONDBTC 2021-12-12 17:00:00 00:00 2021-12-12 17:59:59.999000 00:00    0.0003512    0.00035590    0.00035080    0.00035590  9.439700000000e 02          3.874500000000e 02           1.371697600000e-01
2         491  DEGOUSDT 2021-12-12 17:00:00 00:00 2021-12-12 17:59:59.999000 00:00    6.2600000    6.38000000    6.25000000    6.38000000  2.304267000000e 04          1.287461000000e 04           8.146284280000e 04
3         495  DENTUSDT 2021-12-12 17:00:00 00:00 2021-12-12 17:59:59.999000 00:00    0.0036200    0.00368500    0.00361300    0.00367000  3.868363360000e 08          2.088560540000e 08           7.630782654240e 05
4         522  DOCKUSDT 2021-12-12 17:00:00 00:00 2021-12-12 17:59:59.999000 00:00    0.0706000    0.07286000    0.07029000    0.07158000  3.661556000000e 06          1.757480000000e 06           1.259960511000e 05
...       ...       ...                       ...                              ...          ...           ...           ...           ...                 ...                         ...                          ...
163593  52691  MOVRUSDT 2021-12-23 08:00:00 00:00 2021-12-23 08:59:59.999000 00:00  180.9000000  190.00000000  180.00000000  184.20000000  3.029349000000e 03          1.926400000000e 03           3.592661830000e 05
163594    293  BONDUSDT 2021-12-23 08:00:00 00:00 2021-12-23 08:59:59.999000 00:00   15.9000000   16.95000000   15.85000000   16.45000000  5.232289000000e 04          3.188566000000e 04           5.267414258000e 05
163595   1620   WINUSDT 2021-12-23 08:00:00 00:00 2021-12-23 08:59:59.999000 00:00    0.0004668    0.00047090    0.00046640    0.00047080  3.791888251000e 09          1.836740019000e 09           8.610554246663e 05
163596    734    GASBTC 2021-12-23 08:00:00 00:00 2021-12-23 08:59:59.999000 00:00    0.0001295    0.00012990    0.00012550    0.00012570  1.859040000000e 04          8.756600000000e 03           1.120307510000e 00
163597    553   DOTUSDT 2021-12-23 08:00:00 00:00 2021-12-23 08:59:59.999000 00:00   27.1500000   27.46000000   27.12000000   27.42000000  3.193410800000e 05          1.466104100000e 05           4.004982870500e 06

It currently has the sequential index that you can see on your left. Is it possible to convert it into a multi-index dataframe, the vertical index being the unique values of kline_start_time and the horizontal index being the unique values of symbol ? Note that kline_start_time has 256 unique values and symbol has 620 unique values.

CodePudding user response：

Are you looking for pivot?

value_cols = ['open_price', 'high_price', 'low_price', 'close_price', 'base_volume']
pivoted_df = df.pivot('kline_start_time', 'symbol', value_cols)

Maybe you just want:

new_df = df.set_index(['kline_start_time', 'symbol'])

Update

if I would want it pivoted the other way round? meaning that instead of listing all symbols for each of the value_cols I'd want it listing each of the value_cols for each symbol ?

pivoted_df = pivoted_df.swaplevel(axis=1).sort_index(axis=1)

Obviously you can chain df.pivot(...).swaplevel(...).sort_index(...)