CODEWITHSUNDEEP
python-3.xpandas
raniphore
raniphore

Reputation: 386

Convert column values into rows in the order in which columns are present

Below is a sample dataframe I have. I need to convert each row into multiple rows based on month.

   df = pd.DataFrame({'Jan': [100,200,300],
                   'Feb': [400,500,600],
                   'March':[700,800,900],
                   })

Desired output :

Jan 100 Feb 400 March 700 Jan 200 Feb 500 March 800 Jan 300 Feb 600 March 900

Tried using pandas melt function but what it does is it will group Jan together, then Feb and March. It will be like 3 rows for Jan, then 3 for Feb and same for March. But i want to achieve the above output. Could someone please help ?

Upvotes: 1

Views: 25

Answers (1)

jezrael
jezrael

Reputation: 863301

Use DataFrame.stack with some data cleaning by Series.reset_index with Series.rename_axis:

df1 = (df.stack()
        .reset_index(level=0, drop=True)
        .rename_axis('months')
        .reset_index(name='val'))

Or use numpy - flatten values and repeat columns names by numpy.tile:

df1 = pd.DataFrame({'months': np.tile(df.columns, len(df)),
                    'val': df.values.reshape(1,-1).ravel()})

print (df1)
  months  val
0    Jan  100
1    Feb  400
2  March  700
3    Jan  200
4    Feb  500
5  March  800
6    Jan  300
7    Feb  600
8  March  900

Upvotes: 2

Related Questions