Merging data in two different DF based on a column value in both DF's

Question

I have 2 df's as below

df1:

df2:

I want to merge both the DF's in way to get the below values,

Will this be possible ?

Yes, this is what the pandas `join` and `merge` functions are made for — Ben Grossmann, Feb 21 '22 at 17:11
`df1.merge(df2, on='Date', how='left').fillna({'CNT': '-'})` — Corralien, Feb 21 '22 at 17:12
I reopened the question but your sample works for me. I don't know why you have NaN in `CNT` column — Corralien, Feb 21 '22 at 18:03

score 1 · Accepted Answer · answered Feb 21 '22 at 18:02

It give NaN for all CNT values

I used the solution posted above in comment

data1 = {'Date': ['2017-01-01', '2017-02-01', '2017-03-01', 
                  '2017-04-01', '2017-05-01'],
         'value': [1000, 2000, 3000, 4000, 5000]}
df1 = pd.DataFrame(data1)

data2 = {'Date': ['2017-02-01', '2017-03-01', '2017-04-01'],
         'CNT': [132, 234, 356]}
df2 = pd.DataFrame(data2)

df3 = df1.merge(df2, on='Date', how='left').fillna({'CNT': '-'})

Output:

>>> df3
         Date  value    CNT
0  2017-01-01   1000      -
1  2017-02-01   2000  132.0
2  2017-03-01   3000  234.0
3  2017-04-01   4000  356.0
4  2017-05-01   5000      -

Merging data in two different DF based on a column value in both DF's

1 Answers1