Home > Blockchain >  Assign dates to predetermined periods using pandas
Assign dates to predetermined periods using pandas

Time:10-01

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
  • Related