I have 2 dataframes:
labels:
import pandas as pd
marker_labels = pd.DataFrame({'cohort_id':[1,1, 1], 'marker_type':['a', 'b', 'a'], 'start':['2020-01-2', '2020-01-04 05', '2020-01-06'], 'end':[np.nan, '2020-01-05 16', np.nan]})
marker_labels['start'] = pd.to_datetime(marker_labels['start'])
marker_labels['end'] = pd.to_datetime(marker_labels['end'])
marker_labels.loc[marker_labels['end'].isnull(), 'end'] = marker_labels.start + pd.Timedelta(days=1) - pd.Timedelta(seconds=1)
and data:
import pandas as pd
from pandas import Timestamp
df = pd.DataFrame({'hour': {36: Timestamp('2020-01-04 04:00:00'), 37: Timestamp('2020-01-04 04:00:00'), 38: Timestamp('2020-01-04 04:00:00'), 39: Timestamp('2020-01-04 04:00:00'), 40: Timestamp('2020-01-04 04:00:00'), 41: Timestamp('2020-01-04 04:00:00'), 42: Timestamp('2020-01-04 04:00:00'), 43: Timestamp('2020-01-04 04:00:00'), 44: Timestamp('2020-01-04 04:00:00'), 45: Timestamp('2020-01-04 05:00:00'), 46: Timestamp('2020-01-04 05:00:00'), 47: Timestamp('2020-01-04 05:00:00'), 48: Timestamp('2020-01-04 05:00:00'), 49: Timestamp('2020-01-04 05:00:00'), 50: Timestamp('2020-01-04 05:00:00'), 51: Timestamp('2020-01-04 05:00:00'), 52: Timestamp('2020-01-04 05:00:00'), 53: Timestamp('2020-01-04 05:00:00')}, 'metrik_0': {36: -0.30098661551885625, 37: -0.6402837079024638, 38: -2.6953511655638778, 39: 0.4036062912674384, 40: -0.035627996627399204, 41: -0.06510225503176624, 42: -1.9745426914329782, 43: 1.4112111331287631, 44: 0.18641277342651516, 45: 0.10780795451690242, 46: 0.31822895003286417, 47: -1.0804164740649171, 48: -1.6676697601556636, 49: -1.0354359757914047, 50: 1.8570215568670299, 51: 0.9055795225472866, 52: -0.020539970820695173, 53: -0.7975048293123836}, 'cohort_id': {36: 1, 37: 1, 38: 1, 39: 1, 40: 1, 41: 1, 42: 1, 43: 1, 44: 1, 45: 1, 46: 1, 47: 1, 48: 1, 49: 1, 50: 1, 51: 1, 52: 1, 53: 1}, 'device_id': {36: 6, 37: 5, 38: 11, 39: 20, 40: 18, 41: 1, 42: 14, 43: 9, 44: 12, 45: 9, 46: 14, 47: 11, 48: 20, 49: 5, 50: 1, 51: 12, 52: 6, 53: 18}})
df
I want to perform a LEFT JOIN on the column cohort_id and time interval(hour is BETWEEN(start, end)).
Similar questions were:
So far I have multiple approaches but nut a final solution:
First one: slow, no fully outputted / accessible result in simple pandas columns:
def join_on_matching_interval(x):
result = marker_labels[(marker_labels.cohort_id == x.cohort_id) & (x.hour >= marker_labels.start) & (x.hour <= marker_labels.end)]
if len(result) == 0:
result = []
return result
df['marker_labels'] = df.apply(join_on_matching_interval, axis=1)
print(df.shape[0])
#df = df.explode('marker_labels') # this fails to work
df['size'] = df.marker_labels.apply(lambda x: len(x))
df[(df['size'] > 0)].head()
How could the results be made accessible as columns?
Second one: correct columns but invalid number of rows (and fast):
Following the links I shared above:
print(len(df))
print(len(marker_labels))
merged_res = df.merge(marker_labels, left_on=['cohort_id'], right_on=['cohort_id'], how='left')
print(len(merged_res)) # the number of rows has increased
merged_res = merged_res[(merged_res.hour.between(merged_res.start,merged_res.end)) | (merged_res.start.isnull())]
print(len(merged_res)) # but now not enough rows are left over.
In particular for 3 this means:
How could I include this 3rd case in the conditions?
Do you mean merge and query, then join back:
tmp = (df.reset_index()
.merge(marker_labels, on='cohort_id', how='left')
.query('start <= hour <= end')
.set_index('index')
.reindex(df.index)
)
out = tmp.combine_first(df)
Output:
cohort_id device_id end hour marker_type metrik_0 start
-- ----------- ----------- ------------------- ------------------- ------------- ---------- -------------------
36 1 6 NaT 2020-01-04 04:00:00 nan -0.300987 NaT
37 1 5 NaT 2020-01-04 04:00:00 nan -0.640284 NaT
38 1 11 NaT 2020-01-04 04:00:00 nan -2.69535 NaT
39 1 20 NaT 2020-01-04 04:00:00 nan 0.403606 NaT
40 1 18 NaT 2020-01-04 04:00:00 nan -0.035628 NaT
41 1 1 NaT 2020-01-04 04:00:00 nan -0.0651023 NaT
42 1 14 NaT 2020-01-04 04:00:00 nan -1.97454 NaT
43 1 9 NaT 2020-01-04 04:00:00 nan 1.41121 NaT
44 1 12 NaT 2020-01-04 04:00:00 nan 0.186413 NaT
45 1 9 2020-01-05 16:00:00 2020-01-04 05:00:00 b 0.107808 2020-01-04 05:00:00
46 1 14 2020-01-05 16:00:00 2020-01-04 05:00:00 b 0.318229 2020-01-04 05:00:00
47 1 11 2020-01-05 16:00:00 2020-01-04 05:00:00 b -1.08042 2020-01-04 05:00:00
48 1 20 2020-01-05 16:00:00 2020-01-04 05:00:00 b -1.66767 2020-01-04 05:00:00
49 1 5 2020-01-05 16:00:00 2020-01-04 05:00:00 b -1.03544 2020-01-04 05:00:00
50 1 1 2020-01-05 16:00:00 2020-01-04 05:00:00 b 1.85702 2020-01-04 05:00:00
51 1 12 2020-01-05 16:00:00 2020-01-04 05:00:00 b 0.90558 2020-01-04 05:00:00
52 1 6 2020-01-05 16:00:00 2020-01-04 05:00:00 b -0.02054 2020-01-04 05:00:00
53 1 18 2020-01-05 16:00:00 2020-01-04 05:00:00 b -0.797505 2020-01-04 05:00:00