0

I would like to fill the missing values of the dates of a Pandas dataframe, but instead of filling the missing date based only on the date column, I would like to do it based on more than 1 column. In this case, the column source.

The example is the following

Original

date_found  source        count_unique_uuids    count_unique_uuids_raw
2021-05-13  source_1      20                    20
2021-05-14  source_2      1829                  1829
2021-05-14  source_3      2245                  2245
2021-05-14  source_1      40                    40
2021-05-15  source_1      903                   903
2021-05-16  source_2      20                    20
2021-05-18  source_3      89                    89

Desired dataset

    date_found  source        count_unique_uuids    count_unique_uuids_raw
    2021-05-13  source_1      20                    20
    2021-05-13  source_2      0                     0
    2021-05-13  source_3      0                     0
    2021-05-14  source_1      40                    40
    2021-05-14  source_2      1829                  1829
    2021-05-14  source_3      2245                  2245 
    2021-05-15  source_1      903                   903
    2021-05-15  source_2      0                     0
    2021-05-15  source_3      0                     0
    2021-05-16  source_1      0                     0
    2021-05-16  source_2      20                    20
    2021-05-16  source_3      0                     0
    2021-05-17  source_1      0                     0
    2021-05-17  source_2      0                     0
    2021-05-17  source_3      0                     0
    2021-05-18  source_1      0                     0
    2021-05-18  source_2      0                     0
    2021-05-18  source_3      89                    89

I was using reindex and resample as a reference to build the dataset

Reindex: Add missing dates to pandas dataframe

Resample: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.resample.html

I'm trying the following code

But getting an error ValueError: StringArray requires a sequence of strings or pandas.NA

def add_missing_dates(df: pd.DataFrame) -> pd.DataFrame:
    df['date_found'] = pd.to_datetime(df['date_found'], format='%Y-%m-%d')
    min_date = df['date_found'].min()
    max_date = df['date_found'].max()

    (df.set_index(['date_found'])
     .groupby(['source'], as_index=False, group_keys=False)
     .apply(lambda x: x.reindex(pd.date_range(min_date, max_date)))
     .reset_index().rename(columns={'index': 'date_found'})
     .fillna(0)
     )
    return df


def add_dates_to_source(df: pd.DataFrame, source: str = 'source') -> pd.DataFrame:
    sources = df[source].tolist()
    dfs_to_concat = []

    for source_value in sources:
        filtered_df = df.loc[df[source] == source_value]
        df_ = add_missing_dates(filtered_df)
        df_[source] = source_value
        dfs_to_concat.append(df_)

    return pd.concat(dfs_to_concat)

To run

 df = add_dates_to_source(df)
The Dan
  • 1,408
  • 6
  • 16
  • 41

3 Answers3

1

here is one way to do it

# make the date as of type datetime
df['date_found']=pd.to_datetime(df['date_found'])

#find the min_date
min_date = df['date_found'].min()

#find the max date
max_date = df['date_found'].max()


df2=(df.set_index(['date_found'])      # set index to date, to allow for reindex
 .groupby(['source'],as_index=False, group_keys=False) # group on source
 .apply(lambda x: x.reindex(pd.date_range(min_date, max_date))) # reindex on the date range
 .reset_index() # reset index
 .rename(columns={'index': 'date_found'}) # rename the column
)
df2['source'].ffill(inplace=True)
df2.fillna(0, inplace=True)
df2


    date_found  source  count_unique_uuids  count_unique_uuids_raw
0   2021-05-13  source_1    20.0    20.0
1   2021-05-14  source_1    40.0    40.0
2   2021-05-15  source_1    903.0   903.0
3   2021-05-16  source_1    0.0     0.0
4   2021-05-17  source_1    0.0     0.0
5   2021-05-18  source_1    0.0     0.0
6   2021-05-13  source_1    0.0     0.0
7   2021-05-14  source_2    1829.0  1829.0
8   2021-05-15  source_2    0.0     0.0
9   2021-05-16  source_2    20.0    20.0
10  2021-05-17  source_2    0.0     0.0
11  2021-05-18  source_2    0.0     0.0
12  2021-05-13  source_2    0.0     0.0
13  2021-05-14  source_3    2245.0  2245.0
14  2021-05-15  source_3    0.0     0.0
15  2021-05-16  source_3    0.0     0.0
16  2021-05-17  source_3    0.0     0.0
17  2021-05-18  source_3    89.0    89.0
Naveed
  • 11,495
  • 2
  • 14
  • 21
