Pandas分组后数据框pivot找不到zip索引及重复值报错如何解决

1. pivot找不到索引"zip"的原因 #

你执行groupby(['zip','companyname'], as_index=True)时指定了as_index=True，两个分组键zip、companyname会成为结果数据框op的多层行索引，而非普通数据列。pivot方法的index参数默认识别的是普通列的列名，自然找不到作为索引存在的zip，因此触发KeyError。
另外你最后一行pivot代码的values参数传入的"company"是不存在的字段，对应字段为索引名companyname。

2. ValueError: Index contains duplicate entries, cannot reshape报错原因与解决 #

2.1 报错原因 #

使用method='dense'计算组内排名时，同一邮政编码下如果多个主体的ontime%数值完全相同，会得到完全一致的排名值，导致(zip, rankwithinzip)的组合出现重复，而pivot要求行列组合必须唯一，因此触发重复条目报错。

2.2 解决方法 #

把组内排名的计算方法从dense替换为first，相同得分的主体会按照在数据里出现的先后顺序分配不同的排名，保证(zip, rankwithinzip)组合唯一，即可正常执行pivot。

修正后参考代码 #

import pandas as pd
df = pd.DataFrame({'zip': ["46062","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236"], 
                   'goodbadscore': ["good","good","good","good","good","bad","bad","good","good","good","bad","good","good","good","good","good","bad","good"],
                   'postlcode' : ["46062","11236","11236","11236","11236","46062","11236","46062","11236","11236","11236","11236","11236","11236","11236","11236","11236","11236"],
                   'companyname': ["A","B","C","C","C","A","A","A","B","B","A","A","B","A","A","B","A","A"]}
                   )

### 统计每个邮政编码下的供应商数量及好评数
op = df.groupby(['zip','companyname'], as_index=True).aggregate(
    countinzipcode=('companyname', 'count'),
    good=('goodbadscore', lambda s: s.eq('good').sum())
)

### 计算准时率=好评数/总计数*100
op['ontime%'] = op['good'] / op['countinzipcode'] * 100

### 计算全邮政编码排名和邮政编码内排名，组内排名改用first避免重复
op['rankingbyallzip'] = op['ontime%'].rank(ascending=False, method='dense').astype(int)
op['rankwithinzip'] = op.groupby("zip")["ontime%"].rank("first", ascending=False)

### 先把索引转为普通列再执行pivot，同时修正values参数
op_reset = op.reset_index()
res = op_reset.pivot(index = "zip", columns="rankwithinzip", values="companyname")

内容的提问来源于stack exchange，提问作者Oliver Wu