用不相等的（！=)

Question

我知道有很多帖子，但这并不能解决我的问题。你知道吗

我得到的数据框是：

df1 = [{"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator" : "k","Money" : 100},
    {"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator": "k","Money" : 200},
    {"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator" : "D", "Money" : 0}]
df1 = pd.DataFrame(df1)
df1

Account Name    Customer Number           Debit/Credit Indicator         Money
Sunarto      AFIMBN01000BCA17030001177       k                            100
Sunarto      AFIMBN01000BCA17030001177       k                            200
Sunarto      AFIMBN01000BCA17030001177       D                             0

Account Name              object
Customer Number           object
Debit/Credit Indicator    object
Money                      int64 (or let's say float64)

我想根据“钱”来计算频率

如果钱是0，就不算了。你知道吗

我试过df1["Money"].value_counts()不起作用

df1.loc[df1["Money"] != 0, "Per item"] = df1["Money"].value_counts()
df1

Account Name    Customer Number           Debit/Credit Indicator         Money   Per item
Sunarto      AFIMBN01000BCA17030001177       k                            100     1
Sunarto      AFIMBN01000BCA17030001177       k                            200    NaN
Sunarto      AFIMBN01000BCA17030001177       D                             0   NaN

但我的期望是

Account Name    Customer Number           Debit/Credit Indicator         Money   Per item
Sunarto      AFIMBN01000BCA17030001177       k                            100     1
Sunarto      AFIMBN01000BCA17030001177       k                            200    1
Sunarto      AFIMBN01000BCA17030001177       D                             0   0

所以我的期望是当我申请pivot时，我能得到一个“钱”值的项目

我的期望值

gdf = pd.pivot_table(df1, index = ["Account Name","Customer Number"],values = ["Money", "Per item"],aggfunc = np.sum)

gdf.head()

                                                Money              Per item
Account Name      Customer Number
Sunarto           AFIMBN01000BCA17030001177     300                2.0

Answer 1

您需要为每个条件分配1：

df1.loc[df1["Money"] != 0, "Per item"] = 1

或将布尔掩码转换为整数：

df1["Per item"] = (df1["Money"] != 0).astype(int)

另一种不带pivot_table的聚合解决方案：

gdf = (df1.groupby(["Account Name","Customer Number"])['Money']
          .agg([('Money','sum'), ('Per item', lambda x: x.ne(0).sum())]))
print (gdf)
                                        Money  Per item
Account Name Customer Number                           
Sunarto      AFIMBN01000BCA17030001177    300         2

编辑：

may i know why my code doesn't work?

问题是带有计数器值的^{}返回序列，但索引值是由原始的Series，这里是100, 200的值创建的。因此，索引不匹配并获取缺失的值。解决方法是使用^{}：

df1.loc[df1["Money"] != 0, "Per item"] = df1["Money"].map(df1["Money"].value_counts())
print (df1)
  Account Name            Customer Number Debit/Credit Indicator  Money  \
0      Sunarto  AFIMBN01000BCA17030001177                      k    100   
1      Sunarto  AFIMBN01000BCA17030001177                      k    200   
2      Sunarto  AFIMBN01000BCA17030001177                      D      0   

   Per item  
0       1.0  
1       1.0  
2       NaN  

但如果有多个复制值，则问题不是赋值1，而是计数器值并得到错误的输出，这里双200值错误地返回4值而不是2：

df1 = [{"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator" : "k","Money" : 200},
    {"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator": "k","Money" : 200},
    {"Customer Number": "AFIMBN01000BCA17030001177", "Account Name": "Sunarto","Debit/Credit Indicator" : "D", "Money" : 0}]
df1 = pd.DataFrame(df1)


df1.loc[df1["Money"] != 0, "Per item"] = df1["Money"].map(df1["Money"].value_counts())
print (df1)
  Account Name            Customer Number Debit/Credit Indicator  Money  \
0      Sunarto  AFIMBN01000BCA17030001177                      k    200   
1      Sunarto  AFIMBN01000BCA17030001177                      k    200   
2      Sunarto  AFIMBN01000BCA17030001177                      D      0   

   Per item  
0       2.0  
1       2.0  
2       NaN  

gdf = pd.pivot_table(df1, index = ["Account Name","Customer Number"],values = ["Money", "Per item"],aggfunc = np.sum)

print (gdf)
                                        Money  Per item
Account Name Customer Number                           
Sunarto      AFIMBN01000BCA17030001177    400       4.0