Merge two data sets by date, filling in missing dates in 1st table, power bi

Question

Merge two data sets by date, filling in missing dates in 1st table, power bi

Jacob Nordstrom

2022年3月14日 22:04

I'd like to merge the two data sets by the date, keeping all the dates and filling in the totals col with NULL when the date doesn't match

EDIT: I’m working in Power BI

Data set 1

 dates      A_totals  
 2015-07-09     1
 2015-07-10     1   
 2015-07-12     2    
 2015-07-14     4   
 2015-07-16     0

Data set 2

 dates      B_totals  
 2015-07-09     2
 2015-07-11     5   
 2015-07-13     6    
 2015-07-15     9   
 2015-07-17     1

Desired Output

 dates      A_totals  B_totals 
 2015-07-09     1       2
 2015-07-10     1       null
 2015-07-11     null    5
 2015-07-12     2       null
 2015-07-13     null      6
 2015-07-14     4       null
 2015-07-15     null      9
 2015-07-16     0       null
 2015-07-17     null      1

Topic powerbi

Category Data Science

Oxbowerce · Accepted Answer · 2022年2月4日 16:29

As mentioned in my comment, this can simply be achieved by merging the two tables using the dates column as a key and using an outer join to make sure all rows from both dataframes are kept.

import pandas as pd

df1 = pd.DataFrame({
    "dates": ["2015-07-09", "2015-07-10", "2015-07-12", "2015-07-14", "2015-07-16"],
    "A_totals": [1, 1, 3, 4, 0]
})

df2 = pd.DataFrame({
    "dates": ["2015-07-09", "2015-07-11", "2015-07-13", "2015-07-15", "2015-07-17"],
    "B_totals": [2, 5, 6, 9, 1]
})

df1.merge(df2, how="outer", on="dates", sort=True)

This will return the following dataframe:

dates            A_totals  B_totals
2015-07-09       1.0       2.0
2015-07-10       1.0       NaN
2015-07-11       NaN       5.0
2015-07-12       3.0       NaN
2015-07-13       NaN       6.0
2015-07-14       4.0       NaN
2015-07-15       NaN       9.0
2015-07-16       0.0       NaN
2015-07-17       NaN       1.0

Merge two data sets by date, filling in missing dates in 1st table, power bi

About