How to create boolean columns based on column values in another dataframe

Question

Lets say I have two pandas DataFrames, df1 and df2, one containing the names and age of people, and the other detailing what they are studying. What is an efficient way to join the two, so that I have boolean fields of what each person is studying?

e.g. given the following

# df1
name  | age
------|----
John  | 24
Kelly | 49
Gemma | 18
Bob   | 29

# df2
name  | studies
------|----------
John  | education
John  | science
Kelly | science
Bob   | law
Bob   | commerce

How could I create the following dataframe with boolean values for each field of study?

name  | age | education | science | law   | commerce |
------|-----|-----------|---------|-------|----------|
John  | 24  | True      | True    | False | False    |
Kelly | 49  | False     | True    | False | False    |
Gemma | 18  | False     | False   | False | False    |
Bob   | 29  | False     | False   | True  | True     |

jezrael · Accepted Answer · 2018-09-24 09:02:40Z

3

Use get_dummies with max, then join and replace missing values only for columns from df22:

s = df2.set_index('name')['studies']
df22 = pd.get_dummies(s, prefix_sep='', prefix='', dtype=bool).max(level=0)
df = df1.join(df22, on='name').fillna(dict.fromkeys(df22.columns, False))
print (df)
    name  age  commerce  education    law  science
0   John   24     False       True  False     True
1  Kelly   49     False      False  False     True
2  Gemma   18     False      False  False    False
3    Bob   29      True      False   True    False

edited Sep 24, 2018 at 9:02

answered Sep 24, 2018 at 8:56

jezrael

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

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

7 Comments

oska boska Over a year ago

Amazing, thanks so much. I encountered the error TypeError: get_dummies() got an unexpected keyword argument 'dtype' but managed to get around it by using df22 = pd.get_dummies(s, prefix_sep='', prefix='').max(level=0).astype('bool') instead. Might just be an issue with my data because its not as clean as the example I gave.

jezrael Over a year ago

@oskaboska - No, problem is your pandas version, dtype is implemented in pandas 0.23.0

oska boska Over a year ago

Ah ok, that makes more sense. Thanks again

Stop War Over a year ago

@jezrael - Tried df['column_name'] = df['column_name'].astype('bool') to get a boolean field in dataframe. With this, the boolean value is defaulted to True. How to default the boolean as False?

jezrael Over a year ago

@user12379095 - Do you think df['column_name'] = ~(df['column_name'].astype('bool')) ?

|

Collectives™ on Stack Overflow

How to create boolean columns based on column values in another dataframe

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related