1

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

Bloom
  • 59
  • 4
  • Please don't add tables as images. If you can't get the formatting right, someone will gladly edit it for you. – BigBen Sep 22 '22 at 17:04
  • 1
    Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) Merge the frames, then use groupby. – It_is_Chris Sep 22 '22 at 17:05
  • @It_is_Chris I've tried splitting dataframe b and merging it into dataframe 1 but it's a quite complicated solution. I thought there must be an easier way to pull the data into one table. I'll try your approach using group by. Thank you :) – Bloom Sep 22 '22 at 17:15

2 Answers2

1

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
T C Molenaar
  • 3,205
  • 1
  • 10
  • 26
1
(dfb
 .merge(dfa, on="ID")
 .pivot_table(index=['ID', 'item'], columns='flag', values='price ($)')
 .add_prefix("price_")
 .reset_index()
)
William Rosenbaum
  • 255
  • 2
  • 7