Resampling pandas Dataframe keeping other columns

Question

I'm facing a problem with a pandas dataframe. Actually my Dataframe contains 3 columns: DATE_TIME, SITE_NB, VALUE. For some SITE_NB there are missing rows. For example:

DATE_TIME;SITE_NB; VALUE

2011-01-03 01:00; 1; 10.7

2011-01-03 04:00; 1; 3.2

2011-01-03 05:00; 1; -2.1

So here, rows for 2011-01-03 00:00, 2011-01-03 02:00 and 2011-01-03 03:00 are missing. What I want is add these rows with the same SITE_NB (=1) and with VALUE (=NaN)

I want to do the same for all different SITE_NB in my dataframe. So for each SITE_NB, add missing rows based on DATE_TIME with a frequency of 1 Hour, and putting NaN in VALUE for freshly added rows.

I tried resampling but did not get the right output...

Can somebody help me to solve this issue?

Thanks!

Answer 1

To add missing indices, use:

full_idx = pd.date_range(start='<start_date>', end='<end_date>', freq='H')

df.reindex(full_idx)

That will put NaN's in SITE_NB and VALUE columns.

If each dataframe has only one value of SITE_NB, you can use:

df['SITE_NB'].fillna(df['SITE_NB'].unique()[0], inplace=True)

which replaces all NaN's with the first non-null values in the column.

---

If you actually have one dataframe with multiple SITE_NB values, could you please show what that looks like? Are the time indices overlapping in some cases or not?

Also, this answer might work for you: https://stackoverflow.com/questions/32275540/pandas-reindex-dates-in-groupby