0

Resampling of a DataFrame by 1 Hour in pandas gives unexpected NaN values

I have a dataframe having 3 columns. 1st Column contains date ( like 2020-07-01,2020-07-01...); 2nd column contains time ( like 00:00:00, 01:00:00...) for one month on hourly basis and 3rd column contains the corresponding values of a variable including some missing rows (i.e., missing data) in the dataframe. Also some values in the 2nd column (time) is like 15:06:55, 16:00:01 etc.

I want to resample the dataframe by 1 Hour and fill NaN values only in place of the missing data. In my case, Resampling gives NaN values to the missing data place as well as where the time is like 15:06:55, 16:00:01 etc. Please help me to solve the issue. Thanks in advance.

df['Date-Time'] = pd.to_datetime(df[0] + df[1],format='%Y-%m-%d%H:%M:%S')
df = df.set_index('Date-Time')   
df = df.resample('1H').fillna(method=None)

This code gives NaN values in place of missing data as well as where the time is like 15:06:55, 16:00:01, 17:00:01 etc. I want to resample the dataframe by 1 Hour and fill NaN values only in place of the missing data. I have uploaded an image of the dataframe before resampling. Please help me to solve the issue. Thanks in advance.I have uploaded an image of the dataframe before resampling.

kakou
  • 636
  • 2
  • 7
  • 15
ARINDAM DAS
  • 3
  • 3

1 Answers1

1

You use the fillna(method=None) method to fill the missing data. So you fill it with NaN values explicitly. See the pandas documentation.

You can use an interpolation or fill method to fill the missing data. e.g.:

df = df.resample('1H').ffill()

or

df = df.resample('1H').interpolate(method='bfill')

or you fill it with the fillna() method, if you provide backfill, bfill of ffill in the method-argument.

-> look at the interpolate() documentation

nadine
  • 92
  • 8
  • All the solutions you provide does not produce the expected output. Resampling still gives NaN values where the time is like 15:06:55, 16:00:01 etc. In the original dataframe these timesteps have specific values which is converted to NaN after resampling with all the methods you mentioned above. – ARINDAM DAS May 21 '23 at 07:12
  • So share your code as a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). Based on your code in the question, there's no other option than getting NaN values. – nadine May 21 '23 at 07:38