Search code examples
pythonpandastemperature

split monthly values into daily using pandas for a range of years

enter image description here

Hello. I need to obtain daily data for a range of years, I only have one here. But I have a problem to copy the daily values of one year, for the rest of the range.

How can I do this with pandas?

ETPc.index=pd.to_datetime(ETPc.index, format="%m")
ETPc=ETPc.reindex(pd.date_range(ETPc.index.min(), ETPc.index.max() + pd.offsets.MonthEnd(), name='date'),method='ffill')
ETPc=ETPc.div(ETPc.index.daysinmonth, axis=0).reset_index()

enter image description here

Problem:

years=pd.period_range(1980,2020, freq='Y')
myindex=(np.repeat(years,366).astype(str)+ETPc.index)
ETPc.index=myindex
for year in np.arange(1980,2020):
    if ~calendar.isleap(year):
        ETPc.drop(index=str(year)+'02-29',inplace=True)
ETPc.index=pd.to_datetime(ETPc.index,format='%Y%m-%d')
Solution

  • Use MultiIndex.from_product for combinations years and months, for repeat use DataFrame.reindex, convert MultiIndex to PeriodIndex, use DataFrame.resample for days and last if necessary remove leap days:

    np.random.seed(2022)

df = pd.DataFrame({'FECHA':range(1, 13),
                   'UNIBAN':np.random.rand(12) + 9.98,
                   'TULENAPA':np.random.rand(12) + 9.18}).set_index('FECHA')

    years = pd.MultiIndex.from_product([range(1980, 2020), range(1,13)])

df = df.reindex(years, level=1)
df.index = (pd.to_datetime(df.index.map(lambda x: f'{x[0]}-{x[1]}'), format='%Y-%m')
              .to_period('m'))

df = df.resample('D').ffill()

df = df[(df.index.month != 2) | (df.index.day != 29)]

print (df.head())
              UNIBAN  TULENAPA
1980-01-01  9.989359  10.01358
1980-01-02  9.989359  10.01358
1980-01-03  9.989359  10.01358
1980-01-04  9.989359  10.01358
1980-01-05  9.989359  10.01358

print (df.tail())
               UNIBAN  TULENAPA
2019-12-27  10.807568  9.932473
2019-12-28  10.807568  9.932473
2019-12-29  10.807568  9.932473
2019-12-30  10.807568  9.932473
2019-12-31  10.807568  9.932473