Pandas DataFrame Get Header Names based on values

Question

I have a Pandas DataFrame as shown below - I'm using Python Pandas.

+------------+---------+---------+----------+--------+
| Movie Name | English | Chinese | Japanese | Korean |
+------------+---------+---------+----------+--------+
| A          |       1 |       0 |        0 |      0 |
| B          |       0 |       1 |        1 |      0 |
| C          |       0 |       1 |        1 |      1 |
| D          |       1 |       0 |        0 |      0 |
| E          |       0 |       1 |        0 |      0 |
+------------+---------+---------+----------+--------+

I want to convert it like below, by concatenating Header Names based on values (0 or 1)

Expected Output

+------------+-------------------------+
| Movie Name |        Languages        |
+------------+-------------------------+
| A          | English                 |
| B          | Chinese, Japanese       |
| C          | Chinese,Japanese,Korean |
| D          | English                 |
| E          | Chinese                 |
+------------+-------------------------+

jezrael · Accepted Answer · 2019-04-01 13:47:17Z

5

First create index by DataFrame.set_index, then use DataFrame.dot for matrix multiplication of 1 and last remove last , by Series.str.rstrip with Series.reset_index for 2 column DataFrame:

df = df.set_index('Movie Name')
df1 = df.dot(df.columns + ',').str.rstrip(',').reset_index(name='Languages')
print (df1)
  Movie Name                Languages
0          A                  English
1          B         Chinese,Japanese
2          C  Chinese,Japanese,Korean
3          D                  English
4          E                  Chinese

edited Apr 1, 2019 at 13:47

answered Apr 1, 2019 at 13:41

jezrael

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

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

Comments

BENY · Accepted Answer · 2019-04-01 13:57:50Z

3

IIUC, melt then the problem became a groupby issue

s=df.melt('MovieName').query('value==1').groupby('MovieName').variable.agg(','.join)
df['New']=df.MovieName.map(s)
df
Out[690]: 
  MovieName  English           ...             Korean                      New
0         A        1           ...                  0                  English
1         B        0           ...                  0         Chinese,Japanese
2         C        0           ...                  1  Chinese,Japanese,Korean
3         D        1           ...                  0                  English
4         E        0           ...                  0                  Chinese
[5 rows x 6 columns]

answered Apr 1, 2019 at 13:57

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Mohit Motwani · Accepted Answer · 2019-04-01 13:50:31Z

1

You can use:

df['langauges'] = (df.eq(1)*df.columns).apply(lambda x : ','.join(x), axis = 1).str.strip(',')
df

 Movie Name English Chinese Japanese    Korean  langauges
0     A       1       0        0          0      English
1     B       0       1        1          0      Chinese,Japanese
2     C       0       1        1          1      Chinese,Japanese,Korean
3     D       1       0        0          0      English
4     E       0       1        0          0      Chinese

answered Apr 1, 2019 at 13:50

Mohit Motwani

4,8124 gold badges21 silver badges50 bronze badges

Comments

Kartikeya Sharma · Accepted Answer · 2019-04-01 14:17:06Z

It can be done with pandas.Series.str.cat . You can read more about it here https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.cat.html

import pandas as pd
import numpy as np

df=pd.DataFrame({'Movie Name':['A','B','C','D','E'],'English':[1,0,0,1,0],'Chinese':[0,1,1,0,1],'Japanese':[0,1,1,0,0],'Korean':[0,0,1,0,0]})
df=df.replace(1,df.columns.to_series())
df=df.replace(0,np.NaN)
df['Languages']=df[['English','Chinese','Japanese','Korean']].apply(lambda x: x.str.cat(sep=","),axis=1)
df=df.drop(columns=['English','Chinese','Japanese','Korean'])

Result:

  Movie Name                Languages
0          A                  English
1          B         Chinese,Japanese
2          C  Chinese,Japanese,Korean
3          D                  English
4          E                  Chinese

Collectives™ on Stack Overflow

Pandas DataFrame Get Header Names based on values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related