CODEWITHSUNDEEP
pythonpandas
techdoodle
techdoodle

Reputation: 115

Fill in missing dates in pandas df

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

Upvotes: 4

Views: 256

Answers (1)

jezrael
jezrael

Reputation: 863741

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:

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

Upvotes: 2

Related Questions