0
$\begingroup$

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!

$\endgroup$
0
$\begingroup$

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

$\endgroup$
  • $\begingroup$ Thank you Nemanja Radojković ! creating a new index and reindexing the dataframe solved my issue! $\endgroup$ – JaYo Sep 24 '18 at 9:05
  • $\begingroup$ Glad I could help! :-) $\endgroup$ – Nemanja Radojković Sep 24 '18 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.