0

I have 2 dataframes df1 and df2, I'm trying to merge then by column (product). 

df1 
    product name    Exist
0     1      foo    False
1     2      bar    True
2     3      lorem  False
3     4      ipsum  False
.
.


df2
    product date_search sold
0     1      2020-04-10 10
1     1      2020-04-11 15
2     1      2020-04-12 20
3     2      2020-04-10 8
4     2      2020-04-11 10
5     2      2020-04-12 30
6     3      2020-04-10 2
7     3      2020-04-11 5
8     3      2020-04-12 7
9     4      2020-04-10 4
10    4      2020-04-11 10
11    4      2020-04-12 15
.
.

I'd like to append new columns to result dataframe with a value of df2 as column and other values of df2 as the value, Can anybody help me? Like the following:

df2
    product Exists 2020-04-10 2020-04-11 2020-04-12 
0     1     False      10         15        20
1     2     True        8         10        30
2     3     False       2          5         7
3     4     False       4         10        15
.
.

Thanks!

Abel Mejias
  • 13
  • 4
  • 3
    [How to pivot a dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) and [Pandas merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) are both relevant. What have you tried so far based on your own research? – G. Anderson Apr 15 '20 at 21:47
  • 3
    `df1.merge(df2.pivot(*df2), left_on='product', right_index=True, how='left' )` – Quang Hoang Apr 15 '20 at 21:58

1 Answers1

0

@Quang Hoang nailed it -- just do this:

import pandas as pd

dict1 = {"product" : [1,2,3,4], "name" : ['foo','bar','lorem','ipsum'], "exists": ['false','true','false','false']}
dict2 = {"product" : [1,1,2,2,3,3,4,4], "date": ['2020-01-01','2020-01-02','2020-01-01','2020-01-02','2020-01-01','2020-01-02','2020-01-01','2020-01-02'], "quantity": [10,14,15,4,6,77,34,9]}
df1 = pd.DataFrame.from_dict(dict1)
df2 = pd.DataFrame.from_dict(dict2)


df1.merge(df2.pivot(*df2), left_on='product', right_index=True, how='left' )
dkritz
  • 354
  • 2
  • 11