Convert/unpivot multiple columns to rows in Python Dataframe

Question

I have a dataset which i need to unpivot into muliple rows.

Eg:

id  cor_id1 mail11 mail12 mail13 cor_id2 mail21 mail22 mail23 cor_id3 mail31 mail32 mail33
1    1      a@123  b@234  c@123    2     a@def  b@fgh   c@asd   3      s@wer  b@ert  e@rty
2    4      e@234  e@234  e@qwe    9     e@dfe  f@jfg   r@ert   10     e@wer  g@wer  e@ert

I need to unpivot them as

id cor_id mail
1   1     a@123
1   1     b@234
1   1     c@123
1   2     a@def
1   2     b@fgh
1   2     c@asd
1   3     s@wer
1   3     b@ert
1   3     e@rty
2   4     e@234
2   4     e@234
2   4     e@qwe
2   9     e@dfe
2   9     r@ert
2   10    e@wer
2   10    g@wer
2   10    e@ert

I have tried df.melt but that that is giving for only 1 column.

What if the data has multiple columns to be converted to rows.

id  cor_id1 ad1 mail11 mail12 mail13 cor_id2 ad2 mail21 mail22 mail23 cor_id3 ad3 mail31 mail32 mail33
1    1     23    a@123  b@234  c@123        2   24  a@def  b@fgh   c@asd      3   25   s@wer  b@ert  e@rty
2    4     33    e@234  e@234  e@qwe        9   34 e@dfe  f@jfg   r@ert      10  35    e@wer  g@wer  e@ert

and i want

id cor_id  ad  mail
1   1      23  a@123
1   1      23 b@234
1   1      23 c@123
1   2      24 a@def
1   2      24  b@fgh
1   2      24 c@asd
1   3      25 s@wer
1   3      25 b@ert
1   3      25 e@rty
2   4      33 e@234
2   4      33 e@234
2   4      33 e@qwe
2   9      34 e@dfe
2   9      34 f@jfg
2   9      34 r@ert
2   10     35 e@wer
2   10     35 g@wer
2   10     35 e@ert

jezrael · Accepted Answer · 2019-11-22 11:36:34Z

2

Use wide_to_long, but first is necessary change columns names for cor_id columns with add last digit:

df = df.rename(columns=lambda x: x + x[-1] if x.startswith('cor_id') else x)
df = pd.wide_to_long(df, ['cor_id', 'mail'], i='id', j='i')
df['cor_id'] = df['cor_id'].ffill()
df = df.reset_index(level=1, drop=True).reset_index()

Alternative is add 0 and remove missing rows with dropna:

df = df.rename(columns=lambda x: x + '0' if x.startswith('cor_id') else x)
df = pd.wide_to_long(df, ['cor_id', 'mail'], i='id', j='i')
df['cor_id'] = df['cor_id'].ffill()
df = df.dropna(subset=['mail']).reset_index(level=1, drop=True).reset_index()

print (df)
    id  cor_id   mail
0    1     1.0  a@123
1    1     1.0  b@234
2    1     1.0  c@123
3    1     1.0  a@def
4    1     2.0  b@fgh
5    1     2.0  s@wer
6    1     2.0  b@ert
7    1     3.0  e@rty
8    1     3.0  c@asd
9    2     4.0  e@234
10   2     4.0  e@234
11   2     4.0  e@qwe
12   2     4.0  e@dfe
13   2     9.0  f@jfg
14   2     9.0  e@wer
15   2     9.0  g@wer
16   2    10.0  e@ert
17   2    10.0  r@ert

EDIT: If there is multiple columns like cor_id only add it to tuple for test by startswith and then change forward filling by all columns by list with ffill:

df = df.rename(columns=lambda x: x + '0' if x.startswith(('cor_id','ad')) else x)
df = pd.wide_to_long(df, ['cor_id', 'ad','mail'], i='id', j='i')
df[['cor_id','ad']] = df[['cor_id','ad']].ffill()
df = df.dropna(subset=['mail']).reset_index(level=1, drop=True).reset_index()
print (df)
    id  cor_id    ad   mail
0    1     1.0  23.0  a@123
1    1     1.0  23.0  b@234
2    1     1.0  23.0  c@123
3    1     2.0  24.0  a@def
4    1     2.0  24.0  b@fgh
5    1     2.0  24.0  c@asd
6    1     3.0  25.0  s@wer
7    1     3.0  25.0  b@ert
8    1     3.0  25.0  e@rty
9    2     4.0  33.0  e@234
10   2     4.0  33.0  e@234
11   2     4.0  33.0  e@qwe
12   2     9.0  34.0  e@dfe
13   2     9.0  34.0  f@jfg
14   2     9.0  34.0  r@ert
15   2    10.0  35.0  e@wer
16   2    10.0  35.0  g@wer
17   2    10.0  35.0  e@ert

edited Nov 22, 2019 at 11:36

answered Nov 21, 2019 at 8:50

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shashank Over a year ago

Thats really great what if i have multiple columns like cor_id that needs to be converted to rows

jezrael Over a year ago

@shashank - Can you change data sample for it? How looks columns names for cor_id ?

Aurelie Giraud · Accepted Answer · 2021-05-17 08:29:08Z

2

You could also use pd.melt() for example like this:

melted = pd.melt(df, id_vars = ['id','cor_id'], value_vars = ['mail11','mail12',...], var_name = 'Attribute', value_name = 'Value')

melted.head()

answered May 17, 2021 at 8:29

Aurelie Giraud

1036 bronze badges

Collectives™ on Stack Overflow

Convert/unpivot multiple columns to rows in Python Dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related