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