How to create new columns in dataframe based on conditional matches on another dataframe?

user_14460824 0
Viewed 31

Situation

I have two dataframes df1 that holds some information about cars:

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [22000,25000,27000,35000]
        }

and df2 that holds media types corresponding to the cars in df1:

images = {'Brand': ['Honda Civic','Honda Civic','Honda Civic','Toyota Corolla','Toyota Corolla','Audi A4'],
        'MediaType': ['A','B','C','A','B','C']
        }

Expected result

In result I wanna create an overview in df1 that tells if there is a media type available for the car or not:

result = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [22000,25000,27000,35000],
          'MediaTypeA' : [True,True,False,False],
          'MediaTypeB' : [True,True,False,False],
          'MediaTypeC' : [False,False,False,True]
        }

How can I realize this?

I already could check if a Brand from df1 exists in df2, what tells me there is or there is no media type available at all:

df1['check'] = df1['Brand'].isin(df2['Brand'])

but I am not sure how to glue it with the check for the special media types.

pandas dataframe python-3.x
1 Answers

Use get_dummies for indicators, create unique index by max and add to first DataFrame by DataFrame.join, last replace missing values:

df11 = pd.get_dummies(df2.set_index('Brand')['MediaType'], dtype=bool).max(level=0)
df = df1.join(df11, on='Brand').fillna(False)
print (df)
            Brand  Price      A      B      C
0     Honda Civic  22000   True   True   True
1  Toyota Corolla  25000   True   True  False
2      Ford Focus  27000  False  False  False
3         Audi A4  35000  False  False   True

If possible some missing values in df1 then need DataFrame.reindex with fill_value=False:

df22 = pd.get_dummies(df2.set_index('Brand')['MediaType'], dtype=bool).max(level=0)
df = df1.join(df22.reindex(df1['Brand'].unique(), fill_value=False), on='Brand')
print (df)
            Brand  Price      A      B      C
0     Honda Civic  22000   True   True   True
1  Toyota Corolla  25000   True   True  False
2      Ford Focus  27000  False  False  False
3         Audi A4  35000  False  False   True
user_29010020
Related