Say we have 2 df--please use pd.read_clipboard()
:
df_1
Date
1995-03-12
1980-11-30
1972-03-05
1978-12-03
df_2
Period From To
39 1971-11-29 1975-11-30
40 1975-01-12 1979-11-25
41 1979-11-26 1983-11-27
42 1983-11-28 1987-11-29
43 1987-11-30 1991-11-24
44 1991-11-25 1995-12-03
45 1995-04-12 1999-12-05
Assume too that all dates are in format datetime64[ns]
What I need is to assign the appropriate Period
number from df_2
to each date of df_1
.
Desired output for df_1
:
Date Period
1995-03-12 44
1980-11-30 41
1972-03-05 39
1978-12-03 40
I.e., a Period
column was added to df_1
, containing the appropriate period number as defined in df_2
.
Note df_2
should remain untouched, nor should it be merged with df_1
.
The above is a sample. The actual df_1
has hundreds of dates, and the actual df_2
has fifty periods. Both also have many more columns.
Thank you
CodePudding user response:
Let us try IntervalIndex
df2.index = pd.IntervalIndex.from_arrays(df2.From,df2.To)
df1['Period'] = df2.loc[df1.Date,'Period'].values
df1
Out[16]:
Date Period
0 1995-03-12 44
1 1980-11-30 41
2 1972-03-05 39
3 1978-12-03 40