CODEWITHSUNDEEP
python-3.xpandasdataframedatetime
TYL
TYL

Reputation: 1637

Insert Missing DateTime into Pandas dataframe Python

I have a dataframe (sample below):

date = ['23/8/2019'] * 3 + ['24/8/2019'] * 3
time = ['9:00:00 PM', '10:00:00 PM', '11:00:00 PM', '12:00:00 AM', '1:00:00 AM', '2:00:00 AM']
datetime = pd.to_datetime(pd.Series(date) + ' ' + pd.Series(time))
value = [10,20,40,20,30,5]

df = pd.DataFrame(list(zip(date,time,datetime,value)), columns=['Date','Time','Datetime','Value'])



    Date        Time        Datetime            Value
0   23/8/2019    9:00:00 PM 2019-08-23 21:00:00 10
1   23/8/2019   10:00:00 PM 2019-08-23 22:00:00 20
2   23/8/2019   11:00:00 PM 2019-08-23 23:00:00 40
3   24/8/2019   12:00:00 AM 2019-08-24 00:00:00 20
4   24/8/2019    1:00:00 AM 2019-08-24 01:00:00 30
5   24/8/2019    2:00:00 AM 2019-08-24 02:00:00 5

How do I fill in missing values such that the dataframe will start with 12:00:00 AM of the first day and end with 11:00:00 PM of the last day, meaning it will yield something like this:

    Date        Time        Datetime            Value
0   23/8/2019   12:00:00 AM 2019-08-23 00:00:00 NA
1   23/8/2019    1:00:00 AM 2019-08-23 01:00:00 NA 
                 .
                 .
                 .       
22  23/8/2019    9:00:00 PM 2019-08-23 21:00:00 10
23  23/8/2019   10:00:00 PM 2019-08-23 22:00:00 20
24  23/8/2019   11:00:00 PM 2019-08-23 23:00:00 40
25  24/8/2019   12:00:00 AM 2019-08-24 00:00:00 20
26  24/8/2019    1:00:00 AM 2019-08-24 01:00:00 30
27  24/8/2019    2:00:00 AM 2019-08-24 02:00:00 5
                 .
                 .
                 .
47  24/8/2019   10:00:00 PM 2019-08-24 22:00:00 NA
48  24/8/2019   11:00:00 PM 2019-08-24 23:00:00 NA

Upvotes: 0

Views: 287

Answers (1)

jezrael
jezrael

Reputation: 863166

First is necessary add first and last datetime by minimal with maximal datetime, remove times and add 23 hours in DataFrame.append and then add all datetime between by DataFrame.asfreq, last reaasign Date and Time columns by Series.dt.strftime:

df = pd.DataFrame(list(zip(date,time,datetime,value)), 
                  columns=['Date','Time','Datetime','Value'])

s = df['Datetime'].min().normalize()
e = df['Datetime'].max().normalize() + pd.Timedelta(23, 'H') 

df = df.append(pd.DataFrame({'Datetime':[s, e]}), ignore_index=True).sort_values('Datetime')
df = df.set_index('Datetime').asfreq('H').reset_index()
df['Date'] = df['Datetime'].dt.strftime('%d/%m/%Y')
df['Time'] = df['Datetime'].dt.strftime('%H:%M:%S %p')

print (df)
              Datetime        Date         Time  Value
0  2019-08-23 00:00:00  23/08/2019  00:00:00 AM    NaN
1  2019-08-23 01:00:00  23/08/2019  01:00:00 AM    NaN
2  2019-08-23 02:00:00  23/08/2019  02:00:00 AM    NaN
3  2019-08-23 03:00:00  23/08/2019  03:00:00 AM    NaN
4  2019-08-23 04:00:00  23/08/2019  04:00:00 AM    NaN
5  2019-08-23 05:00:00  23/08/2019  05:00:00 AM    NaN
6  2019-08-23 06:00:00  23/08/2019  06:00:00 AM    NaN
7  2019-08-23 07:00:00  23/08/2019  07:00:00 AM    NaN
8  2019-08-23 08:00:00  23/08/2019  08:00:00 AM    NaN
9  2019-08-23 09:00:00  23/08/2019  09:00:00 AM    NaN
10 2019-08-23 10:00:00  23/08/2019  10:00:00 AM    NaN
11 2019-08-23 11:00:00  23/08/2019  11:00:00 AM    NaN
12 2019-08-23 12:00:00  23/08/2019  12:00:00 PM    NaN
13 2019-08-23 13:00:00  23/08/2019  13:00:00 PM    NaN
14 2019-08-23 14:00:00  23/08/2019  14:00:00 PM    NaN
15 2019-08-23 15:00:00  23/08/2019  15:00:00 PM    NaN
16 2019-08-23 16:00:00  23/08/2019  16:00:00 PM    NaN
17 2019-08-23 17:00:00  23/08/2019  17:00:00 PM    NaN
18 2019-08-23 18:00:00  23/08/2019  18:00:00 PM    NaN
19 2019-08-23 19:00:00  23/08/2019  19:00:00 PM    NaN
20 2019-08-23 20:00:00  23/08/2019  20:00:00 PM    NaN
21 2019-08-23 21:00:00  23/08/2019  21:00:00 PM   10.0
22 2019-08-23 22:00:00  23/08/2019  22:00:00 PM   20.0
23 2019-08-23 23:00:00  23/08/2019  23:00:00 PM   40.0
24 2019-08-24 00:00:00  24/08/2019  00:00:00 AM   20.0
25 2019-08-24 01:00:00  24/08/2019  01:00:00 AM   30.0
26 2019-08-24 02:00:00  24/08/2019  02:00:00 AM    5.0
27 2019-08-24 03:00:00  24/08/2019  03:00:00 AM    NaN
28 2019-08-24 04:00:00  24/08/2019  04:00:00 AM    NaN
29 2019-08-24 05:00:00  24/08/2019  05:00:00 AM    NaN
30 2019-08-24 06:00:00  24/08/2019  06:00:00 AM    NaN
31 2019-08-24 07:00:00  24/08/2019  07:00:00 AM    NaN
32 2019-08-24 08:00:00  24/08/2019  08:00:00 AM    NaN
33 2019-08-24 09:00:00  24/08/2019  09:00:00 AM    NaN
34 2019-08-24 10:00:00  24/08/2019  10:00:00 AM    NaN
35 2019-08-24 11:00:00  24/08/2019  11:00:00 AM    NaN
36 2019-08-24 12:00:00  24/08/2019  12:00:00 PM    NaN
37 2019-08-24 13:00:00  24/08/2019  13:00:00 PM    NaN
38 2019-08-24 14:00:00  24/08/2019  14:00:00 PM    NaN
39 2019-08-24 15:00:00  24/08/2019  15:00:00 PM    NaN
40 2019-08-24 16:00:00  24/08/2019  16:00:00 PM    NaN
41 2019-08-24 17:00:00  24/08/2019  17:00:00 PM    NaN
42 2019-08-24 18:00:00  24/08/2019  18:00:00 PM    NaN
43 2019-08-24 19:00:00  24/08/2019  19:00:00 PM    NaN
44 2019-08-24 20:00:00  24/08/2019  20:00:00 PM    NaN
45 2019-08-24 21:00:00  24/08/2019  21:00:00 PM    NaN
46 2019-08-24 22:00:00  24/08/2019  22:00:00 PM    NaN
47 2019-08-24 23:00:00  24/08/2019  23:00:00 PM    NaN

Upvotes: 2

Related Questions