Creating new Pandas DataFrame rows from multiple ID columns

Question

I have a Pandas dataframe where rows have attributes and the potential for multiple IDs. For example:

Name    Weapon    Color   ID1    ID2    ID3
Leo     Sword     Blue    11     12
Raph    Sai       Red     13
Don     Bo        Purple  14     15     16
Mike    Nunchuck  Orange  17

I'd like to make each of those IDs its own row while retaining the row's attributes. For example:

Name    Weapon    Color   ID
Leo     Sword     Blue    11
Leo     Sword     Blue    12
Raph    Sai       Red     13
Don     Bo        Purple  14
Don     Bo        Purple  15
Don     Bo        Purple  16
Mike    Nunchuck  Orange  17

How would I use chose specific columns to create rows from and which columns to retain the data from?

anky · Accepted Answer · 2019-05-22 16:54:52Z

4

You can use:

df.melt(['Name','Weapon','Color'],value_name='ID').drop('variable',1).dropna()

    Name    Weapon   Color    ID
0    Leo     Sword    Blue  11.0
1   Raph       Sai     Red  13.0
2    Don        Bo  Purple  14.0
3   Mike  Nunchuck  Orange  17.0
4    Leo     Sword    Blue  12.0
6    Don        Bo  Purple  15.0
10   Don        Bo  Purple  16.0

edited May 22, 2019 at 16:54

answered May 22, 2019 at 16:49

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

anky Over a year ago

df.melt(['Name','Weapon','Color'],value_name='ID').drop('variable',1).dropna().sort_values('ID') if sorting on ID is required. :)

Erfan Over a year ago

Saver would be to sort on ['Name', 'Weapon', 'Color'], since those are the columns which are melted, but good answer, upvoted

Quang Hoang · Accepted Answer · 2019-05-22 17:09:45Z

2

You can also use stack(), which keeps the original order of your data:

(df.set_index(['Name', 'Weapon', 'Color'])
   .stack().reset_index(level=-1, drop=True)
   .reset_index(name='ID'))

Output:

    Name    Weapon      Color   ID
0   Leo     Sword       Blue    11.0
1   Leo     Sword       Blue    12.0
2   Raph    Sai         Red     13.0
3   Don     Bo          Purple  14.0
4   Don     Bo          Purple  15.0
5   Don     Bo          Purple  16.0
6   Mike    Nunchuck    Orange  17.0

Note that this will ignore the index of the original dataframe.

answered May 22, 2019 at 17:09

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Collectives™ on Stack Overflow

Creating new Pandas DataFrame rows from multiple ID columns

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