0
$\begingroup$

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
Improve this question
$\endgroup$
3
  • $\begingroup$ You should be able to achieve this by simply merging the two tables using the dates column and using an outer join to do so. $\endgroup$
    – Oxbowerce
    Commented Feb 4, 2022 at 9:22
  • $\begingroup$ @Oxbowerce When i do this, it creates empty date columns where data set 2 doesnt match data set 1 on the date $\endgroup$
    – Jacob Nordstrom
    Commented Feb 4, 2022 at 16:18
  • $\begingroup$ I'm not sure how you're doing it, but see my answer below for the exact code. $\endgroup$
    – Oxbowerce
    Commented Feb 4, 2022 at 16:30

1 Answer 1

Reset to default
1
$\begingroup$

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
Improve this answer
$\endgroup$
4
  • $\begingroup$ Like I mentioned in the title, I’m working in powerBI. If I’m asking this question in the wrong place, let me know. If there is a way to use Python or R to merge two data sets, that’d would be cool. My understanding is that the “Run (R/Python) script” will only work on the data set chosen when using Power Query Editor. I need to merge two separate sheets. $\endgroup$
    – Jacob Nordstrom
    Commented Feb 4, 2022 at 22:07
  • $\begingroup$ Apologies, assumes that you wanted to do this in Python. The process for doing this in PowerBI is almost the same, see this example from the Microsoft documentation. As the second to last step where you're expanding the second dataset make sure to also select the key column. You should then have two date column where some rows will be null, which you should be able to fill using simple if/else logic. $\endgroup$
    – Oxbowerce
    Commented Feb 5, 2022 at 11:24
  • $\begingroup$ Unfortunately, this post you linked details the issue I have. When merging, that last row has null for the date rather than the date existing in the second table. If I merge via R or Python, this is not the case. $\endgroup$
    – Jacob Nordstrom
    Commented Feb 7, 2022 at 16:44
  • $\begingroup$ Can you maybe link a screenshot to shows what your current output in PowerBI looks like? In addition, are you selecting the date field from the second dataset when expanding it? $\endgroup$
    – Oxbowerce
    Commented Feb 7, 2022 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.