CODEWITHSUNDEEP
pythonpandasdataframe
bgilliam
bgilliam

Reputation: 95

how do i convert a pandas dataframe from wide to long while keeping the index?

i'd like to transform a dataframe from wide to long, going from many columns to two columns, while keeping the index. I've tried below using melt. Please let me know what i'm missing.

n.b. the actual dataframe will have hundreds of columns, so i can't list them in the code.

create dataframe:

df = pd.DataFrame(np.random.randint(0,100,size=(3, 3)), columns=list('ABC'),index = ['jan','feb','mar'])

output:

      A   B   C
jan  76   7  72
feb  29  15  69
mar   4  24   9

melt dataframe:

df2 = pd.melt(df.reset_index())

output:

     variable value
0     index   jan
1     index   feb
2     index   mar
3         A    76
4         A    29
5         A     4
6         B     7
7         B    15
8         B    24
9         C    72
10        C    69
11        C     9

desired output:

                variable  value
       jan         A     76
       feb         A     29
       mar         A      4
       jan         B      7
       feb         B     15
       mar         B     24
       jan         C     72
       feb         C     69
       mar         C      9

Upvotes: 2

Views: 155

Answers (1)

anky
anky

Reputation: 75100

With df.melt , you can use , 'index' as name of index, then set the 'index' column back as index and rename:

df.reset_index().melt('index').set_index('index').rename_axis(None)

With df.stack also possible using below:

(df.stack().rename_axis([None,'variable']).reset_index(-1,name='value')
                                              .sort_values('variable'))

    variable  value
jan        A     76
feb        A     29
mar        A      4
jan        B      7
feb        B     15
mar        B     24
jan        C     72
feb        C     69
mar        C      9

Upvotes: 2

Related Questions