Fill in missing values in pandas dataframe

Question

I would like to fill in missing values in my pandas dataframe. Optimally I would like the minute column to range from 0-60 for each hour. Unfortunately, the data generating process did not record any rows where sub_count = 0. Is there anyway to do this? My data covers the dates 2014-03-31 and 2014-04-01.

df = 

   sub_count        date  hour  minute
0          1  2014-03-31     0       0
1          1  2014-03-31     0       4
2          1  2014-03-31     0       5
3          1  2014-03-31     0       6
4          2  2014-03-31     0       7
...

TomAugspurger · Accepted Answer · 2014-04-01 18:32:03Z

3

Construct a DatetimeIndex (you may be able to do this while reading the data in, depending on how it's stored):

df = df.set_index(pd.to_datetime(df.date + 'T' +
                                 df.hour.astype(str) + ':' +
                                 df.minute.astype(str))

In [23]: df = df['sub_count']

In [24]: df
Out[24]: 
2014-03-31 00:00:00    1
2014-03-31 00:04:00    1
2014-03-31 00:05:00    1
2014-03-31 00:06:00    1
2014-03-31 00:07:00    2
Name: sub_count, dtype: int64

Then resample:

In [26]: df.resample('T')
Out[26]: 
2014-03-31 00:00:00     1
2014-03-31 00:01:00   NaN
2014-03-31 00:02:00   NaN
2014-03-31 00:03:00   NaN
2014-03-31 00:04:00     1
2014-03-31 00:05:00     1
2014-03-31 00:06:00     1
2014-03-31 00:07:00     2
Freq: T, Name: sub_count, dtype: float64

answered Apr 1, 2014 at 18:32

TomAugspurger

29k8 gold badges90 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3439329 Over a year ago

I'm getting an: unsupported operand type(s) for +: 'datetime.date' and 'str' error. If I add .astype(str) to the end of df.date, the code runs but df.resample('T') doesn't seem to do anything :(

user3439329 Over a year ago

what kind of dataframe is this? I can't edit the column names anymore.

TomAugspurger Over a year ago

Do you have other data columns than sub_count? df = df['sub_count'] selected the only data column in your example, so it's a Series now. You can keep it as a DataFrame with df = df[['sub_count']]

Collectives™ on Stack Overflow

Fill in missing values in pandas dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related