Fill in missing dates in pandas df

Question

I have a data corresponding to a list of DBs and diff rows with dates that they were in use.

 DB             Dates        USAGE

 ABC            03-06-2018   IN USE
 ABC            07-06-2018   IN USE 
 XYZ            04-06-2018   IN USE
 XYZ            08-06-2018   IN USE

What i want is to have the full calendar month corresponding to every db and not just the dates on which they were in use

 DB             Dates        USAGE
 ABC            01-06-2018    NOT IN USE
 ABC            02-06-2018    NOT IN USE
 ABC            03-06-2018    IN USE
 .
 .
 ABC            07-06-2018    IN USE
 .
 .
 ABC            30-06-2018    NOT IN USE 
 XYZ            01-06-2018    NOT IN USE
 .
 .
 XYZ            30-06-2018    NOT IN USE

If I understood you well, you can query the the dataframe based on the column "usage", follow this question: https://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas .. is this what you need ? — Minions, Jul 30 '18 at 05:45
[Possible duplicate](https://stackoverflow.com/questions/19324453/add-missing-dates-to-pandas-dataframe) — DYZ, Jul 30 '18 at 05:55
Not sure about a downvote, but pretty sure it is a dupe. Both the OP and the linked question are about adding missing dates from a range. — DYZ, Jul 30 '18 at 05:58
@jezrael It's up to you, of course. I say it is a _possible_ dupe. — DYZ, Jul 30 '18 at 06:00
@DYZ - Dupe is not possible use here, because 2 categories and because is necessary count minimal and maximal day of months. — jezrael, Jul 30 '18 at 06:01
@jezrael I agree that, while the linked answer may be used to answer the OP, a complete answer requires more bits and pieces than the linked one. — DYZ, Jul 30 '18 at 06:06

jezrael · Accepted Answer · 2018-07-30T06:03:24.277

Use:

df['Dates'] = pd.to_datetime(df['Dates'], format='%d-%m-%Y')

a = df['Dates'].dt.to_period('m')
dates = pd.date_range(a.min().to_timestamp('ms'), a.max().to_timestamp('m'))

mux = pd.MultiIndex.from_product([df['DB'].unique(), dates], names=['DB','Dates'])

df = df.set_index(['DB','Dates'])['USAGE'].reindex(mux, fill_value='NOT IN USE').reset_index()
print (df.head())
    DB      Dates       USAGE
0  ABC 2018-06-01  NOT IN USE
1  ABC 2018-06-02  NOT IN USE
2  ABC 2018-06-03      IN USE
3  ABC 2018-06-04  NOT IN USE
4  ABC 2018-06-05  NOT IN USE

print (df.tail())
     DB      Dates       USAGE
55  XYZ 2018-06-26  NOT IN USE
56  XYZ 2018-06-27  NOT IN USE
57  XYZ 2018-06-28  NOT IN USE
58  XYZ 2018-06-29  NOT IN USE
59  XYZ 2018-06-30  NOT IN USE

Detail:

print (dates)
DatetimeIndex(['2018-06-01', '2018-06-02', '2018-06-03', '2018-06-04',
               '2018-06-05', '2018-06-06', '2018-06-07', '2018-06-08',
               '2018-06-09', '2018-06-10', '2018-06-11', '2018-06-12',
               '2018-06-13', '2018-06-14', '2018-06-15', '2018-06-16',
               '2018-06-17', '2018-06-18', '2018-06-19', '2018-06-20',
               '2018-06-21', '2018-06-22', '2018-06-23', '2018-06-24',
               '2018-06-25', '2018-06-26', '2018-06-27', '2018-06-28',
               '2018-06-29', '2018-06-30'],
              dtype='datetime64[ns]', freq='D')

Exlanation:

First convert column to_datetime
Create all possible dates - first convert column to to_period, then to date_range with to_timestamp with start and end of month
Then create MultiIndex from_product
and reindex with replace missing values.

@jezrail any way to ignore the dates falling on the weekends? — techdoodle, Jul 30 '18 at 07:39
@techdoodle - Do you think remove weekend dateimes from `dates` like `dates = dates[~dates.weekday.isin([5,6])]` ? — jezrael, Jul 30 '18 at 07:55
@techdoodle - Or set different these dates as last step like `df.loc[df['Dates'].dt.weekday.isin([5,6]), 'USAGE'] = 'no using'` ? — jezrael, Jul 30 '18 at 07:57
how can I get the same range of dates but i want the hourly interval too. 24 entries for each date — techdoodle, Aug 09 '18 at 10:21

Fill in missing dates in pandas df

1 Answers1