-1

i have a pandas dataframe with 2 column.1st column has datatype timestamp and 2nd column consist of values and datatype is int.

df1 frame
1st column            2nd column
2019-06-15 00:00:00     520
2019-06-15 02:00:00     263
2019-06-15 04:00:00     756
2019-06-16 14:00:00     264
2019-06-16 17:00:00     1254

i want output like this

1st column            2nd column 
2019-06-15 00:00:00     756
2019-06-15 01:00:00     0
2019-06-15 02:00:00     263
2019-06-15 03:00:00     0
2019-06-15 04:00:00     756
         ...
2019-06-16 00:00:00     0
2019-06-16 01:00:00     0
         ...
2019-06-16 14:00:00      264
2019-06-16 15:00:00     0
2019-06-16 16:00:00     0
2019-06-16 01:00:00     1254
         ...
2016-06-16 23:00:00      0
2019-06-15 01:00:00      0

that is i want to fill the missing hours values to zero.

vishwajeet
  • 83
  • 1
  • 9
  • what have you tried so far? Seems like you should be able to create a new DataFrame with the appropriate `index`, or use `reindex` and then `fillna` – David Zemens Jun 19 '19 at 13:19

1 Answers1

2

Use resample with fillna:

df.set_index('1st column').resample('H').first().fillna(0)

                     2nd column
1st column                     
2019-06-15 00:00:00       520.0
2019-06-15 01:00:00         0.0
2019-06-15 02:00:00       263.0
2019-06-15 03:00:00         0.0
2019-06-15 04:00:00       756.0
2019-06-15 05:00:00         0.0
2019-06-15 06:00:00         0.0
2019-06-15 07:00:00         0.0
2019-06-15 08:00:00         0.0
2019-06-15 09:00:00         0.0
2019-06-15 10:00:00         0.0
2019-06-15 11:00:00         0.0
2019-06-15 12:00:00         0.0
2019-06-15 13:00:00         0.0
2019-06-15 14:00:00         0.0
2019-06-15 15:00:00         0.0
2019-06-15 16:00:00         0.0
2019-06-15 17:00:00         0.0
2019-06-15 18:00:00         0.0
2019-06-15 19:00:00         0.0
2019-06-15 20:00:00         0.0
2019-06-15 21:00:00         0.0
2019-06-15 22:00:00         0.0
2019-06-15 23:00:00         0.0
2019-06-16 00:00:00         0.0
2019-06-16 01:00:00         0.0
2019-06-16 02:00:00         0.0
2019-06-16 03:00:00         0.0
2019-06-16 04:00:00         0.0
2019-06-16 05:00:00         0.0
2019-06-16 06:00:00         0.0
2019-06-16 07:00:00         0.0
2019-06-16 08:00:00         0.0
2019-06-16 09:00:00         0.0
2019-06-16 10:00:00         0.0
2019-06-16 11:00:00         0.0
2019-06-16 12:00:00         0.0
2019-06-16 13:00:00         0.0
2019-06-16 14:00:00       264.0
2019-06-16 15:00:00         0.0
2019-06-16 16:00:00         0.0
2019-06-16 17:00:00      1254.0
Erfan
  • 40,971
  • 8
  • 66
  • 78
  • suppose my first date and time is starting from '2019-06-11 11:00:00' and i want the hours from zero than how it can be done in these .@Erfan – vishwajeet Jun 21 '19 at 12:13
  • Thats a different question, please post a new question, put the link here and I try to help you @vishwajeet – Erfan Jun 21 '19 at 12:37