Python grouping Orderbook (Pandas dataframe)

Question

     asks_price  asks_qty exchange_name_ask     bids_price  bids_qty exchange_name_bid
0      20156.51  0.000745          Coinbase      20153.28  0.000200          Coinbase
1      20157.52  0.050000          Coinbase      20152.27  0.051000          Coinbase
2      20158.52  0.000745          Coinbase      20151.28  0.000200          Kraken
3      20158.52  0.050000          FTX           20151.28  0.051000          Coinbase

I would like to group the same price, add quantity together and combine the name of the exchange like :

     asks_price  asks_qty exchange_name_ask     bids_price  bids_qty exchange_name_bid
0    20156.51  0.000745          Coinbase      20153.28  0.000200          Coinbase
1    20157.52  0.050000          Coinbase      20152.27  0.051000          Coinbase
2    20158.52  0.050745          CoinbaseFTX   20151.28  0.051200       KrakenCoinbase

I received a good answer simply if it is the same name I do not want it to be concat, example CoinbaseCoinbase. How can i do that ?

Answer 1

group the dataframe by asks_price then call sum passing False to numeric_only argument, the solution assumes that the data is in the order you've provided in the sample data, else you need to handle the logic for sorting:

>>> df.groupby(['asks_price', 'bids_price']).sum(False)

                       asks_qty exchange_name_ask  bids_qty exchange_name_bid
asks_price bids_price                                                        
20156.51   20153.28    0.000745          Coinbase    0.0002          Coinbase
20157.52   20152.27    0.050000          Coinbase    0.0510          Coinbase
20158.52   20151.28    0.050745       CoinbaseFTX    0.0512    KrakenCoinbase

You can call reset_index() at last if you want to have prices as column rather than index.

Answer 2

df.groupby(['asks_price', 'bids_price'], as_index=False).sum(False)

    asks_price  bids_price  asks_qty    exchange_name_ask   bids_qty    exchange_name_bid
0   20156.51    20153.28    0.000745    Coinbase            0.0002      Coinbase
1   20157.52    20152.27    0.050000    Coinbase            0.0510      Coinbase
2   20158.52    20151.28    0.050745    CoinbaseFTX         0.0512      KrakenCoinbase