# Pandas Merge question

I have a dataset where for each date I have two values say A and B, there are dates when one of the values is missing

Date       C1   C2
1/1/2019    A   10
1/1/2019    B   20
1/2/2019    A   25
1/2/2019    B   30
1/3/2019    A   23
1/4/2019    A   32


I want to make sure for every date, I have the date for A and B. One way I can think is to have driver table with all distinct dates and data for A and B and then do left join. Is there any better way of doing this with pandas merge or iterating through rows? I can't think of any but feel like there might be better way of doing it. Thanks!

I think you have listed the main ways of doing it - you can achieve it by iterating or merging. What is "best" will depend on your use case.

Here is a way to do it by iterating over a dataframe. This way gives you more control about what gets filled i.e. you could add more conditions on which values to fill in. I first make a new dataframe that has all the complete dates and alternating A B A B values in column C1:

import pandas as pd
import numpy as np

dates = pd.date_range(start="1/1/2019", end="1/10/2019")
repeated_dates = np.repeat(dates, 2)
df = pd.DataFrame(index=repeated_dates, columns=["C1", "C2"])
df["C1"] = (len(df) // 2) * ["A", "B"]

# See first 5 rows

C1   C2
2019-01-01  A  NaN
2019-01-01  B  NaN
2019-01-02  A  NaN
2019-01-02  B  NaN
2019-01-03  A  NaN


We will later fill the values of the C2 column.

Next make a dataframe (it would actually be your starting data), by dropping a few rows from our "results" dataframe above:

df_missing = df.drop(df.index[[3, 9]])

C2_col = []

# grouping by the index (i.e. the date) gives us two rows at a time
for date, group in df.groupby(df.index):
try:
# see which values your data has for this data and extract them
day = df_missing.loc[date, ["C1", "C2"]]
C2_A, C2_B = day.C2.values

# If the date wasn't there, we can catch the error and give any values we want
except KeyError as e:
# Could now use more condition e.g. on the date or previous values, etc.
C2_A = C2_B = "was_missing"

# Keep the values in a list
C2_col.extend([C2_A, C2_B])

# Overwrite the column that was full of NaN values
df["C2"] = C2_col


We can see in the final result that all dates are present, along with the A B A pattern, and we could insert whatever we wanted into those dates with missing values:

print(df)

C1           C2
2019-01-01  A          NaN
2019-01-01  B          NaN
2019-01-02  A  was_missing
2019-01-02  B  was_missing
2019-01-03  A          NaN
2019-01-03  B          NaN
2019-01-04  A          NaN
2019-01-04  B          NaN
2019-01-05  A  was_missing
2019-01-05  B  was_missing
2019-01-06  A          NaN
2019-01-06  B          NaN
2019-01-07  A          NaN
2019-01-07  B          NaN
2019-01-08  A          NaN
2019-01-08  B          NaN
2019-01-09  A          NaN
2019-01-09  B          NaN
2019-01-10  A          NaN
2019-01-10  B          NaN

• Thanks. I think merge is easier so will do with that. Thanks for the code. It's interesting to see we can do this way too. – user82041 Sep 16 '19 at 21:10
• @user82041: Consider accepting the answer if it helped you solve the problem, see: datascience.stackexchange.com/help/someone-answers – Shaido Sep 17 '19 at 6:48