algorithms/leetcode/1741-FindTotalTimeSpentbyEachEmployee.py at master · freewu/algorithms · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# 1741. Find Total Time Spent by Each Employee
# Table: Employees
# +-------------+------+
# | Column Name | Type |
# +-------------+------+
# | emp_id      | int  |
# | event_day   | date |
# | in_time     | int  |
# | out_time    | int  |
# +-------------+------+
# (emp_id, event_day, in_time) is the primary key of this table.
# The table shows the employees' entries and exits in an office.
# event_day is the day at which this event happened, in_time is the minute at which the employee entered the office, and out_time is the minute at which they left the office.
# in_time and out_time are between 1 and 1440.
# It is guaranteed that no two events on the same day intersect in time, and in_time < out_time.

# Write an SQL query to calculate the total time in minutes spent by each employee on each day at the office.
# Note that within one day, an employee can enter and leave more than once. The time spent in the office for a single entry is out_time - in_time.
# Return the result table in any order.
# The query result format is in the following example.

# Example 1:
# Input:
# Employees table:
# +--------+------------+---------+----------+
# | emp_id | event_day  | in_time | out_time |
# +--------+------------+---------+----------+
# | 1      | 2020-11-28 | 4       | 32       |
# | 1      | 2020-11-28 | 55      | 200      |
# | 1      | 2020-12-03 | 1       | 42       |
# | 2      | 2020-11-28 | 3       | 33       |
# | 2      | 2020-12-09 | 47      | 74       |
# +--------+------------+---------+----------+
# Output:
# +------------+--------+------------+
# | day        | emp_id | total_time |
# +------------+--------+------------+
# | 2020-11-28 | 1      | 173        |
# | 2020-11-28 | 2      | 30         |
# | 2020-12-03 | 1      | 41         |
# | 2020-12-09 | 2      | 27         |
# +------------+--------+------------+
# Explanation:
# Employee 1 has three events: two on day 2020-11-28 with a total of (32 - 4) + (200 - 55) = 173, and one on day 2020-12-03 with a total of (42 - 1) = 41.
# Employee 2 has two events: one on day 2020-11-28 with a total of (33 - 3) = 30, and one on day 2020-12-09 with a total of (74 - 47) = 27.

import pandas as pd

def total_time(employees: pd.DataFrame) -> pd.DataFrame:
    # 计算出 total_time
    employees['total_time'] = employees['out_time'] - employees['in_time']
    # 按 ["event_day","emp_id"] 分组并汇总统计 total_time
    employees = employees.groupby(["event_day","emp_id"])[['total_time']].sum().reset_index()
    # 按要求改列名
    employees = employees.rename(columns= {"event_day": "day"})
    # 按天排序
    return employees.sort_values("day")

if __name__ == "__main__":
    data = [['1', '2020-11-28', '4', '32'], ['1', '2020-11-28', '55', '200'], ['1', '2020-12-3', '1', '42'], ['2', '2020-11-28', '3', '33'], ['2', '2020-12-9', '47', '74']]
    employees = pd.DataFrame(data, columns=['emp_id', 'event_day', 'in_time', 'out_time']).astype({'emp_id':'Int64', 'event_day':'datetime64[ns]', 'in_time':'Int64', 'out_time':'Int64'})
    print(total_time(employees))