如何在熊猫的群体中找到共同的价值观?

Question

我的"数据帧"包含两列，第一列是SKU编号，第二列是每个SKU编号的部件号。某些 SKU 共享相同的部件号，如何找到这些共享部件号的 SKU？

import pandas as pd 
table_teste = pd.read_csv("table.csv")
print(table_teste)[see in the picture attached here the screenshot of the input vales][1]
Output:
SKU    Part Number
0         4679343         126420
1         4679343         489136
2         4679343         490202
3         4679343         490282
4         4679343         491971
5         4679343         492963
6         4679343         626681
7         4679343         627996
8         4679343         628361
9         4679343         628379
10        4679343         628379
11        4679343         628408
12        4679343         628531
13        4679343        1105601
14        4679343        1140073
15        4679343        2169104
16        4679343        2169104
17        4679343        2169142
18        4679343        2185762
19        4679343        2194712
20        4679343        2195058
21        4679343        2256086
22        4679343        2315522
23        4679343        2315522
24        4679343        2319835
25        4679343        8314101
26        4679343        8314102
27        4679343        8314229
28        4679343        8314231
29        4679343        8314232
...           ...            ...
73953  WRO80CKDWA      W11234774
73954  WRO80CKDWA      W11239503
73955  WRO80CKDWA      W11240332
73956  WRO80CKDWA      W11240358
73957  WRO80CKDWA      W11240361
73958  WRO80CKDWA      W11240362
73959  WRO80CKDWA      W11240363
73960  WRO80CKDWA      W11282632
73961  WRO80CKDWA      W11282632
73962  WRO80CKDWA      W11293453
73963  WRO80CKDWA      W11294381
73964  WRO80CKDWA      W11294503
73965  WRO80CKDWA      W11298984
73966  WRO80CKDWA      W11308860
73967  WRO80CKDWA      W11308879
73968  WRO80CKDWA      W11314128
73969  WRO80CKDWA      W11317776
73970  WRO80CKDWA      W11323281
73971  WRO80CKDWA      W11323282
73972  WRO80CKDWA      W11323283
73973  WRO80CKDWA      W11323284
73974  WRO80CKDWA      W11366199
73975  WRO80CKDWA      W11366205
73976  WRO80CKDWA      W11366209
73977  WRO80CKDWA      W11366214
73978  WRO80CKDWA      W11366215
73979  WRO80CKDWA      W11370412
73980  WRO80CKDWA      W11370419
73981  WRO80CKDWA      W11370494
73982  WRO80CKDWA  ZCOMP_FREIGHT

现在，我需要生成一个矩阵，该矩阵在行中具有SKU编号，在列中具有相同的SKU编号，并且在矩阵中计算SKU编号1和SKU编号2的组合共享的相同部件号的数量。SKU 编号 2 与 SKU 编号 3 相同，依此类推。总共有 182 个 SKU 编号。

谢谢

Answer 1

查找超过1 个 SKU 的所有部件号：

partNumber_w_dupSKU = data %>%
group_by(partNumber) %>%
summarize(n_SKU = n_distinct(SKU)) %>%
ungroup() %>%
filter(n_SKU > 1)

查找与这些部件号关联的所有 SKU：

data %>%
arrange(SKU) %>%
filter(partNumber %in% partNumber_w_dupSKU$partNumber)

Answer 2

您可以在部件号上使用 groupby((，这将相应地对数据帧进行分组 如果对 SKU 编号进行分组，则它将显示一个数据帧，其中包含共享通用部件号的 SKU 编号 反之亦然

Answer 3

您可以尝试使用分组，将组转换为列表并重置索引。

# dictionary of sku number as key and value as part number
# I'm assuming this is how the df might look like
d = {1: 2, 2: 3, 3: 2, 4: 2, 5: 3, 6: 2, 7: 3}
# making a dataframe out of the dict to resemble df in que
df = pd.DataFrame(d.items(), columns=['SKU Number', 'Part Number'])
df
Output: 
SKU Number  Part Number
0           1            2
1           2            3
2           3            2
3           4            2
4           5            3
5           6            2
6           7            3

# first groupby part numbers
g = df.groupby('Part Number')
# convert groups to list of two-SKU combinations and then reset index to create a new data
x = g['SKU Number'].apply(itertools.combinations(x, 2))).reset_index(name='SKU numbers')
x
Output:
Part Number                                       SKU numbers
0            2  [(1, 3), (1, 4), (1, 6), (3, 4), (3, 6), (4, 6)]
1            3                          [(2, 5), (2, 7), (5, 7)]

^ 现在，我们为每个部件号提供了所有两个成员的SKU组合。让我们分解 SKU 编号列中的列表。

x = x.explode('SKU numbers')
x
out:
Part Number SKU numbers
0            2      (1, 3)
0            2      (1, 4)
0            2      (1, 6)
0            2      (3, 4)
0            2      (3, 6)
0            2      (4, 6)
1            3      (2, 5)
1            3      (2, 7)
1            3      (5, 7)

现在我们需要对 SKU 编号对进行分组并计算与之关联的部件号

x = x.groupby('SKU numbers').count().reset_index()
x
out:
SKU numbers  Part Number
0      (1, 3)            1
1      (1, 4)            1
2      (1, 6)            1
3      (2, 5)            1
4      (2, 7)            1
5      (3, 4)            1
6      (3, 6)            1
7      (4, 6)            1
8      (5, 7)            1

^ 现在我们有每个SKU对的部件号计数。让我们构造矩阵。

import numpy as np

indexes = x['SKU numbers'].values
part_number_counts = x['Part Number'].values
# in my small case, we have 7 unique SKUs
unique_SKUs = 7
# creating a zero matrix so that we can populate part num counts
# for each SKU pair
a = np.zeros((unique_SKUs, unique_SKUs))
a
out:
array([[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.]])

# split [(x1, y1), (x2, y2) ...] to rows  -> [x1, x2 ...]
#                                 columns -> [y1, y2 ....]
rows, columns = map(np.array , zip(*indexes))
# rows-1, columns-1 are done to make index 0-based
a[rows-1, columns-1] = part_number_counts
a
out:
array([[0., 0., 1., 1., 0., 1., 0.],
[0., 0., 0., 0., 1., 0., 1.],
[0., 0., 0., 1., 0., 1., 0.],
[0., 0., 0., 0., 0., 1., 0.],
[0., 0., 0., 0., 0., 0., 1.],
[0., 0., 0., 0., 0., 0., 0.],
[0., 0., 0., 0., 0., 0., 0.]])

对于最后一部分，我使用我的索引(SKU 对(，将它们转换为从 0 开始的索引，并将其相应的part_number_counts更新为零矩阵以获得结果矩阵。

生成的矩阵将具有 (unique_SKU_numbers， unique_SKU_numbers( 的形状，值 i，j 对应于part_number_counts