How to compare transaction time point within time range of workers to add employee into transaction table in pandas?

user_5967613 0
Viewed 77

I would like to calculate workers tips. Several workers worked on each transaction. I have 2 dataframes, ts_df (for transaction) and shift_df (for workers shift), to calculate the tip amount for each workes.

Transaction table example:

>>> ts_df.head()
         Date      Time   tip
1  2021-03-01  09:00:39  2.40
2  2021-03-01  09:27:39  5.45
4  2021-03-01  09:54:50  2.00
5  2021-03-01  09:57:37  2.10
6  2021-03-01  10:35:56  2.57
>>> ts_df.dtypes
Date     object
Time     object
tip     float64
dtype: object

Here is example of shift transaction:

>>> shift_df.head()
         Date Clock_start Clock_end       employee
0  2021-03-01    08:30:00  14:30:00           Jose
1  2021-03-01    09:30:00  15:30:00          April
2  2021-03-02    06:00:00  07:30:00           John
3  2021-03-02    08:30:00  14:30:00           Jose
4  2021-03-02    09:30:00  15:30:00          April
>>> shift_df.dtypes
Date           object
Clock_start    object
Clock_end      object
employee       object
dtype: object

All date and time objects are python date() and time() objects.

For example, Jose and April worked for the transaction[4] (4 2021-03-01 09:54:50 2.00). Jose and April should gets 1.00 each. What will be the best way?

Waht I am considering is that list of workers for the transaction can be added in a list to calculate the tip amount. Something like this:

In ts_df:

         Date      Time   tip      workers
1  2021-03-01  09:00:39  2.40      ['Jose']
2  2021-03-01  09:27:39  5.45      ['Jose']
4  2021-03-01  09:54:50  2.00      ['Jose', 'April']
5  2021-03-01  09:57:37  2.10      ['Jose', 'April']
6  2021-03-01  10:35:56  2.57      ['Jose', 'April']

Please teach me how to compare two independent tables to calculate worker's tip.

Thanks in advance!

pandas python
2 Answers

A solution could be to apply a Cartesian product between both dataframes, and then filter on the condition that Clock_start <= Time <= Clock_end and Date_ts == Date_shift. Something like this:

df = ts_df.merge(shift_df, how='cross', suffixes=('_ts', '_shift')
df.loc[(df.Date_ts==df.Date_shift) & (df.Clock_start <= df.Time) & (df.Time <= df.Clock_end)]

This should leave you with duplicated transactions, since each transaction is repeated for each shift. To solve that, you could add an identifier to ts_df beforehand and then apply df.groupby(by=identifier) to the resulting df.

user_65756480

Reproduce the original dataframe:

ts_df = pd.DataFrame(
    [
     ['2021-03-01', '09:00:39', 2.4],
     ['2021-03-01', '09:27:39', 5.45],
     ['2021-03-01', '09:54:50', 2.0],
     ['2021-03-01', '09:57:37', 2.1],
     ['2021-03-01', '10:35:36', 2.57]
    ]
)
ts_df.columns=['Date', 'Time', 'tip']

shift_df = pd.DataFrame(
    [
     ['2021-03-01', '08:30:00', '14:30:00', 'Jose'],
     ['2021-03-01', '09:30:00', '15:30:00', 'April'],
     ['2021-03-02', '06:00:00', '07:30:00', 'John'],
     ['2021-03-02', '08:30:00', '14:30:00', 'Jose'],
     ['2021-03-02', '09:30:00', '15:30:00', 'April']
    ]
)

shift_df.columns = ['Date', 'Clock_start', 'Clock_end', 'employee']

ts_df['Date'] = pd.to_datetime(ts_df['Date']).dt.date
ts_df['Time'] = pd.to_datetime(ts_df['Time'], format = '%H:%M:%S').dt.time

shift_df['Date'] = pd.to_datetime(shift_df['Date']).dt.date
shift_df['Clock_start'] = (
    pd.to_datetime(shift_df['Clock_start'], format = '%H:%M:%S').dt.time
)
shift_df['Clock_end'] = (
    pd.to_datetime(shift_df['Clock_end'], format = '%H:%M:%S').dt.time
)

Now we need to cross join and filter like @Miguel said!

temp_df = ts_df.merge(
    shift_df
)

ts_df = (
    temp_df.loc[
        (temp_df.Clock_start <= temp_df.Time) &
        (temp_df.Time <= temp_df.Clock_end)
    ]
)

cross-join

Cool now we have every instance of a worker and a tip they got but we still don't have the list that you wanted with only one row for each tip

ts_df = (
    ts_df
    .groupby(['Date', 'Time', 'tip'])['employee']
    .apply(list)
    .reset_index(name='workers')
)

list of workers

user_48291580
Related