python - 将计算应用于 Pandas DataFrame 中的过滤值
问题描述
我是熊猫新手。
考虑一下我的数据框：
df
Search Impressions Clicks Transactions ContainsBest ContainsFree Country
Best phone 10 5 1 True False UK
Best free phone 15 4 2 True True UK
free phone 20 3 4 False True UK
good phone 13 1 5 False False US
just a free phone 12 3 4 False True US
我有列ContainsBest和ContainsFree. 我想对所有内容求和Impressions，在哪里，Clicks然后我想求和，在哪里是True，并且对 column 中的每个唯一值执行相同的操作。所以新的 DataFrame 看起来像这样：TransactionsContainsBestTrueImpressionsClicksTransactionsContainsFreeCountry
输出_df
Country Impressions Clicks Transactions
UK 45 12 7
ContainsBest 25 9 3
ContainsFree 35 7 6
US 25 4 9
ContainsBest 0 0 0
ContainsFree 12 3 4
为此，我知道我需要使用以下内容：
uk_toal_impressions = df['Impressions'].sum().where(df['Country']=='UK')
uk_best_impressions = df['Impressions'].sum().where(df['Country']=='UK' & df['ContainsBest'])
uk_free_impressions = df['Impressions'].sum().where(df['Country']=='UK' & df['ContainsFree'])
然后我会对Clicksand应用相同的逻辑，并Transactions为Country US.
我要实现的第二件事是添加列TopCategoriesper Countryand Impressions, Clicksand Transactions，这样我的final_output_df看起来像这样：
final_output_df
Country Impressions Clicks Transactions TopCategoriesForImpressions TopCategoriesForClicks TopCategoriesForTransactions
UK 45 12 7 ContainsFree ContainsBest ContainsFree
ContainsBest 25 9 3 ContainsBest ContainsFree ContainsBest
ContainsFree 35 7 6
US 25 4 9 ContainsFree ContainsFree ContainsFree
ContainsBest 0 0 0
ContainsFree 12 3 4
列TopCategoriesForxx逻辑是一种简单的排序ContainsBest和ContainsFree列下的Country行。所以TopCategoriesForImpressionsforUK国家是
- 包含免费
- 包含最佳
而TopCategoriesForClicksforUK国家是：
- 包含最佳
- 包含免费
我知道我需要使用这样的东西：
TopCategoriesForImpressions = output_df['Impressions'].sort_values(by='Impressions', ascending=False).where(output_df['Country']=='UK')
我只是觉得很难把所有东西都放在我最后的样子final_output_df。另外，我假设我不需要output_df被创建，只是想添加它以便更好地理解我的步骤是什么来实现final_output_df.
所以我的问题是：
- 如何应用基于一个和多个条件的计算？见行
ContainsBest和ContainsFree - 如何根据条件对列值进行排序？见专栏
TopCategoriesForImpressions - 实际上，我有70个国家和20个栏目
Containsxxx，有没有办法在不添加70个国家和20个栏目条件的情况下实现这一点Containsxxx？
非常感谢您的建议。
解决方案
解决方案的第一部分应该是：
#removed unnecessary column Search and added ContainAll column filled Trues
df1 = df.drop('Search', 1).assign(ContainAll = True)
#columns for tests
cols1 = ['Impressions','Clicks','Transactions']
cols2 = ['ContainsBest','ContainsFree','ContainAll']
print (df1[cols2].dtypes)
ContainsBest bool
ContainsFree bool
ContainAll bool
dtype: object
print (df1[cols1].dtypes)
Impressions int64
Clicks int64
Transactions int64
dtype: object
print (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask'))
Country Impressions Clicks Transactions Type mask
0 UK 10 5 1 ContainsBest True
1 UK 15 4 2 ContainsBest True
2 UK 20 3 4 ContainsBest False
3 US 13 1 5 ContainsBest False
4 US 12 3 4 ContainsBest False
5 UK 10 5 1 ContainsFree False
6 UK 15 4 2 ContainsFree True
7 UK 20 3 4 ContainsFree True
8 US 13 1 5 ContainsFree False
9 US 12 3 4 ContainsFree True
10 UK 10 5 1 ContainAll True
11 UK 15 4 2 ContainAll True
12 UK 20 3 4 ContainAll True
13 US 13 1 5 ContainAll True
14 US 12 3 4 ContainAll True
print (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask').query('mask'))
Country Impressions Clicks Transactions Type mask
0 UK 10 5 1 ContainsBest True
1 UK 15 4 2 ContainsBest True
6 UK 15 4 2 ContainsFree True
7 UK 20 3 4 ContainsFree True
9 US 12 3 4 ContainsFree True
10 UK 10 5 1 ContainAll True
11 UK 15 4 2 ContainAll True
12 UK 20 3 4 ContainAll True
13 US 13 1 5 ContainAll True
14 US 12 3 4 ContainAll True
#all possible combinations of Country and boolean columns
mux = pd.MultiIndex.from_product([df['Country'].unique(), cols2],
names=['Country','Type'])
#reshape by melt for all boolean column to one mask column
#filter Trues by loc and aggregate sum
#add 0 rows by reindex
df1 = (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask')
.query('mask')
.drop('mask', axis=1)
.groupby(['Country','Type'])
.sum()
.reindex(mux, fill_value=0)
.reset_index())
print (df1)
Country Type Impressions Clicks Transactions
0 UK ContainsBest 25 9 3
1 UK ContainsFree 35 7 6
2 UK ContainAll 45 12 7
3 US ContainsBest 0 0 0
4 US ContainsFree 12 3 4
5 US ContainAll 25 4 9
第二个是可能的过滤行检查排序，每组numpy.argsort按降序排列：
def f(x):
i = x.index.to_numpy()
a = i[(-x.to_numpy()).argsort(axis=0)]
return pd.DataFrame(a, columns=x.columns)
df2 = (df1[df1['Type'].isin(['ContainsBest','ContainsFree']) &
~df1[cols1].eq(0).all(1)]
.set_index('Type')
.groupby('Country')[cols1]
.apply(f)
.add_prefix('TopCategoriesFor')
.rename_axis(['Country','Type'])
.rename({0:'ContainsBest', 1:'ContainsFree'})
)
print (df2)
TopCategoriesForImpressions TopCategoriesForClicks \
Country Type
UK ContainsBest ContainsFree ContainsBest
ContainsFree ContainsBest ContainsFree
US ContainsBest ContainsFree ContainsFree
TopCategoriesForTransactions
Country Type
UK ContainsBest ContainsFree
ContainsFree ContainsBest
US ContainsBest ContainsFree
df3 = df1.join(df2, on=['Country','Type'])
print (df3)
Country Type Impressions Clicks Transactions \
0 UK ContainsBest 25 9 3
1 UK ContainsFree 35 7 6
2 UK ContainAll 45 12 7
3 US ContainsBest 0 0 0
4 US ContainsFree 12 3 4
5 US ContainAll 25 4 9
TopCategoriesForImpressions TopCategoriesForClicks \
0 ContainsFree ContainsBest
1 ContainsBest ContainsFree
2 NaN NaN
3 ContainsFree ContainsFree
4 NaN NaN
5 NaN NaN
TopCategoriesForTransactions
0 ContainsFree
1 ContainsBest
2 NaN
3 ContainsFree
4 NaN
5 NaN
推荐阅读
- javascript - 如何使用纯 js 和 FileReader API 创建文件上传
- c++ - this指针可以是多态的吗？
- java - 在从 TextField (FXML) 扩展的自定义类中分配布尔值时出错
- python - 在 vanilla LSTM 中表示时空图像数据的最佳方法是什么
- linq - 如何使用 Linq 连接两个表并计算一列匹配记录的行数
- android - Android EditText 使用标题后的整个活动高度
- python - pymongo中带有forEach函数的Mongo聚合查询不起作用
- php - 带有 php curl 的 2ba Web API 发布请求
- python - 两个简化条件合二为一
- sorting - 对部分排序的数组进行排序