将每日状态表缩减为仅包含状态更改

Question

我有一个大的(570m行(每日状态表，可供100k以上的用户使用。目前它是MySQL(或CSV(。该表包含三列：user_id、status和date。理想情况下，我想将该表简化为一个新表，其中包括每个状态周期的user_id、status、start_date和end_date(其中周期至少是一个日期(。

挑战在于用户可以在状态之间来回切换：我不能假设周期被定义为给定状态的最小日期和最大日期之间的差。

我可以使用MySQL、Python或使用终端(mac(的解决方案。

当前数据：

user_id, status, date
1, GRE, 2018-09-02
1, GRE, 2018-09-03
1, PRO, 2018-09-04
1, PRO, 2018-09-05
1, PRO, 2018-09-06
1, GRE, 2018-09-07
1, GRE, 2018-09-08
1, GRE, 2018-09-09
1, GRE, 2018-09-10

所需的新格式：

user_id, status, start_date, end_date
1, GRE, 2018-09-02, 2018-09-03
1, PRO, 2018-09-04, 2018-09-06
1, GRE, 2018-09-07, 2018-09-10

Answer 1

考虑以下内容：

架构

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(user_id INT NOT NULL
,status CHAR(3) NOT NULL
,date DATE NOT NULL
,PRIMARY KEY(user_id,date)
);
INSERT INTO my_table VALUES
(1, 'GRE', '2018-09-02'),
(1, 'GRE', '2018-09-03'),
(1, 'PRO', '2018-09-04'),
(1, 'PRO', '2018-09-05'),
(1, 'PRO', '2018-09-06'),
(1, 'GRE', '2018-09-07'),
(1, 'GRE', '2018-09-08'),
(1, 'GRE', '2018-09-09'),
(1, 'GRE', '2018-09-10'),
(2, 'GRE', '2018-09-02'),
(2, 'GRE', '2018-09-03'),
(2, 'PRO', '2018-09-04'),
(2, 'PRO', '2018-09-05'),
(2, 'PRO', '2018-09-06'),
(2, 'GRE', '2018-09-07'),
(2, 'GRE', '2018-09-08'),
(2, 'GRE', '2018-09-09'),
(2, 'GRE', '2018-09-10');

查询

WITH t AS (
SELECT user_id
, status
, date
, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY date) 
- DENSE_RANK() OVER (PARTITION BY user_id,status ORDER BY DATE) grp
FROM my_table
)
SELECT t.user_id
, t.status
, MIN(t.date) start
, MAX(t.date) finish
FROM t
GROUP  
BY user_id
, status
, grp
ORDER  
BY user_id
, start;

+---------+--------+------------+------------+
| user_id | status | start      | finish     |
+---------+--------+------------+------------+
|       1 | GRE    | 2018-09-02 | 2018-09-03 |
|       1 | PRO    | 2018-09-04 | 2018-09-06 |
|       1 | GRE    | 2018-09-07 | 2018-09-10 |
|       2 | GRE    | 2018-09-02 | 2018-09-03 |
|       2 | PRO    | 2018-09-04 | 2018-09-06 |
|       2 | GRE    | 2018-09-07 | 2018-09-10 |
+---------+--------+------------+------------+

Answer 2

首先，我们需要在数据帧中添加另一列，以区分数据帧开头和结尾的"GRE"状态(以及类似的其他重复状态(。换句话说，需要跟踪状态何时发生变化。

df['track_id'] = (df.status.shift() != df.status).cumsum()

df:

user_id status        date  track_id
0        1    GRE  2018-09-02         1
1        1    GRE  2018-09-03         1
2        1    PRO  2018-09-04         2
3        1    PRO  2018-09-05         2
4        1    PRO  2018-09-06         2
5        1    GRE  2018-09-07         3
6        1    GRE  2018-09-08         3
7        1    GRE  2018-09-09         3
8        1    GRE  2018-09-10         3

然后，对于status,track_id的每个组合，使用groupby()取出第一行和最后一行

df_f = df.groupby(['status','track_id']).first().reset_index()
df_l = df.groupby(['status','track_id']).last().reset_index()

df_f:

status  track_id  user_id        date
0    GRE         1        1  2018-09-02
1    GRE         3        1  2018-09-07
2    PRO         2        1  2018-09-04

df_l:

status  track_id  user_id        date
0    GRE         1        1  2018-09-03
1    GRE         3        1  2018-09-10
2    PRO         2        1  2018-09-06

将df_f和df_l中的date列分别重命名为start_date和end_date：

df_f.rename(columns = {'date':'start_date'}, inplace=True)
df_l.rename(columns = {'date':'end_date'}, inplace=True)

最后合并两个数据帧以获得您想要的格式：

final_df = df_f.merge(df_l, on=['status','track_id','user_id'])

final_df:

final_df.sort_values('track_id')
status  track_id  user_id  start_date    end_date
0    GRE         1        1  2018-09-02  2018-09-03
2    PRO         2        1  2018-09-04  2018-09-06
1    GRE         3        1  2018-09-07  2018-09-10

注意，我为user_id = 1做了整个操作，只是为了给你一个想法。对于更多的user_id，您需要首先过滤掉它们，对它们单独应用上述操作，然后在最后进行concat。

Answer 3

使用Python Pandas

import pandas as pd
def format_data(filenm):
# Load CSV to Dataframe
df = pd.read_csv(filenm, skipinitialspace=True)

# Add a column that allows grouping consecutive rows with same status
#  (note: technique from https://towardsdatascience.com/pandas-dataframe-group-by-consecutive-same-values-128913875dba)
df['same'] = (df['status'] != df['status'].shift()).cumsum()

# Get dates fro grouped user id and rows with same status
g = df.groupby(['user_id', 'same'])['date']

# Add start and stop dates based upon above grouping
df = df.assign(startdate = g.transform('first'), enddate = g.transform('last'))

# Return Dataframe with userid, status, startdate, enddate
return df.groupby(['user_id', 'same']).agg({
'status': 'first',                     # use first item in group
'startdate': 'first',                  # use first item in group
'enddate': 'last'                      # use last item in group
}).reset_index().drop('same', 1)         # Drop 'same' column (only used for grouping)

用法

result = format_data('myfile.txt')

输入myfile.txt

(在OP数据中添加了第二个用户id用于演示(

user_id, status, date
1, GRE, 2018-09-02
1, GRE, 2018-09-03
1, PRO, 2018-09-04
1, PRO, 2018-09-05
1, PRO, 2018-09-06
1, GRE, 2018-09-07
1, GRE, 2018-09-08
1, GRE, 2018-09-09
1, GRE, 2018-09-10
2, GRE, 2018-09-08
2, GRE, 2018-09-09
2, PRO, 2018-09-10

结果

user_id status  startdate   enddate
0   1   GRE 2018-09-02  2018-09-03
1   1   PRO 2018-09-04  2018-09-06
2   1   GRE 2018-09-07  2018-09-10
3   2   GRE 2018-09-08  2018-09-09
4   2   PRO 2018-09-10  2018-09-10