5

In Pandas, I have the following data frame:

   id1 id2 t1  l1  t2  l2 
0  1   2   a   b   c   d
1  3   4   g   h   i   j

I would like to melt two columns at once. That is, the desired output is:

   id1 id2 tz  lz  
0  1   2   a   b
1  1   2   c   d
2  3   4   g   h
3  3   4   i   j

I know standard melting:

d.melt(id_vars=['id1', 'id2'],
       value_vars=['t1', 't2', 'l1', 'l2'])

but that stacks all columns

   id1  id2 variable value
0    1    2       t1     a
1    3    4       t1     g
2    1    2       t2     c
3    3    4       t2     i
4    1    2       l1     b
5    3    4       l1     h
6    1    2       l2     d
7    3    4       l2     j

How could I melt two columns at once? Something like:

d.melt(id_vars=['id1', 'id2'],
       value_vars={('t1', 'l1'): 'tz', ('t2', 'l2'): 'lz'})

would be great.

eyllanesc
  • 235,170
  • 19
  • 170
  • 241
hyperio
  • 401
  • 3
  • 13

2 Answers2

14

This is wide_to_long

pd.wide_to_long(df, stubnames=['t','l'], i=['id1','id2'], j='drop').reset_index(level=[0,1])
Out[52]: 
      id1  id2  t  l
drop                
1       1    2  a  b
2       1    2  c  d
1       3    4  g  h
2       3    4  i  j
ali bakhtiari
  • 1,051
  • 4
  • 23
BENY
  • 317,841
  • 20
  • 164
  • 234
  • 1
    Which part makes it select (t1, l1) and (t2, l2), not (t1, t2) and (l1, l2). Does it depend on the order in which the columns appear in the dataframe? if so, can I specify the order? Thanks. – hyperio Apr 19 '19 at 19:32
  • @hyperio https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.wide_to_long.html, it is stubnames – BENY Apr 19 '19 at 19:38
5

You can use melt twice here and after that concat them to get desired output:

t = d.melt(id_vars=['id1', 'id2'], value_vars=['t1', 't2'], value_name='tz').drop('variable', axis=1)
l = d.melt(id_vars=['id1', 'id2'], value_vars=['l1', 'l2'], value_name='lz').iloc[:, -1:]

df = pd.concat([t, l], axis=1).sort_values('id1')

Output

print(df)
   id1  id2 tz lz
0    1    2  a  b
2    1    2  c  d
1    3    4  g  h
3    3    4  i  j
Erfan
  • 40,971
  • 8
  • 66
  • 78