1

this is what i came to (doesn't look nice but seems to work as expected):

min_date = df['date_found'].min()
max_date = df['date_found'].max()

dates = (pd.date_range(min_date,max_date).to_frame()
         .reset_index(drop=True)
         .set_axis(['date_found'],axis=1))

res = (dates.merge(df['source'].drop_duplicates(),how='cross')
       .merge(df,how='left').fillna(0))

print(res)
'''
   date_found    source  count_unique_uuids  count_unique_uuids_raw
0  2021-05-13  source_1                20.0                    20.0
1  2021-05-13  source_2                 0.0                     0.0
2  2021-05-13  source_3                 0.0                     0.0
3  2021-05-14  source_1                40.0                    40.0
4  2021-05-14  source_2              1829.0                  1829.0
5  2021-05-14  source_3              2245.0                  2245.0
6  2021-05-15  source_1               903.0                   903.0
7  2021-05-15  source_2                 0.0                     0.0
8  2021-05-15  source_3                 0.0                     0.0
9  2021-05-16  source_1                 0.0                     0.0
10 2021-05-16  source_2                20.0                    20.0
11 2021-05-16  source_3                 0.0                     0.0
12 2021-05-17  source_1                 0.0                     0.0
13 2021-05-17  source_2                 0.0                     0.0
14 2021-05-17  source_3                 0.0                     0.0
15 2021-05-18  source_1                 0.0                     0.0
16 2021-05-18  source_2                 0.0                     0.0
17 2021-05-18  source_3                89.0                    89.0
​
SergFSM
  • 1,419
  • 1
  • 4
  • 7
0

One option is with complete from pyjanitor, to expose missing rows:

# pip install pyjanitor
import pandas as pd
import janitor
df['date_found'] = pd.to_datetime(df['date_found'])

# build a dictionary to contain the new dates
# the key of the dictionary must exist in the dataframe
new_dates = {"date_found":pd.date_range(df.date_found.min(), 
                                        df.date_found.max(), 
                                        freq='D')}

df.complete(new_dates, 'source', fill_value=0)
   date_found    source  count_unique_uuids  count_unique_uuids_raw
0  2021-05-13  source_1                  20                      20
1  2021-05-13  source_2                   0                       0
2  2021-05-13  source_3                   0                       0
3  2021-05-14  source_1                  40                      40
4  2021-05-14  source_2                1829                    1829
5  2021-05-14  source_3                2245                    2245
6  2021-05-15  source_1                 903                     903
7  2021-05-15  source_2                   0                       0
8  2021-05-15  source_3                   0                       0
9  2021-05-16  source_1                   0                       0
10 2021-05-16  source_2                  20                      20
11 2021-05-16  source_3                   0                       0
12 2021-05-17  source_1                   0                       0
13 2021-05-17  source_2                   0                       0
14 2021-05-17  source_3                   0                       0
15 2021-05-18  source_1                   0                       0
16 2021-05-18  source_2                   0                       0
17 2021-05-18  source_3                  89                      89

Another option, using Pandas only:

min_date = df['date_found'].min()
max_date = df['date_found'].max()

dates = pd.date_range(min_date,max_date)
source = df.source.unique()
combinations = pd.MultiIndex.from_product([dates, source], 
                                          names = ['date_found', 'source'])
combinations = pd.DataFrame([], index = combinations)
(combinations
.merge(df, how = 'outer', on = ['date_found', 'source'])
.fillna(0, downcast='infer')
)
   date_found    source  count_unique_uuids  count_unique_uuids_raw
0  2021-05-13  source_1                  20                      20
1  2021-05-13  source_2                   0                       0
2  2021-05-13  source_3                   0                       0
3  2021-05-14  source_1                  40                      40
4  2021-05-14  source_2                1829                    1829
5  2021-05-14  source_3                2245                    2245
6  2021-05-15  source_1                 903                     903
7  2021-05-15  source_2                   0                       0
8  2021-05-15  source_3                   0                       0
9  2021-05-16  source_1                   0                       0
10 2021-05-16  source_2                  20                      20
11 2021-05-16  source_3                   0                       0
12 2021-05-17  source_1                   0                       0
13 2021-05-17  source_2                   0                       0
14 2021-05-17  source_3                   0                       0
15 2021-05-18  source_1                   0                       0
16 2021-05-18  source_2                   0                       0
17 2021-05-18  source_3                  89                      89
sammywemmy
  • 27,093
  • 4
  • 17
  • 31