Duplicate column A element if designated column is not null

user_13079709 0
Viewed 28

I have four columns in my dataframe. Column A is names and Column B, C, and D are language codes designated to names in Column A. I would like to create a combine Column B, C, and D together into one column and have their designated names on the adjacent column. The sample dataframe shall illustrate the operation in a clearer manner. Can anyone please help me on this? Any help is appreciated!!

Current df

Name     Language 1     Language 2     Language 3
one         en             NaN            NaN
two         ko             ja             zh-CN
three       fr             de             NaN
four        nl             ml             NaN
five        kh             NaN            NaN
six         hi             en             es

I'm thinking it would be a wide to long operation or some sort.

Desired output

Name     Language
one         en
two         ko
two         ja
two       zh-CN
three       fr
three       de
four        nl
four        ml
five        kh
six         hi
six         en
six         es

Thanks again!

pandas concat merge python
1 Answers

Set the Name column as index, then stack the remaining columns, which are languages, into one. This results in an extra index, with a values column, and all the null values excluded. The extra index is not relevant, so drop it with droplevel. Finally, reset index to get it back as a dataframe, and pass an argument of Language to the name parameter.

df.set_index("Name").stack().droplevel(-1).reset_index(name="Language")

    Name    Language
0   one      en
1   two      ko
2   two      ja
3   two      zh-CN
4   three    fr
5   three    de
6   four     nl
7   four     ml
8   five     kh
9   six      hi
10  six      en
11  six      es
user_71757130
Related