I'm looking to stack the indices of some columns on top of one another, this is what I currently have:

                          
            Buy     Buy Currency   Sell     Sell Currency   
Date                        
2013-12-31  100     CAD            100      USD
2014-01-02  200     USD            200      CAD
2014-01-03  300     CAD            300      USD
2014-01-06  400     USD            400      CAD

This is what I'm looking to achieve:

Buy/Sell     Buy/Sell Currency   
                     

100          USD
100          CAD
200          CAD
200          USD
300          USD
300          CAD

And so on. Basically want to take the values in "Buy" and "Buy Currency" and stack their values in the "Sell" and "Sell Currency" columns, one after the other.

And so on. I should mention that my data frame has 10 columns in total so using

df_pl.stack(level=0)

doesn't seem to work.

CodePudding user response：

using concat

import pandas as pd


print(pd.concat(
    [df['Buy'], df['sell']], axis=1
).stack().reset_index(1, drop=True).rename(index='buy/sell')
)

output:

0    100
0    100
1    200
1    200
2    300
2    300
3    400
3    400

CodePudding user response：

# assuming that your data has date as index.
df.set_index('date', inplace=True)


# create a mapping to new column names
d={'Buy Currency': 'Buy/Sell Currency', 
   'Sell Currency' : 'Buy/Sell Currency',
   'Buy' : 'Buy/Sell',
   'Sell' :'Buy/Sell'
  }
df.columns=df.columns.map(d)



# stack first two columns over the next two columns
out=pd.concat([ df.iloc[:,:2],
            df.iloc[:,2:]
          ],
           ignore_index=True
          )
out

    Buy/Sell    Buy/Sell Currency
0   100     CAD
1   200     USD
2   300     CAD
3   400     USD
4   100     USD
5   200     CAD
6   300     USD
7   400     CAD

CodePudding user response：

One option is with pivot_longer from pyjanitor, where for this particular use case, you pass a list of regular expressions (to names_pattern) to aggregate the desired column labels into new groups (in names_to):

# pip install pyjanitor
import pandas as pd
import janitor

df.pivot_longer(index=None, 
                names_to = ['Buy/Sell', 'Buy/Sell Currency'], 
                names_pattern = [r"Buy$|Sell$", ". Currency$"], 
                ignore_index = False, 
                sort_by_appearance=True)

            Buy/Sell Buy/Sell Currency
Date                                  
2013-12-31       100               CAD
2013-12-31       100               USD
2014-01-02       200               USD
2014-01-02       200               CAD
2014-01-03       300               CAD
2014-01-03       300               USD
2014-01-06       400               USD
2014-01-06       400               CAD