0

I want to merge two dataframes into one, but there are some duplicate values in the key, which is Item. It can't be achieved by using 'cross join' cuz it only uses "cross join" in the same group. Can someone share the thought to solve it? Thanks

For example:

dataframe1:

ID    Item    Price
1     apple     5
1     banana    3
1     lemon     2
2     apple     7
2     banana    4
2     lemon     4

dataframe2

Item     state
apple      TX
apple      CA
apple      NJ
banana     CA
lemon      NY
lemon      PA

Expected result:

ID    Item    Price   State
1     apple     5       TX
1     apple     5       NJ
1     apple     5       CA
1     banana    3       CA
1     lemon     2       NY
1     lemon     2       PA
2     apple     7       TX
2     apple     7       NJ
2     apple     7       CA
2     banana    4       CA
2     lemon     4       NY
2     lemon     4       PA
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Jammy
  • 87
  • 1
  • 8
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – Anurag Dabas Jul 07 '21 at 09:30
  • Not really, I do know how to join two dataframes It's not just using left, right or cross join tho. – Jammy Jul 07 '21 at 09:35
  • I tried to set up the index as "Item" for two dataframes. Then, to merge with index by using "cross" way. Yet, it can't work still. – Jammy Jul 07 '21 at 09:38
  • `df1.merge(df2,on='Item',how='outer')`? – Anurag Dabas Jul 07 '21 at 09:39
  • 1
    Oops, alright. I made a mistake that the values in one of the column"item" starts with a capital character... That's why it can't work.... seems like it's a stupid question smh... what should I do for this post @AnuragDabas Much appreciated. – Jammy Jul 07 '21 at 09:52

1 Answers1

4

You can do:

pd.merge(df1, df2).sort_values(by=['ID'])

output:

    ID    Item  Price state
0    1   apple      5    TX
1    1   apple      5    CA
2    1   apple      5    NJ
6    1  banana      3    CA
8    1   lemon      2    NY
9    1   lemon      2    PA
3    2   apple      7    TX
4    2   apple      7    CA
5    2   apple      7    NJ
7    2  banana      4    CA
10   2   lemon      4    NY
11   2   lemon      4    PA
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
mozway
  • 194,879
  • 13
  • 39
  • 75