How to fill in missing values in pandas dataframe with zeros?

Question

I have a pandas DataFrame with the following values:

df =

1970-01-01 00:00:18        1        1     0         1             0
1970-01-01 00:00:19        0        0     0         1             0
1970-01-01 00:00:20        0        0     0         1             0
1970-01-01 00:00:25        0        1     0         0             1
1970-01-01 00:00:26        0        0     0         0             1

Now, I want to add rows for every missing second, and fill the values of the new row with zeros.

df =

1970-01-01 00:00:18        1        1     0         1             0
1970-01-01 00:00:19        0        0     0         1             0
1970-01-01 00:00:20        0        0     0         1             0
1970-01-01 00:00:21        0        0     0         0             0
1970-01-01 00:00:22        0        0     0         0             0
1970-01-01 00:00:23        0        0     0         0             0
1970-01-01 00:00:24        0        0     0         0             0
1970-01-01 00:00:25        0        1     0         0             1
1970-01-01 00:00:26        0        0     0         0             1

I looked into reindex and resample but it didn't see a way to make it work.

Ideally, I would also like to delete the '1970-01-01' part from the timestamp. But this doesn't have priority.

jezrael · Accepted Answer · 2020-04-21 12:31:25Z

2

Use DataFrame.asfreq working with DatetimeIndex, last convert index to column if necessary:

print (df)
                  date  a  b  c  d  e
0  1970-01-01 00:00:18  1  1  0  1  0
1  1970-01-01 00:00:19  0  0  0  1  0
2  1970-01-01 00:00:20  0  0  0  1  0
3  1970-01-01 00:00:25  0  1  0  0  1
4  1970-01-01 00:00:26  0  0  0  0  1

df['date'] = pd.to_datetime(df['date'])

df = df.set_index('date').asfreq('S', fill_value=0).reset_index()
print (df)
                 date  a  b  c  d  e
0 1970-01-01 00:00:18  1  1  0  1  0
1 1970-01-01 00:00:19  0  0  0  1  0
2 1970-01-01 00:00:20  0  0  0  1  0
3 1970-01-01 00:00:21  0  0  0  0  0
4 1970-01-01 00:00:22  0  0  0  0  0
5 1970-01-01 00:00:23  0  0  0  0  0
6 1970-01-01 00:00:24  0  0  0  0  0
7 1970-01-01 00:00:25  0  1  0  0  1
8 1970-01-01 00:00:26  0  0  0  0  1

answered Apr 21, 2020 at 12:31

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

How to fill in missing values in pandas dataframe with zeros?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related