为具有对应前索引/相同跟踪的案例设置唯一标识符
假设我有以下的数据集：
| 个人编号 | 类别 | 年份 | 月份 | 索引_ID | 前一个索引_ID |
|---|---|---|---|---|---|
| 1 | 100 | 2022 | 8 | 42100 | |
| 1 | 100 | 2022 | 9 | 9534 | 42100 |
| 1 | 9400 | 2023 | 9 | 4 | |
| 1 | 9400 | 2023 | 10 | 485 | 4 |
| 2 | 100 | 2022 | 1 | 214 | 102 |
| 2 | 100 | 2022 | 2 | 194231 | 214 |
| 3 | 200 | 2022 | 2 | 2111 | |
| 3 | 200 | 2022 | 3 | 1012 | 2111 |
| 3 | 200 | 2022 | 4 | 9876 | 1012 |
| 3 | 200 | 2022 | 5 | 8794 | 9876 |
| 3 | 200 | 2022 | 6 | 24142 | 8794 |
| 4 | 100 | 2022 | 4 | 42100 | |
| 4 | 200 | 2022 | 7 | 12 | |
| 4 | 200 | 2022 | 8 | 14 | 12 |
| 4 | 200 | 2022 | 9 | 485 | 14 |
第一列（个人编号）是一个数字，用来标识一个人。还有一列（类别）表示类别。接下来是年份和月份（年份 月份）。还有一个索引列（索引_ID），最重要的是一个列，说明一个参考，即某个案例可能关联的前一个索引（前一个索引_ID）。所以，让我们把它说得更简单易懂：
第一个案例属于个人1，类别是100。我们有两个条目属于这个案例。它的索引是42100。下一个记录的索引是9534，它与第一个记录有关，因为“前一个索引_ID”这一列的值是42100。
第二个案例属于个人1，类别是9400。我们有两个条目属于这个案例。它的索引是4。下一个记录的索引是485，它与第一个记录有关，因为“前一个索引_ID”这一列的值是4。
第三个案例：
2;100;2022;1;214;102
2;100;2022;2;194231;214
属于个人2，类别是100。在这里我们可以看到，我们的数据集中没有第一个记录，它的索引应该是102。
接下来是个人3，有5条记录：
3;200;2022;2;2111;
3;200;2022;3;1012;2111
3;200;2022;4;9876;1012
3;200;2022;5;8794;9876
3;200;2022;6;24142;8794
这就是一个案例。
现在我想添加一列，给每个案例一个唯一标识符。
我的代码如下：
import pandas as pd
myfile = pd.read_csv(r"C:\pathtofile\testfile.csv", sep=";")
myfile['newID'] = myfile.groupby(['Personalnumber','Category'], sort=False).ngroup().add(1)
print(myfile)
结果确实是我想要的：
Personalnumber Category Year Month Index_ID Previous_Index_ID newID
0 1 100 2022 8 42100 NaN 1
1 1 100 2022 9 9534 42100.0 1
2 1 9400 2023 9 4 NaN 2
3 1 9400 2023 10 485 4.0 2
4 2 100 2022 1 214 102.0 3
5 2 100 2022 2 194231 214.0 3
6 3 200 2022 2 2111 NaN 4
7 3 200 2022 3 1012 2111.0 4
8 3 200 2022 4 9876 1012.0 4
9 3 200 2022 5 8794 9876.0 4
10 3 200 2022 6 24142 8794.0 4
11 4 100 2022 4 42100 NaN 5
12 4 200 2022 7 12 NaN 6
12 4 200 2022 8 14 12 6
12 4 200 2022 9 485 14 6
新ID这一列显示了正确的案例编号。
现在又出现了一个新的案例：
1;100;2022;8;101;
1;100;2022;9;204;101
1;100;2022;10;4344;204
1;100;2022;11;2069;4344
这个案例也属于个人1，类别是100。现在数据看起来是这样的：
| 个人编号 | 类别 | 年份 | 月份 | 索引_ID | 前一个索引_ID |
|---|---|---|---|---|---|
| 1 | 100 | 2022 | 8 | 42100 | |
| 1 | 100 | 2022 | 8 | 101 | |
| 1 | 100 | 2022 | 9 | 9534 | 42100 |
| 1 | 100 | 2022 | 9 | 204 | 101 |
| 1 | 100 | 2022 | 10 | 4344 | 204 |
| 1 | 100 | 2022 | 11 | 2069 | 4344 |
| 1 | 9400 | 2023 | 9 | 4 | |
| 1 | 9400 | 2023 | 10 | 485 | 4 |
| 2 | 100 | 2022 | 1 | 214 | 102 |
| 2 | 100 | 2022 | 2 | 194231 | 214 |
| 3 | 200 | 2022 | 2 | 2111 | |
| 3 | 200 | 2022 | 3 | 1012 | 2111 |
| 3 | 200 | 2022 | 4 | 9876 | 1012 |
| 3 | 200 | 2022 | 5 | 8794 | 9876 |
| 3 | 200 | 2022 | 6 | 24142 | 8794 |
| 4 | 100 | 2022 | 4 | 42100 | |
| 4 | 200 | 2022 | 7 | 12 | |
| 4 | 200 | 2022 | 8 | 14 | 12 |
| 4 | 200 | 2022 | 9 | 485 | 14 |
正如你所看到的，数据变得混乱了，我的代码导致了错误的结果。原因是新的案例落在了同一个“位置”，它也属于类别100，并且属于个人1。然而，从索引_ID和前一个索引_ID这两列可以看出这是另一个案例。这两列显示了可以区分它们的线索，表明这两个案例是不同的。（当然，也可能还有更多的案例“落在同一个位置”，所以这并不局限于这里的两个案例。）所以我现在的问题是，如何得到以下想要的输出：
Personalnumber Category Year Month Index_ID Previous_Index_ID newID
0 1 100 2022 8 42100 NaN 1
1 1 100 2022 8 101 NaN 2
2 1 100 2022 9 9534 42100.0 1
3 1 100 2022 9 204 101.0 2
4 1 100 2022 10 4344 204.0 2
5 1 100 2022 11 2069 4344.0 2
6 1 9400 2023 9 4 NaN 3
7 1 9400 2023 10 485 4.0 3
8 2 100 2022 1 214 102.0 4
9 2 100 2022 2 194231 214.0 4
10 3 200 2022 2 2111 NaN 5
11 3 200 2022 3 1012 2111.0 5
12 3 200 2022 4 9876 1012.0 5
13 3 200 2022 5 8794 9876.0 5
14 3 200 2022 6 24142 8794.0 5
15 4 100 2022 4 42100 NaN 6
16 4 200 2022 7 12 NaN 7
16 4 200 2022 8 14 12 7
16 4 200 2022 9 485 14 7
我该怎么做？
索引_ID在整个数据集中并不是唯一的，它只在每年每月内是唯一的。所以你可以看到，索引_ID 42100在2022年8月（个人编号1）出现过，也在2022年4月（个人编号4）出现过。或者索引_ID 485在2023年10月（个人编号1）出现过，也在2022年9月（个人编号4）出现过。不过，当然，它在每年每月内是唯一的。
（这些索引数字是完全随机的。所以在索引_ID或前一个索引_ID列上进行升序或降序排序并不是解决方案。）
编辑关于我对Muhammed Samed Özmen回答的评论：
考虑以下示例：
Personalnumber;Category;Year;Month;Index_ID;Previous_Index_ID
398;14;2022;1;10708;1
398;14;2022;2;50242;10708
398;14;2022;3;76850;50242
398;14;2022;4;120861;76850
398;14;2022;5;110883;120861
398;14;2022;6;188043;110883
398;14;2022;7;9432;188043
398;14;2022;8;175715;9432
398;14;2022;9;142837;175715
398;14;2022;10;152659;142837
398;14;2022;11;52335;152659
398;14;2022;12;156366;52335
398;14;2023;1;16416;156366
398;14;2023;2;163499;16416
398;14;2023;3;1;163499
在最后一行（398;14;2023;3;1;163499）时，代码抛出了递归错误。我认为递归错误可能是由于398;14;2022;1;10708;1和398;14;2023;3;1;163499引起的。
但是如果我把最后一条记录改为索引_ID = 2，像这样：
Personalnumber;Category;Year;Month;Index_ID;Previous_Index_ID
398;14;2022;1;10708;1
398;14;2022;2;50242;10708
398;14;2022;3;76850;50242
398;14;2022;4;120861;76850
398;14;2022;5;110883;120861
398;14;2022;6;188043;110883
398;14;2022;7;9432;188043
398;14;2022;8;175715;9432
398;14;2022;9;142837;175715
398;14;2022;10;152659;142837
398;14;2022;11;52335;152659
398;14;2022;12;156366;52335
398;14;2023;1;16416;156366
398;14;2023;2;163499;16416
398;14;2023;3;2;163499
那么它就可以正常工作，并且为这个案例设置了一个新的ID，正如它应该的那样（所有这些记录都属于一个案例）。
2 个回答
2
如果当前行不是空的，它会从这一行中提取出Previous_Index_ID。如果Previous_Index_ID不是NaN（也就是说，它有一个有效的值），这就意味着有一个之前的索引可以跟随。在这种情况下，它会递归地调用find_root_index函数，传入personalnumber、category和prev_index_id。这个递归调用会一直进行，直到找到一个根索引或者到达链的末尾。
对数据表中的每一行应用一个lambda函数，以计算该行的新ID。它会获取当前行的Personalnumber、Category和Index_ID。然后调用find_root_index函数来找到当前案例的根索引。接着，它使用root_to_newID字典将根索引映射到相应的新ID。如果没有找到根索引，就将newID赋值为None。最后，将新ID列的值设置为lambda函数计算出的结果。
import pandas as pd
data = {
"Personalnumber": [1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4],
"Category": [100, 100, 100, 100, 100, 100, 9400, 9400, 100, 100, 200, 200, 200, 200, 200, 100, 200, 200, 200],
"Year": [2022, 2022, 2022, 2022, 2022, 2022, 2023, 2023, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022],
"Month": [8, 8, 9, 9, 10, 11, 9, 10, 1, 2, 2, 3, 4, 5, 6, 4, 7, 8, 9],
"Index_ID": [42100, 101, 9534, 204, 4344, 2069, 4, 485, 214, 194231, 2111, 1012, 9876, 8794, 24142, 42100, 12, 14, 485],
"Previous_Index_ID": [None, None, 42100, 101, 204, 4344, None, 4, 102, 214, None, 2111, 1012, 9876, 8794, None, None, 12, 14]
}
myfile = pd.DataFrame(data)
root_to_newID = {}
current_id = 1
def find_root_index(personalnumber, category, index_id):
row = myfile[(myfile['Personalnumber'] == personalnumber) &
(myfile['Category'] == category) &
(myfile['Index_ID'] == index_id)]
if row.empty:
return index_id
prev_index_id = row['Previous_Index_ID'].iloc[0]
if pd.notna(prev_index_id):
return find_root_index(personalnumber, category, prev_index_id)
return index_id
for _, row in myfile.iterrows():
root_index = find_root_index(row['Personalnumber'], row['Category'], row['Index_ID'])
if root_index is not None and (row['Personalnumber'], row['Category'], root_index) not in root_to_newID:
root_to_newID[(row['Personalnumber'], row['Category'], root_index)] = current_id
current_id += 1
myfile['newID'] = myfile.apply(lambda row: root_to_newID.get((row['Personalnumber'], row['Category'], find_root_index(row['Personalnumber'], row['Category'], row['Index_ID'])), None), axis=1)
print(myfile)
输出将会是这样的
Personalnumber Category Year Month Index_ID Previous_Index_ID newID
0 1 100 2022 8 42100 NaN 1
1 1 100 2022 8 101 NaN 2
2 1 100 2022 9 9534 42100.0 1
3 1 100 2022 9 204 101.0 2
4 1 100 2022 10 4344 204.0 2
5 1 100 2022 11 2069 4344.0 2
6 1 9400 2023 9 4 NaN 3
7 1 9400 2023 10 485 4.0 3
8 2 100 2022 1 214 102.0 4
9 2 100 2022 2 194231 214.0 4
10 3 200 2022 2 2111 NaN 5
11 3 200 2022 3 1012 2111.0 5
12 3 200 2022 4 9876 1012.0 5
13 3 200 2022 5 8794 9876.0 5
14 3 200 2022 6 24142 8794.0 5
15 4 100 2022 4 42100 NaN 6
16 4 200 2022 7 12 NaN 7
17 4 200 2022 8 14 12.0 7
18 4 200 2022 9 485 14.0 7
对于新的示例集。
import pandas as pd
data = {
"Personalnumber": [398]*14,
"Category": [14]*14,
"Year": [2022]*12 + [2023]*2,
"Month": list(range(1, 13)) + [1, 2],
"Index_ID": [10708, 50242, 76850, 120861, 110883, 188043, 9432, 175715, 142837, 152659, 52335, 156366, 16416, 163499],
"Previous_Index_ID": [1, 10708, 50242, 76850, 120861, 110883, 188043, 9432, 175715, 142837, 152659, 52335, 156366, 16416]
}
myfile = pd.DataFrame(data)
root_to_newID = {}
current_id = 1
def find_root_index(personalnumber, category, index_id):
row = myfile[(myfile['Personalnumber'] == personalnumber) &
(myfile['Category'] == category) &
(myfile['Index_ID'] == index_id)]
if row.empty:
return index_id
prev_index_id = row['Previous_Index_ID'].iloc[0]
if pd.notna(prev_index_id):
return find_root_index(personalnumber, category, prev_index_id)
return index_id
for _, row in myfile.iterrows():
root_index = find_root_index(row['Personalnumber'], row['Category'], row['Index_ID'])
if root_index is not None and (row['Personalnumber'], row['Category'], root_index) not in root_to_newID:
root_to_newID[(row['Personalnumber'], row['Category'], root_index)] = current_id
current_id += 1
myfile['newID'] = myfile.apply(lambda row: root_to_newID.get((row['Personalnumber'], row['Category'], find_root_index(row['Personalnumber'], row['Category'], row['Index_ID'])), None), axis=1)
print(myfile)
示例输出是
Personalnumber Category Year Month Index_ID Previous_Index_ID newID
0 398 14 2022 1 10708 1 1
1 398 14 2022 2 50242 10708 1
2 398 14 2022 3 76850 50242 1
3 398 14 2022 4 120861 76850 1
4 398 14 2022 5 110883 120861 1
5 398 14 2022 6 188043 110883 1
6 398 14 2022 7 9432 188043 1
7 398 14 2022 8 175715 9432 1
8 398 14 2022 9 142837 175715 1
9 398 14 2022 10 152659 142837 1
10 398 14 2022 11 52335 152659 1
11 398 14 2022 12 156366 52335 1
12 398 14 2023 1 16416 156366 1
13 398 14 2023 2 163499 16416 1
1
我们先来看一下这个说法：
Index_ID在整个数据集中并不是唯一的，它只在每年每月内是唯一的。
这意味着 Previous_Index_ID 指向的是某个固定的过去时间段的 Index_ID。根据提供的数据，我猜测 Previous_Index_ID 是上一个日历月的 Index_ID。
我们的计划是把对应的上一个记录的索引和当前的记录连接起来，然后用唯一的ID替换组的根节点，并把指向之前行的指针替换为它们所指向记录中的相应数据。
首先，我们要准备好可以使用的数据：
import pandas as pd
from io import StringIO
data = '''Personalnumber,Category,Year,Month,Index_ID,Previous_Index_ID
1,100,2022,8,42100,
1,100,2022,8,101,
1,100,2022,9,9534,42100.0
1,100,2022,9,204,101.0
1,100,2022,10,4344,204.0
1,100,2022,11,2069,4344.0
1,9400,2023,9,4,
1,9400,2023,10,485,4.0
2,100,2022,1,214,102.0
2,100,2022,2,194231,214.0
3,200,2022,2,2111,
3,200,2022,3,1012,2111.0
3,200,2022,4,9876,1012.0
3,200,2022,5,8794,9876.0
3,200,2022,6,24142,8794.0
4,100,2022,4,42100,
4,200,2022,7,12,
4,200,2022,8,14,12.0
4,200,2022,9,485,14.0'''
df = pd.read_csv(StringIO(data))
然后检查一下最初的说法是否正确（以防万一）：
index_by_date = df.groupby(['Year', 'Month'])['Index_ID']
assert index_by_date.count().eq(index_by_date.nunique()).all()
准备一些辅助数据，比如日期向前移动一个月、唯一的记录标识符（按年、月、索引ID）和一个组的起始点标记：
year, month = divmod(df['Month'], 12)
year += df['Year']
month += 1
index_id = pd.MultiIndex.from_arrays([year, month, df['Index_ID']])
root = df.index.min() - 1 # group starting point marker
现在将对应的上一个行号左连接到数据中：
df = (
df # join the indices of the previous records as group_id
.join(pd.Series(df.index, index_id, name='group_id'),
on=['Year', 'Month', 'Previous_Index_ID'])
.fillna({'group_id': root})
.astype({'group_id': int})
)
最后一步，遍历新列，用唯一的组标识符替换标记的起始点，并在链中的相应单元格中填入这些标识符：
for current, previous in df['group_id'].items():
df.loc[current, 'group_id'] = (current if previous == root
else df.at[previous, 'group_id'])
注意，在这个循环中，我们可以把 ...= current if ... 替换成其他提供唯一组标识符的方式，如果有需要的话，比如：
group_id = 0
for current, ...
df.loc[current, ...] = (group_id:=group_id+1) if ...
# or
from itertools import count
group_id = count(1)
for current, ...
df.loc[current, ...] = next(group_id) if ...
我们也可以把这个循环用 Numba 重写，以便更快运行，只要数据索引是从零开始的范围索引：
from numba import jit
@jit(nopython=True)
def set_group_ids(column, root_marker=-1):
group_id = 1
for i in range(column.shape[0]):
if column[i] == root_marker:
column[i] = group_id
group_id += 1
else:
column[i] = column[column[i]]
set_group_ids(df['group_id'].values, root)
可以尝试的代码：
import pandas as pd
from io import StringIO
data = '''Personalnumber,Category,Year,Month,Index_ID,Previous_Index_ID
1,100,2022,8,42100,
1,100,2022,8,101,
1,100,2022,9,9534,42100.0
1,100,2022,9,204,101.0
1,100,2022,10,4344,204.0
1,100,2022,11,2069,4344.0
1,9400,2023,9,4,
1,9400,2023,10,485,4.0
2,100,2022,1,214,102.0
2,100,2022,2,194231,214.0
3,200,2022,2,2111,
3,200,2022,3,1012,2111.0
3,200,2022,4,9876,1012.0
3,200,2022,5,8794,9876.0
3,200,2022,6,24142,8794.0
4,100,2022,4,42100,
4,200,2022,7,12,
4,200,2022,8,14,12.0
4,200,2022,9,485,14.0'''
df = pd.read_csv(StringIO(data))
index_by_date = df.groupby(['Year', 'Month'])['Index_ID']
assert index_by_date.count().eq(index_by_date.nunique()).all(), \
"Index_ID is not unique within (Year, Month)"
year, month = divmod(df['Month'], 12)
year += df['Year']
month += 1
index_id = pd.MultiIndex.from_arrays([year, month, df['Index_ID']])
root = df.index.min() - 1 # group starting point marker
df = (df.join(pd.Series(df.index, index_id, name='group_id'),
on=['Year','Month','Previous_Index_ID'])
.fillna({'group_id': root})
.astype({'group_id': int}))
try:
assert df.index.equals(pd.RangeIndex(len(df))), \
"Unable to use Numba due to inappropriate indexing"
from numba import jit
@jit(nopython=True)
def set_group_ids(column, root_marker=-1):
group_id = 1
for i in range(column.shape[0]):
if column[i] == root_marker:
column[i] = group_id
group_id += 1
else:
column[i] = column[column[i]]
set_group_ids(df['group_id'].values, root)
except (AssertionError, ModuleNotFoundError, ImportError) as e:
from warnings import warn
from itertools import count
warn(repr(e))
group_id = count(1)
for curr, prev in df['group_id'].items():
df.loc[curr, 'group_id'] = (next(group_id) if prev == root
else df.at[prev, 'group_id'])
附注：
这个算法只有在所有之前的索引在表中物理上都位于后续索引之前时才有效。否则，我们必须先填充所有的起始点（根），然后用适当的组标识符填充链。为此，我们可以使用两个参考序列，分别是下一个和上一个条目的索引（一个用来识别根，另一个用来填充链）：
date = pd.to_datetime(df[['Year', 'Month']].assign(Day=1))
previous = pd.Series(
df.index,
pd.MultiIndex.from_arrays([date + pd.offsets.MonthBegin(), df['Index_ID']]),
name='previous'
).reindex(
pd.MultiIndex.from_arrays([date, df['Previous_Index_ID']]),
fill_value=-1
).reset_index(drop=True)
following = (
previous[previous >= 0]
.rename_axis('following')
.reset_index()
.set_index('previous')
.reindex(previous.index, fill_value=-1)
.squeeze()
)
roots = previous < 0
grouper = pd.Series(0, df.index, name='GroupID')
grouper[roots] = range(1, 1+sum(roots))
for root, group_id in grouper[roots].items():
successor = following[root]
while successor >= 0:
grouper[successor] = group_id
successor = following[successor]
df = df.join(grouper)