将数据帧转换为基于值列表的摘要表

Question

我试图从一个数据帧创建一个摘要表，如下例所示。列具有一组唯一值的列表。你知道吗

tdf = pd.DataFrame({"A": ["ind1", "ind2", "ind1", "ind3", "ind3", "ind1", "ind1"],
                   "B": ["ind3", "ind1", "ind3", "ind1", "ind1","ind3", "ind2"],
                   "C": ["ind1","ind1","ind2","ind2","ind3","ind3","ind3"],
                   "D": ["ind3","ind1","ind2","ind3","ind2","ind1","ind3"],
                   "E": ["ind1","ind3","ind1","ind1","ind2","ind2","ind2"]})

然后，我需要创建一个新的类似于表的对象，该对象有一个与列相对应的头，以及3个具有设置索引值的频率计数的行。你知道吗

setvalues = ['ind1','ind2','ind3']

result = pd.DataFrame({"A": [4,1,2],
                   "B": [3,1,3],
                   "C": [2,2,3],
                   "D": [2,2,3],
                   "E": [3,3,1]})

我尝试了透视表，但它没有返回所需的格式。在Excel中，我可以设置索引值并对列进行简单的COUNTIF，但是我很难用Python实现它。你知道吗

Answer 1

import pandas as pd

tdf = pd.DataFrame({"A": ["ind1", "ind2", "ind1", "ind3", "ind3", "ind1", "ind1"],
                   "B": ["ind3", "ind1", "ind3", "ind1", "ind1","ind3", "ind2"],
                   "C": ["ind1","ind1","ind2","ind2","ind3","ind3","ind3"],
                   "D": ["ind3","ind1","ind2","ind3","ind2","ind1","ind3"],
                   "E": ["ind1","ind3","ind1","ind1","ind2","ind2","ind2"]})

full = tdf.apply(pd.value_counts).fillna(0);
print(full)

Answer 2

这里我们可以使用value_counts

tdf.apply(pd.Series.value_counts)
      A  B  C  D  E
ind1  4  3  2  2  3
ind2  1  1  2  2  3
ind3  2  3  3  3  1