从Pandas数据帧创建一个矩阵来显示连通性-2

Question

这是从Pandas数据帧创建矩阵以显示连通性的后续问题。区别在于矩阵。

我在熊猫数据帧中有这种格式的数据：

Customer_ID  Location_ID
Alpha             A
Alpha             B
Alpha             C
Beta              A
Beta              B
Beta              D

我想研究客户的流动模式。我的目标是确定客户最常光顾的地点集群。我认为以下矩阵可以提供这样的信息：

   A  B  C  D
A  0  0  0  0
B  2  0  0  0
C  1  1  0  0
D  1  1  0  0

在Python中如何做到这一点？

我的数据集相当大（数十万客户和大约一百个位置）。

Answer 1

为了完整起见，这里是我之前答案的修改版本。基本上，在更新矩阵时添加一个条件：if edge > node:

import pandas as pd
#I'm assuming you can get your data into a pandas data frame:
data = {'Customer_ID':[1,1,1,2,2,2],'Location':['A','B','C','A','B','D']}
df = pd.DataFrame(data)
#Initialize an empty matrix
matrix_size = len(df.groupby('Location'))
matrix = [[0 for col in range(matrix_size)] for row in range(matrix_size)]
#To make life easier, I made a map to go from locations 
#to row/col positions in the matrix
location_set = list(set(df['Location'].tolist()))
location_set.sort()
location_map = dict(zip(location_set,range(len(location_set))))
#Group data by customer, and create an adjacency list (dyct) for each
#Update the matrix accordingly
for name,group in df.groupby('Customer_ID'):
    locations = set(group['Location'].tolist())
    dyct = {}
    for i in locations:
        dyct[i] = list(locations.difference(i))
    #Loop through the adjacency list and update matrix
    for node, edges in dyct.items(): 
        for edge in edges:
            #Add this condition to create bottom half of the symmetric matrix
            if edge > node:
                matrix[location_map[edge]][location_map[node]] +=1

Answer 2

更改为此行中的2个字符：

overlaps += [(l2, l1, 0) for l1, l2, _ in overlaps]

来自

overlaps += [(l2, l1, c) for l1, l2, c in overlaps]

第一个版本中这行的目的是填充对称值。如果您想要一个较低的对角线矩阵，只需在相应的键中填充零即可。

原始代码：

import pandas as pd
from collections import Counter
from itertools import product
df = pd.DataFrame({
    'Customer_ID': ['Alpha', 'Alpha', 'Alpha', 'Beta', 'Beta', 'Beta'],
    'Location_ID': ['A', 'B', 'C', 'A', 'B', 'D'],
    })

ctrs = {location: Counter(gp.Customer_ID) for location, gp in df.groupby('Location_ID')}

# In [7]: q.ctrs
# Out[7]:
# {'A': Counter({'Alpha': 1, 'Beta': 1}),
#  'B': Counter({'Alpha': 1, 'Beta': 1}),
#  'C': Counter({'Alpha': 1})}

ctrs = list(ctrs.items())
overlaps = [(loc1, loc2, sum(min(ctr1[k], ctr2[k]) for k in ctr1))
    for i, (loc1, ctr1) in enumerate(ctrs, start=1)
    for (loc2, ctr2) in ctrs[i:] if loc1 != loc2]
overlaps += [(l2, l1, 0) for l1, l2, _ in overlaps]

df2 = pd.DataFrame(overlaps, columns=['Loc1', 'Loc2', 'Count'])
df2 = df2.set_index(['Loc1', 'Loc2'])
df2 = df2.unstack().fillna(0).astype(int)

#      Count         
# Loc2     A  B  C  D
# Loc1               
# A        0  0  0  0
# B        2  0  0  0
# C        1  1  0  0
# D        1  1  0  0