CODEWITHSUNDEEP
pythonpandas
Danjiri
Danjiri

Reputation: 37

reshaping my pandas dataframe

I have the following dataframe: 

   name     Jan     Feb     Mar     Apr     May     Jun     Jul     Aug  \
0     IBM  156.08  160.01  159.81  165.22  172.25  167.15  164.75  152.77   
1    MSFT   45.51   43.08   42.13   43.47   47.53   45.96   45.61   45.51   
2  GOOGLE  512.42  537.99  559.72  540.50  535.24  532.92  590.09  636.84   
3   APPLE  110.64  125.43  125.97  127.29  128.76  127.81  125.34  113.39   

      Sep     Oct     Nov     Dec  
0  145.36  146.11  137.21  137.96  
1   43.56   48.70   53.88   55.40  
2  617.93  663.59  735.39  755.35  
3  112.80  113.36  118.16  111.73

Which I want to transform into the following:

 Month        AAPL        GOOG         IBM
0   Jan  117.160004  534.522445  153.309998
1   Feb  128.460007  558.402511  161.940002
2   Mar  124.430000  548.002468  160.500000
3   Apr  125.150002  537.340027  171.289993
4   May  130.279999  532.109985  169.649994

I've been fiddling around with melt and pivot but have no idea how to get this to work. Any advice would be appreciated.

Thanks

Upvotes: 2

Views: 65

Answers (3)

piRSquared
piRSquared

Reputation: 294488

Creative Way

pd.DataFrame({'Month': df.columns[1:]}).assign(**{c: v for c, *v in df.values})

   Month   APPLE  GOOGLE     IBM   MSFT
0    Jan  110.64  512.42  156.08  45.51
1    Feb  125.43  537.99  160.01  43.08
2    Mar  125.97  559.72  159.81  42.13
3    Apr  127.29  540.50  165.22  43.47
4    May  128.76  535.24  172.25  47.53
5    Jun  127.81  532.92  167.15  45.96
6    Jul  125.34  590.09  164.75  45.61
7    Aug  113.39  636.84  152.77  45.51
8    Sep  112.80  617.93  145.36  43.56
9    Oct  113.36  663.59  146.11  48.70
10   Nov  118.16  735.39  137.21  53.88
11   Dec  111.73  755.35  137.96  55.40

Upvotes: 1

Scott Boston
Scott Boston

Reputation: 153500

Let's use 'set_index','rename_axis', and T for transpose.

df.set_index('name')\
  .rename_axis(None).T\
  .rename_axis('Month')\
  .reset_index()

Output:

  Month     IBM   MSFT  GOOGLE   APPLE
0   Jan  156.08  45.51  512.42  110.64
1   Feb  160.01  43.08  537.99  125.43
2   Mar  159.81  42.13  559.72  125.97
3   Apr  165.22  43.47  540.50  127.29
4   May  172.25  47.53  535.24  128.76
5   Jun  167.15  45.96  532.92  127.81
6   Jul  164.75  45.61  590.09  125.34
7   Aug  152.77  45.51  636.84  113.39

Upvotes: 2

Daniyal Ahmed
Daniyal Ahmed

Reputation: 755

Set_index basically changes the group by element and groups data according to the index specified by you so If you set_index to name of the group this should solve your issue.

Upvotes: -1

Related Questions