How to add empy datetime rows?

user_13039962 0
Viewed 39

I have this df:

       CODE    YEAR  MONTH DAY TMAX  TMIN   PP
9984   000130  1991    1    1  32.6  23.4  0.0
9985   000130  1991    1    2  31.2  22.4  0.0
9986   000130  1991    1    3  32.0   NaN  0.0
9987   000130  1991    1    4  32.2  23.0  0.0
9988   000130  1991    1    5  30.5  22.0  0.0
      ...   ...  ...  ...   ...   ...  ...
20118  000130  2018    9   30  31.8  21.2  NaN
30028  000132  1991    1    1  35.2   NaN  0.0
30029  000132  1991    1    2  34.6   NaN  0.0
30030  000132  1991    1    3  35.8   NaN  0.0
30031  000132  1991    1    4  34.8   NaN  0.0
      ...   ...  ...  ...   ...   ...   ...
45000  000132  2019    10   5  35.5   NaN  21.1

I want to add empty rows with NaN values, that can complete the date to 2020 12 31. This must be done by each code.

Expected result:

       CODE    YEAR  MONTH DAY TMAX  TMIN   PP
9984   000130  1991    1    1  32.6  23.4  0.0
9985   000130  1991    1    2  31.2  22.4  0.0
9986   000130  1991    1    3  32.0   NaN  0.0
9987   000130  1991    1    4  32.2  23.0  0.0
9988   000130  1991    1    5  30.5  22.0  0.0
      ...   ...  ...  ...   ...   ...  ...
20118  000130  2018    9   30  31.8  21.2  NaN
      ...   ...  ...  ...   ...   ...  ...
.....  000130  2020    12   31 NaN    NaN  NaN
30028  000132  1991    1    1  35.2   NaN  0.0
30029  000132  1991    1    2  34.6   NaN  0.0
30030  000132  1991    1    3  35.8   NaN  0.0
30031  000132  1991    1    4  34.8   NaN  0.0
      ...   ...  ...  ...   ...   ...   ...
45000  000132  2019    10   5  35.5   NaN  21.1
      ...   ...  ...  ...   ...   ...   ...
       000132  2020    12   31 NaN    NaN  NaN

Thanks in advance. I will appreciate any help.

pandas datetime python
1 Answers

Assuming your DataFrame is called df you can create date_range from the last observed date to a desired end date and use pd.concat:

dates = pd.to_datetime(df[['YEAR', 'MONTH', 'DAY']])
end_date = pd.to_datetime('2020-12-31')
# create a date range - you can specify the frequency!
dates_new = pd.date_range(dates.max(), end_date, freq='D')
df_new = pd.DataFrame(data=dates_new, columns=['date'])
# align index
df_new.index = df_new.index + df.index.max() + 1
df_new['YEAR'] = df_new.date.dt.year
df_new['MONTH'] = df_new.date.dt.month
df_new['DAY'] = df_new.date.dt.day
pd.concat([df, df_add[['YEAR', 'MONTH', 'DAY']]])

You did not say how the CODE column needs to be filled. It looks like you might want to use ffill().

user_104665560
Related