How to merge two dataframe based on time intervals and transform them

1
votes

I have two dataframes, first one is creating by users manually and second one is errors from machines. I want to merge them based on time interval in first dataframe(df_a)

Here are the dataframes;

d_a = {'Station' : ['A1','A2'],
       'Reason_a' : ['Electronic','Feed'],
       'StartTime_a' : ['2019-01-02 02:00:00','2019-01-02 04:22:00'],
       'EndTime_a' : ['2019-01-02 02:20:00', '2019-01-02 04:45:00']}

d_b = {'Station' : ['A1','A1','A1','A2','A2','A2'],
       'Reason_b' : ['a','n','c','d','e','n'],
       'StartTime_b' : ['2019-01-02 00:00:00.000','2019-01-02 00:05:00.000','2019-01-01 23:55:00.000','2019-01-02 04:19:53.000','2019-01-02 04:19:37.000','2019-01-02 04:23:00.000'],
       'EndTime_b' : ['2019-01-02 00:19:15.000','2019-01-02 00:29:45.000','2019-01-02 00:12:12.000','2019-01-02 04:27:12.000','2019-01-02 04:47:16.000','2019-01-02 04:52:45.000']}

df_a = pd.DataFrame(d_a)
df_b = pd.DataFrame(d_b)

Any intersection point of time intervals of two dataframes considered as valid record.

condition1 = df_b start_time start after df_a start time and ends before df_a endtime

condition2 = df_b start_time starts before df_a start time but ends before df_a endtime

condition3 = df_b start_timestarts between df_a starttime and df_a end time but ends after df_a endtime

In the end I want to merge these two dataframes based on conditions. my ideal table looks like below

 Station     Reason_a        a     n     c     d    e 
  A1         Electronic      1     1     1     0    0
  A2          Feed           0     1     0     1    0

How should I approach this problem? Any comment would be helpful.

Thanks in advance.

4
pythonpandasdatetimeconditional-statementsnested-loops

4 Answers

2
votes

I would solve it by merging the tables on station and calculating the intersections :D

import numpy as np

df = pd.merge(df_a, df_b, on="Station")

# Convert to date
for datevar in ["StartTime_a", "StartTime_b", "EndTime_a", "EndTime_b"]:
    df[datevar] = pd.to_datetime(df[datevar])

# Intersections definition
df["intersection"] = (((df.StartTime_a > df.StartTime_b) & (df.StartTime_a < df.EndTime_b)) |
                      ((df.StartTime_a < df.StartTime_b) & (df.EndTime_a > df.StartTime_b)))

# Filter only intersections
(df[["Station", "Reason_a", "Reason_b", "intersection"]]
.pivot_table(index=["Station", "Reason_a"], columns="Reason_b", aggfunc=np.sum)
.fillna(0).astype(int))
1
votes

Its possible performing these types of merge using pandas merge_asof.

Assuming that 'Station' is an additional key to the merge process you can use something like this:

df_a['StartTime_a'] = pd.to_datetime(df_a['StartTime_a'])
df_b['StartTime_b'] = pd.to_datetime(df_b['StartTime_b'])
df_a['EndTime_a'] = pd.to_datetime(df_a['EndTime_a'])
df_b['EndTime_b'] = pd.to_datetime(df_b['EndTime_b'])
##before using merge_asof sorting is needed
df_a.sort_values(by='StartTime_a', inplace=True)
df_b.sort_values(by='StartTime_b', inplace=True)
##merge and filter based on first condition
cond_1 = pd.merge_asof(df_a, df_b, by='Station', left_on='StartTime_a', 
right_on='StartTime_b', direction='forward')
cond_1 = cond_1[cond_1['StartTime_b'] <= cond_1['EndTime_a']]
##merge and filter based on second condition
cond_2 = pd.merge_asof(df_a, df_b, by='Station', left_on='StartTime_a', 
right_on='StartTime_b', direction='backward')
cond_2 = cond_2[cond_2['EndTime_b'] <= cond_2['EndTime_a']]
##merge and filter based on third condition
cond_3 = pd.merge_asof(df_a, df_b, by='Station', left_on='StartTime_a', 
right_on='StartTime_b', direction='forward')
cond_3 = cond_3[cond_3['StartTime_b'] <= cond_3['EndTime_a']]
cond_3 = cond_3[cond_3['EndTime_b'] >= cond_3['EndTime_a']]
##concatenating all matches
res_df = pd.concat([cond_1, cond_2, cond_3], sort=False)
1
votes

I came up with this:

    df_c = pd.merge(df_a,df_b, left_on = 'Station', right_on = 'Station')

make datetime:

    df_c['StartTime_a'] = pd.to_datetime(df_c['StartTime_a'])
    df_c['StartTime_b'] = pd.to_datetime(df_c['StartTime_b'])
    df_c['EndTime_a'] = pd.to_datetime(df_c['EndTime_a'])
    df_c['EndTime_b'] = pd.to_datetime(df_c['EndTime_b'])

apply lambda function:

    df_c['c'] = df_c.apply(lambda x : 1 if (x.StartTime_b > x.StartTime_a) and (x.EndTime_b < x.EndTime_a) 
                   else (1 if  (x.StartTime_b < x.StartTime_a) and (x.EndTime_b < x.EndTime_a) 
                   else (1 if ((x.StartTime_b > x.StartTime_a) and (x.StartTime_b < x.EndTime_a)) and (x.EndTime_b > x.EndTime_a) else 0)), axis=1)

followed by:

    df_d = df_c.groupby(['Station','Reason_a','Reason_b'])['c'].sum().unstack()
    df_d.fillna(0, inplace=True)
0
votes

If you want to avoid the merge, go like this:

    def check_condition(x):

        df_1 = df_a[(df_a['StartTime_a'] < x.StartTime_b) & (df_a['EndTime_a'] > x.EndTime_b)]
        df_2 = df_a[(df_a['StartTime_a'] > x.StartTime_b) & (df_a['EndTime_a'] > x.EndTime_b)]
        df_3 = df_a[(df_a['StartTime_a'] < x.StartTime_b) & (df_a['EndTime_a'] > x.StartTime_b) 
            & (df_a['EndTime_a'] < x.EndTime_b)]

        if df_1.shape[0]+df_2.shape[0] + df_3.shape[0] !=0:
            return 1
        else:
            return 0



     df_b['c'] = df_b.apply(lambda x: check_condition(x), axis=1)