Is there a way to add a new row in a DataFrame when columns have the same name?-CodePudding

After a quadruple for loop, quite long and quite confusing, i get something like this:

ETH-BNB     ETH-BNB     ETH-DOGE     ETH-DOGE     LTC-BTC     LTC-DOGE     LTC-XMR     LTC-XMR  
868.706    2724.619     1207.112     3615.28      278.007     161.565      84.9219     126.234

What I want is something like this:

ETH-BNB     ETH-DOGE     LTC-BTC     LTC-DOGE     LTC-XMR
868.706     1207.112     278.007     161.565      84.9219
2724.619    3615.28        NAN         NAN        126.234

Is there a way I can achieve this?

Thank you in advance

CodePudding user response：

"Quadruple for loop" sounds like you might rethink how you construct your dataframe, but either way, this works:

df_merged = df.groupby(level=0, axis=1).apply(lambda x:x.T.reset_index(drop=True)).droplevel(1, axis=1)

df.groupby(level=0, axis=0) groups the segments with the same column name like e.g.,

   ETH-BNB   ETH-BNB
0  868.706  2724.619

lambda x:x.T.reset_index(drop=True) then stacks each of the groups into this:

          0
0   868.706
1  2724.619

The result is a dataframe that looks almost right:

    ETH-BNB  ETH-DOGE  LTC-BTC LTC-DOGE  LTC-XMR
          0         0        0        0        0
0   868.706  1207.112  278.007  161.565  84.9219
1  2724.619   3615.28      NaN      NaN  126.234

Finally, we just need to get rid of the MultiIndex in the columns, which is achieved by the last step, .droplevel(1, axis=1), resulting in:

    ETH-BNB  ETH-DOGE  LTC-BTC LTC-DOGE  LTC-XMR
0   868.706  1207.112  278.007  161.565  84.9219
1  2724.619   3615.28      NaN      NaN  126.234