Combining two pandas dataframes into one based on conditions

I got two dataframes, simplified they look like this:

Dataframe A

ID item
1 apple
2 peach

Dataframe B

ID flag price ($)
1 A 3
1 B 2
2 B 4
2 A 2

ID: unique identifier for each item
flag: unique identifier for each vendor
price: varies for each vendor

In this simplified case I want to extract the price values of dataframe B and add them to dataframe A in separate columns depending on their flag value.

The result should look similar to this
Dataframe C

ID item price_A price_B
1 apple 3 2
2 peach 2 4

I tried to split dataframe B into two dataframes the different flag values and merge them afterwards with dataframe A, but there must be an easier solution.

Thank you in advance! 🙂

*edit: removed the pictures

>Solution :

You can use pd.merge and pd.pivot_table for this:

df_C = pd.merge(df_A, df_B, on=['ID']).pivot_table(index=['ID', 'item'], columns='flag', values='price')
df_C.columns = ['price_' + alpha for alpha in df_C.columns]

df_C = df_C.reset_index()

Output:

>>> df_C
   ID   item  price_A  price_B
0   1  apple        3        2
1   2  peach        2        4

Leave a Reply