Transposing all but the first column in a dataframe

Question

I have got the following dataframe

df = pd.DataFrame({'ISIN': ['A', 'B', 'C'], 
                   'Jan': [40000, 50000, 42000],
                   'Feb': [40000, 50000, 42000],
                   'Mar': [40000, 50000, 42000]})

Which produces

   ISIN Jan   Feb    Mar
0  A    40000 50000  42000
1  B    40000 50000  42000
2  C    40000 50000  42000

I am trying to produce an output where the data looks like this:

   Date     A    B      C
0  Jan    40000 40000  40000
1  Feb    50000 50000  50000
2  Mar    42000 42000  42000

My first pass as this was to simply transpose the analysis using

df = df.T

That sort of worked except for the ISIN row (ie what I want as my column headers) were still sitting in the dataset:

           0     1      2
  ISIN     A     B      C
  Jan    40000 40000  40000
  Feb    50000 50000  50000
  Mar    42000 42000  42000

I then tried to pop out the ISIN row before doing the transpostion, ie

ISIN = df.pop('ISIN') df = df.T

That stripped out the ISIN row but then when I try to insert my popped column (using df.insert) and change the axis...I get stuck

I also tried using df.melt but I struggled to get the right things in the right places

I'm sure that this is very easy but I'm going around in circles here and I would really appreciate some wisdom.

Thanks!

In your initial DataFrame, `ISIN` is a column. Try setting it as the index, then transposing: `df = df.set_index('ISIN').T` — Peter Leimbigler, Feb 24 '20 at 16:34
Thank you. I am clearly a dunce - but fortunately you are not!! — harrison10001, Feb 25 '20 at 10:10

score 1 · Answer 1 · answered Feb 24 '20 at 16:34

When you transpose a DataFrame, you also exchange the index and the columns. So you must explicitelyset the index:

df = df.set_index('ISIN').T.reset_index()

You would get:

ISIN index      A      B      C
0      Jan  40000  50000  42000
1      Feb  40000  50000  42000
2      Mar  40000  50000  42000

The values are correct but the labels are off. You can fix them with rename_axis:

df = df.set_index('ISIN').T.rename_axis('Date').reset_index().rename_axis(None, axis=1)

You get as expected:

  Date      A      B      C
0  Jan  40000  50000  42000
1  Feb  40000  50000  42000
2  Mar  40000  50000  42000

score 0 · Accepted Answer · answered Feb 24 '20 at 16:34

When transposing, the index becomes columns and the columns index, so you need to first set ISIN as the index and then transpose.

import pandas as pd

df = pd.DataFrame({'ISIN': ['A', 'B', 'C'], 
                   'Jan': [40000, 50000, 42000],
                   'Feb': [40000, 50000, 42000],
                   'Mar': [40000, 50000, 42000]})
df.set_index('ISIN', inplace=True)
print(df.T)

Which produces:

ISIN      A      B      C
Jan   40000  50000  42000
Feb   40000  50000  42000
Mar   40000  50000  42000

score 0 · Answer 3 · answered Feb 24 '20 at 16:48

0

You can try:

df.rename(columns={'ISIN': 'Date'}).set_index('Date').T

Result:

Date      A      B      C
Jan   40000  50000  42000
Feb   40000  50000  42000
Mar   40000  50000  42000

answered Feb 24 '20 at 16:48

René

4,594
5
23
52

Transposing all but the first column in a dataframe

3 Answers3