0
$\begingroup$

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!

$\endgroup$
2
$\begingroup$

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
print(df.head())

           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
| improve this answer | |
$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – user82041 Sep 16 '19 at 21:10
  • $\begingroup$ @user82041: Consider accepting the answer if it helped you solve the problem, see: datascience.stackexchange.com/help/someone-answers $\endgroup$ – Shaido Sep 17 '19 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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