Mapping ranges of date in pandas dataframe-CodePudding

I would like to map values defined in a dictionary of date: value into a DataFrame of dates.

Consider the following example:

import pandas as pd

df = pd.DataFrame(range(19), index=pd.date_range(start="2010-01-01", end="2010-01-10", freq="12H"))

dct = {
    "2009-01-01": 1,
    "2010-01-05": 2,
    "2020-01-01": 3,
}

I would like to get something like this:

df
                      0  test
2010-01-01 00:00:00   0   1.0
2010-01-01 12:00:00   1   1.0
2010-01-02 00:00:00   2   1.0
2010-01-02 12:00:00   3   1.0
2010-01-03 00:00:00   4   1.0
2010-01-03 12:00:00   5   1.0
2010-01-04 00:00:00   6   1.0
2010-01-04 12:00:00   7   1.0
2010-01-05 00:00:00   8   2.0
2010-01-05 12:00:00   9   2.0
2010-01-06 00:00:00  10   2.0
2010-01-06 12:00:00  11   2.0
2010-01-07 00:00:00  12   2.0
2010-01-07 12:00:00  13   2.0
2010-01-08 00:00:00  14   2.0
2010-01-08 12:00:00  15   2.0
2010-01-09 00:00:00  16   2.0
2010-01-09 12:00:00  17   2.0
2010-01-10 00:00:00  18   2.0

I have tried the following but I get a list of nan:

df["test"] = pd.Series(df.index.map(dct), index=df.index).ffill()

Any suggestions?

CodePudding user response：

There are missing values, because no match types - in dict are keys like strings, in DaatFrame is datetimes in DatetimeIndex, need same types - here datetimes in helper Series created from dictionary with Series.asfreq for add datetimes between:

dct = {
    "2009-01-01": 1,
    "2010-01-05": 2,
    "2020-01-01": 3,
}


s = pd.Series(dct).rename(lambda x: pd.to_datetime(x)).asfreq('d', method='ffill')

df["test"] = df.index.to_series().dt.normalize().map(s)
print (df)
                      0  test
2010-01-01 00:00:00   0     1
2010-01-01 12:00:00   1     1
2010-01-02 00:00:00   2     1
2010-01-02 12:00:00   3     1
2010-01-03 00:00:00   4     1
2010-01-03 12:00:00   5     1
2010-01-04 00:00:00   6     1
2010-01-04 12:00:00   7     1
2010-01-05 00:00:00   8     2
2010-01-05 12:00:00   9     2
2010-01-06 00:00:00  10     2
2010-01-06 12:00:00  11     2
2010-01-07 00:00:00  12     2
2010-01-07 12:00:00  13     2
2010-01-08 00:00:00  14     2
2010-01-08 12:00:00  15     2
2010-01-09 00:00:00  16     2
2010-01-09 12:00:00  17     2
2010-01-10 00:00:00  18     2