Search code examples
pythonpandasdataframetransformation

Pandas How to Pivot columns that come in sets of 3 into rows

I have a pandas df that I need to transform and pivot. Each season has 3 columns associated with it (start, end and rate). I am looking to pivot these columns and at the end have only 3 columns start, end and rate enter image description here

df:

ID S1start  S1end  Rate  S2start  S2end   Rate  S3start  S3end  Rate  S4start  S4end  Rate
1  1/1/21  1/31/21  80   2/1/21  2/28/21   85   3/1/21  3/31/21  90   4/1/21  4/30/21  95

final:

 ID  Start     End   Rate
  1  1/1/21  1/31/21  80
  1  2/1/21  2/28/21  85
  1  3/1/21  3/31/21  90
  1  4/1/21  4/30/21  95
Solution

  • You can use df.filter with pd.concat:

    In [589]: start = df.stack().filter(like='start').reset_index()[0]    
In [590]: end = df.stack().filter(like='end').reset_index()[0]
In [591]: rate = df.stack().filter(like='Rate').reset_index()[0]

In [594]: x = pd.concat([start.rename('Start'), end.rename('End'), rate.rename('Rate')], 1)

    Let's say you have 2 static cols: ID, PropCode. You can attach these cols to x like this:

    In [640]: x[['ID', 'PropCode']] = df[['ID', 'PropCode']].values.tolist() * len(x)

In [641]: x
Out[641]: 
    Start      End Rate  ID  PropCode
0  1/1/21  1/31/21   80   1     52032
1  2/1/21  2/28/21   85   1     52032
2  3/1/21  3/31/21   90   1     52032
3  4/1/21  4/30/21   95   1     52032