CODEWITHSUNDEEP
pythonpandasdataframe
Arvinth Kumar
Arvinth Kumar

Reputation: 1014

How to convert the long to wide format in Pandas dataframe?

I am having dataframe df in below format 

 Date   TLRA_CAPE      TLRA_Pct   B_CAPE     B_Pct    RC_CAPE     RC_Pct
 1/1/2000  10              0.20        30        0.40        50       0.60
 2/1/2000  15              0.25        35        0.45        55       0.65
 3/1/2000  17              0.27        37        0.47        57       0.6

I need to convert into below format

 Date      Variable     CAPE     Pct   
 1/1/2000   TLRA        10       0.20
 2/1/2000   TLRA        15       0.25
 3/1/2000   TLRA         17      0.27 
1/1/2000    B            30      0.40
2/1/2000    B            35      0.45
3/1/2000    B            37      0.47
1/1/2000    RC           50      0.60
2/1/2000    RC            55     0.65
3/1/2000    RC            57     0.6

I am struggling to convert to required format. I tried using pd.melt , pd.pivot but those are not working.

Upvotes: 0

Views: 77

Answers (1)

BENY
BENY

Reputation: 323316

After change of your columns you can do with wide_to_long, and you have both PCT and Pct, I assumed that is typo, if not , do df.columns=df.columns.str.upper()

df=df.set_index('Date')
df.columns=df.columns.str.split('_').map(lambda x : '_'.join(x[::-1]))
pd.wide_to_long(df.reset_index(),['CAPE','Pct'],i='Date',j='Variable',sep='_',suffix='\w+')
Out[63]: 
                   CAPE   Pct
Date     Variable            
1/1/2000 TLRA        10  0.20
2/1/2000 TLRA        15  0.25
3/1/2000 TLRA        17  0.27
1/1/2000 B           30  0.40
2/1/2000 B           35  0.45
3/1/2000 B           37  0.47
1/1/2000 RC          50  0.60
2/1/2000 RC          55  0.65
3/1/2000 RC          57  0.60

Upvotes: 2

Related Questions