Mapping between pandas dataframes to generate new columns

MAsoccer9 0
Viewed 23

I have two dataFrames as shown below:

df1 = 

temperature   Mon_start   Mon_end   Tues_start   Tues_end
cold           1:00        3:00        9:00        10:00
warm           7:00        8:00        16:00       20:00
hot            4:00        6:00        12:00       14:00

df2 = 

sample1     data_value
A              2:00     
A              7:30
B              18:00
B              9:45

I need to use the values in df2['data_value'] to find out what day an experiment was performed and what temperature it was using df1. So essentially using df1 as a lookup table to check for if data_value is between a given start and end time and for what temp and if so, assign its value in a new column called day with the day. The output I've been trying to get is:

sample1     data_value     day      temperature
A              2:00        Mon         cold
A              7:30        Mon         warm
B              18:00       Tues        warm
B              9:45        Tues        cold

The actual dataFrame is quite long, so I defined a function and did np.vectorize() to speed it up, but can't seem to get the mapping and new columns defined correctly.

Or do I need to do a for-loop and check over every combination of *_start and *_end to do so?

Any help would be greatly appreciated!

dataframe pandas python
1 Answers

If your data are valid, e.g. no row in df2 with 3:30, then you can use merge_asof:

# convert data to timedelta so we can compare correctly
for col in df1.columns[1:]:
    df1[col] = pd.to_timedelta(df1[col]+':00')
df2['data_value'] = pd.to_timedelta(df2['data_value'] + ':00')

pd.merge_asof(df2.sort_values('data_value'), 
              df1.melt('temperature', var_name='day').sort_values('value'),
              left_on='data_value', right_on='value')

Output:

  sample1      data_value temperature         day           value
0       A 0 days 02:00:00        cold   Mon_start 0 days 01:00:00
1       A 0 days 07:30:00        warm   Mon_start 0 days 07:00:00
2       B 0 days 09:45:00        cold  Tues_start 0 days 09:00:00
3       B 0 days 18:00:00        warm  Tues_start 0 days 16:00:00
Quang Hoang0
Related