Creating new dataframe from existing dataframe columns if not empty

Question

I have a Pandas DataFrame with columns that contain data for some rows and not for others. I need to merge several columns into a single column, removing missing data. For example:

   Name     Preference_1 Preference_2 Preference_3 Preference_4
0  Dave        Beach         Lake     Mountain       Desert
1  Jeff     Outdoors          NaN          NaN          NaN
2   Tom       Forest        Ocean        Swamp          NaN

Needs to become this:

   Name   Preference
0  Dave      Beach
1  Dave       Lake
2  Dave   Mountain
3  Dave     Desert
4  Jeff   Outdoors
5  Tom      Ocean
6  Tom      Swamp
7  Tom     Forest

why do Ocean, Swamp move from Tom to Jeff in the output? — Andy L.
– Andy L., Commented Dec 18, 2019 at 21:15

ansev · Accepted Answer · 2019-12-18 21:58:06Z

2

Use DataFrame.melt:

( df.melt('Name',value_name='Preference')
    .drop('variable',axis=1)
    .dropna()
    .sort_values('Name')
    .reset_index(drop=True) )

or DataFrame.stack with DataFrame.set_index:

df.set_index('Name').stack().rename('Preference').reset_index(level=['Name',0],drop=0)

Output

   Name Preference
0  Dave      Beach
1  Dave       Lake
2  Dave   Mountain
3  Dave     Desert
4  Jeff   Outdoors
5   Tom     Forest
6   Tom      Ocean
7   Tom      Swamp

edited Dec 18, 2019 at 21:58

answered Dec 18, 2019 at 21:27

ansev

31k5 gold badges21 silver badges33 bronze badges

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

1 Comment

Brian Smart Over a year ago

Thank you very much

Collectives™ on Stack Overflow

Creating new dataframe from existing dataframe columns if not empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related