Dataframe to append new column with values from other dataframe when if statement between two dataframes match

Question

I have two dataframes: df1 and df2

df1:

                       date   
0       2002-06-02 00:00:00       
1       2002-06-02 06:00:00           
2       2002-06-02 12:00:00  
3       2002-06-02 18:00:00           
4       2002-06-03 00:00:00   
5       2002-06-03 06:00:00           
6       2002-06-03 12:00:00

df2:

                       date  total  
0       2002-06-02 00:00:00  NaN       
1       2002-06-03 00:00:00  120000       
2       2002-06-04 00:00:00  NaN

Desired output:

    date  total  
0       2002-06-02 00:00:00  NaN       
1       2002-06-02 06:00:00  NaN       
2       2002-06-02 12:00:00  NaN   
3       2002-06-02 18:00:00  NaN 
4       2002-06-03 00:00:00  120000         
5       2002-06-03 06:00:00  NaN 
6       2002-06-03 12:00:00  NaN

Here is the code that I have attempted:

df1['total'] = df1['date'].map(df2.set_index('date')['total'])

However, what I get as an output is this....

                       date  total  
0       2002-06-02 00:00:00  NaN       
1       2002-06-02 00:00:00  NaN       
2       2002-06-02 00:00:00  NaN   
3       2002-06-02 00:00:00  NaN 
4       2002-06-03 00:00:00  120000         
5       2002-06-03 00:00:00  120000 
6       2002-06-03 00:00:00  120000

I am new to python and hope someone can help me resolve this. Thanks

Why not use LEFT JOIN on df1.date = df2.date ? ,sry for not giving you the exact code as I am from Spark-Scala background. But concept is the same. Try JOINING both Dataframes — Abhishek Sengupta, Jul 01 '20 at 16:52
Hi Abhishek, the df1['date'] and df['date'] don't match in length; df1['date'] increases by increments of 6 hours and df2['date'] increases by increments of 24 hours — windwalker, Jul 01 '20 at 16:56
4 2002-06-03 00:00:00 vs 1 2002-06-03 00:00:00 120000 Here I can see both column has same exact content. — Abhishek Sengupta, Jul 01 '20 at 17:02
where matches like 4 2002-06-03 00:00:00 vs 1 2002-06-03 00:00:00 occur, the total from df2 should appear alongside as shown in 'Desired Ouput' — windwalker, Jul 01 '20 at 17:06
the date column's content matches exactly for both data-frames. I think this is what you want. — Abhishek Sengupta, Jul 01 '20 at 17:07
I have just tried the following code but it produces issues with the time also, changes all times to 00:00:00 df3 = df1.join(df2.set_index('date'), on = 'date') print(df3) — windwalker, Jul 01 '20 at 17:10
val df3 = df1.alias("df1").join(df2.alias("df2"), Seq("date"), "left").na.fill("NaN", Seq("total")).drop("id") I tried this.It works ,though this code is in scala — Abhishek Sengupta, Jul 01 '20 at 17:32
Thanks for all your comments and advice, i found that 'join' actually once I changed the 'date' format — windwalker, Jul 02 '20 at 18:47

Dataframe to append new column with values from other dataframe when if statement between two dataframes match

0 Answers0