Combining two pandas dataframes into one based on conditions-CodePudding

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

CodePudding user response：

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

CodePudding user response：

(dfb
 .merge(dfa, on="ID")
 .pivot_table(index=['ID', 'item'], columns='flag', values='price ($)')
 .add_prefix("price_")
 .reset_index()
)