Python是否可以使用循环函数来跟踪一个人在整个时间内的移动并将其与其他人分组？

Question

我的问题是，我想全程跟踪ID，看看他们下一步会去哪里，并将他们与其他人分组作为他们的第一个位置点。目前我正在使用excel按日期和ID排序。按日期排序时，我知道每个人第一次访问某个地点的地点。如果我删除了这些ID的第一个实例，那么剩下的就是它们的下一个位置。然后删除这些实例，依此类推

以下是一个示例数据集：

ID  Location    Date
76  School      4/12/2018
111 Post Office 4/15/2018
112 School      4/10/2018
324 School      2/10/2018
22  Library     4/12/2018
19  Library     4/13/2028
17  Post Office 5/11/2018
76  Library     4/25/2018
19  Library     4/27/2019
112 School      3/23/2018
76  Post Office 4/27/2018
113 Ice Cream   5/23/2018
19  School      7/23/2019
112 Library     3/23/2018
76  Ice Cream   6/4/2019
112 Fountain    6/10/2019

以下是预期输出：

ID  Location    Date       Group
76  School      4/12/2018  1
111 Post Office 4/15/2018  1
112 School      4/10/2018  2
324 School      2/10/2018  1
22  Library     4/12/2018  1
19  Library     4/13/2028  1 
17  Post Office 5/11/2018  1
76  Library     4/25/2018  2
19  Library     4/27/2019  2
112 School      3/23/2018  1
76  Post Office 4/27/2018  3
113 Ice Cream   5/23/2018  1
19  School      7/23/2019  1
112 Library     3/23/2018  1
76  Ice Cream   6/4/2019   4
112 Fountain    6/10/2019  3

输出应该有一个新的列，其中它根据ID的第一个位置（按日期）对ID进行分组，然后第二个组应该是这些相同的人下一个旅行的地方，等等

任何帮助都将不胜感激。我知道如何将文件加载到python之类的程序中，但就我的一生而言，我在为上述程序创建函数时遇到了难以置信的麻烦。再次感谢您的帮助

Answer 1

以下是我使用熊猫的答案。假设您有csv文件中的数据，我们可以执行以下操作：

import pandas as pd

df = pd.read_csv('Sample.csv')
gdf = pd.DataFrame()

#Change to datetime for rank operation
df.Date = pd.to_datetime(df.Date)
df = df.sort_values('Date')

# Rank by date and do a dense rank to avoid same date as same rank
gdf['Rank'] = df.groupby('ID')['Date'].rank(method='dense')
result = df.join(gdf)

# Sort to match original order of table
result = result.sort_index()

print(result)

     ID    Location       Date  Rank
0    76      School 2018-04-12   1.0
1   111  PostOffice 2018-04-15   1.0
2   112      School 2018-04-10   2.0
3   324      School 2018-02-10   1.0
4    22     Library 2018-04-12   1.0
5    19     Library 2018-04-13   1.0
6    17  PostOffice 2018-05-11   1.0
7    76     Library 2018-04-25   2.0
8    19     Library 2019-04-27   2.0
9   112      School 2018-03-23   1.0
10   76  PostOffice 2018-04-27   3.0
11  113    IceCream 2018-05-23   1.0
12   19      School 2019-07-23   3.0
13  112     Library 2018-03-23   1.0
14   76    IceCream 2019-06-04   4.0
15  112    Fountain 2019-06-10   3.0

注意：我认为这一行的结果中有一个小错误：

19  School      7/23/2019  1

Answer 2

假设我们有一个您提到的CSV数据集（去掉第一行）：

76  School      4/12/2018
111 Post Office 4/15/2018
112 School      4/10/2018
324 School      2/10/2018
22  Library     4/12/2018
19  Library     4/13/2028
17  Post Office 5/11/2018
76  Library     4/25/2018
19  Library     4/27/2019
112 School      3/23/2018
76  Post Office 4/27/2018
113 Ice Cream   5/23/2018
19  School      7/23/2019
112 Library     3/23/2018
76  Ice Cream   6/4/2019
112 Fountain    6/10/2019

然后，我们可以使用自定义排序（）按您想要的方式对数据进行排序：

import csv
import datetime

l = []

with open('stack.csv', 'r') as file:
    reader = csv.reader(file)
    for row in reader:
        l.append(row)


l.sort(key = lambda x: (int(x[0]), datetime.datetime.strptime(x[2], '%m/%d/%Y')))
[print(i) for i in l]

这将为您提供以下输出（按ID和日期排序）：

['17', 'PO', '05/11/2018']
['19', 'L', '04/27/2019']
['19', 'S', '07/23/2019']
['19', 'L', '04/13/2028']
['22', 'L', '04/12/2018']
['76', 'S', '04/12/2018']
['76', 'L', '04/25/2018']
['76', 'IC', '06/04/2019']
['76', 'PO', '04/27/2020']
['111', 'PO', '04/15/2018']
['112', 'S', '02/23/2018']
['112', 'L', '03/23/2018']
['112', 'S', '04/10/2018']
['112', 'F', '06/10/2019']
['113', 'IC', '05/23/2018']
['324', 'S', '02/10/2018']

可以使用for循环将组添加到此输出中：

f_id = l[0][0]
group = 1
for i in l:
    if f_id != i[0]:
        group = 1
        f_id = i[0]
    i.append(group)
    group+=1

这将获得您的输出：

['17', 'PO', '05/11/2018', 1]
['19', 'L', '04/27/2019', 1]
['19', 'S', '07/23/2019', 2]
['19', 'L', '04/13/2028', 3]
['22', 'L', '04/12/2018', 1]
['76', 'S', '04/12/2018', 1]
['76', 'L', '04/25/2018', 2]
['76', 'IC', '06/04/2019', 3]
['76', 'PO', '04/27/2020', 4]
['111', 'PO', '04/15/2018', 1]
['112', 'S', '02/23/2018', 1]
['112', 'L', '03/23/2018', 2]
['112', 'S', '04/10/2018', 3]
['112', 'F', '06/10/2019', 4]
['113', 'IC', '05/23/2018', 1]
['324', 'S', '02/10/2018', 1]

然后，您可以使用标题将此列表写回CSV文件