python - 计数是否在多个索引数据框中

Question

我有一个多索引数据框，我想知道为 3 个标准中的每一个支付了特定债务阈值的客户百分比:城市、卡和抵押品。
这是一个工作脚本:

import pandas as pd

d = {'City': ['Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo','Tokyo','Tokyo','Lisbon','Tokyo','Tokyo','Lisbon','Lisbon','Lisbon','Tokyo','Lisbon','Tokyo'], 
     'Card': ['Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card','Visa','Visa','Master Card','Master Card','Visa','Master Card','Visa','Visa','Master Card','Visa','Master Card'],
     'Colateral':['Yes','No','Yes','No','No','No','No','Yes','Yes','No','Yes','Yes','No','Yes','No','No','No','Yes','Yes','No','No','No'],
     'Client Number':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22],
     '% Debt Paid':[0.8,0.1,0.5,0.30,0,0.2,0.4,1,0.60,1,0.5,0.2,0,0.3,0,0,0.2,0,0.1,0.70,0.5,0.1]}

df = pd.DataFrame(data=d)

df1 = (df.set_index(['City','Card','Colateral'])
         .drop(['Client Number'],axis=1)
        .sum(level=[0,1,2]))

df2 = df1.reindex(pd.MultiIndex.from_product(df1.index.levels), fill_value=0)

这是结果:

为了克服这个问题，我尝试了以下方法但没有成功:

df1 = (df.set_index(['City','Card','Colateral'])
        .drop(['Client Number'],axis=1)
       [df.Total = 0].count(level=[0,1,2])/[df.Total].count()
       [df.Total > 0 & df.Total <=0.25 ].count(level=[0,1,2])/[df.Total].count()
       [df.Total > 0.25 & df.Total <=0.5 ].count(level=[0,1,2])/[df.Total])
       [df.Total > 0.5 & df.Total <=0.75 ].count(level=[0,1,2])/[df.Total]
       [df.Total > 0.75 & df.Total <1 ].count(level=[0,1,2])/[df.Total]
       [df.Total = 1].count(level=[0,1,2])/[df.Total]
       [df.Total > 1].count(level=[0,1,2])/[df.Total])

df2 = df1.reindex(pd.MultiIndex.from_product(df1.index.levels), fill_value=0)

这就是我希望针对所有标准实现的结果。
关于如何解决这个问题的任何想法？谢谢你。

Answer 1

你可以做 value_counts

newdf=df.groupby(['City','Card','Colateral'])['% Debt Paid'].\
           value_counts(bins=[-0.1,0,0.25,0.5,0.75,1,1.0001,999],normalize=True)