尝试通过对其他列应用条件来过滤数据框中的列

Question

我的 csv 文件中有 3 列： account_id 、 game_variant 、 no_of_games .... 表看起来像这样account_id    game_variant   no_of_games130               a             2145               c             1130               b             4130               c             1142               a             3140               c             2145               b             5所以，我想提取变体 a,b,c,a∩b,b∩c,a∩c,a∩b∩c 中玩的游戏数量我能够通过与 game_variant 分组并对 no_of_games 进行求和来单独提取在 a、b、c 中玩的游戏，但无法逻辑地放入交叉部分。请帮我解决这个问题data_agg = df.groupby(['game_variant']).agg({'no_of_games':[np.sum]})提前致谢

Answer 1

这里的解决方案将根据每个玩家的级别返回交集。这还使用了defaultdict，因为这对于这种情况非常方便。我将解释内联代码

from itertools import combinations

import pandas

from collections import defaultdict

from pprint import pprint  # only needed for pretty printing of dictionary

df = pandas.read_csv('df.csv', sep='\s+')  # assuming the data frame is in a file df.csv

# group by account_id to get subframes which only refer to one account.

data_agg2 = df.groupby(['account_id'])

# a defaultdict is a dictionary, where when no key is present, the function defined

# is used to create the element. This eliminates the check, if a key is

# already present or to set all combinations in advance.

games_played_2 = defaultdict(int)

# iterate over all accounts

for el in data_agg2.groups:

    # extract the sub-dataframe from the gouped function

    tmp = data_agg2.get_group(el)

    # print(tmp)  # you can uncomment this to see each account

    

    # This is in principle the same loop as suggested before. However, as not every

    # player has played all variants, one only has to create the number of combinations

    # necessary for that player

    for i in range(len(tmp.loc[:, 'no_of_games'])):

        # As now the game_variant is a column and not the index, the first part of zip

        # is slightly adapted. This loops over all combinations of variants for the

        # current account.

        for comb, combsum in zip(combinations(tmp.loc[:, 'game_variant'], i+1), combinations(tmp.loc[:, 'no_of_games'].values, i+1)):

            # Here, each variant combination gets a unique key. Comb is sorted, as the

            # variants might be not in alphabetic order. The number of games played for

            # each variant for that player are added to the value of all players before.

            games_played_2['_'.join(sorted(comb))] += sum(combsum)

pprint (games_played_2)

# returns

>> defaultdict(<class 'int'>,

            {'a': 5,

             'a_b': 6,

             'a_b_c': 7,

             'a_c': 3,

             'b': 9,

             'b_c': 11,

             'c': 4})

由于您已经提取了它们的变体所玩的游戏数量，因此您可以简单地将它们相加。如果您想自动执行此操作，则可以itertools.combinations在循环中使用它，该循环会迭代所有可能的组合长度：

from itertools import combinations

import pandas

import numpy as np

from pprint import pprint  # only needed for pretty printing of dictionary

df = pandas.read_csv('df.csv', sep='\s+')  # assuming the data frame is in a file df.csv

data_agg = df.groupby(['game_variant']).agg({'no_of_games':[np.sum]})

games_played = {}

for i in range(len(data_agg.loc[:, 'no_of_games'])):

    for comb, combsum in zip(combinations(data_agg.index, i+1), combinations(data_agg.loc[:, 'no_of_games'].values, i+1)):

        games_played['_'.join(comb)] = sum(combsum)

pprint(games_played)

返回：

>> {'a': array([5], dtype=int64),

>>  'a_b': array([14], dtype=int64),

>>  'a_b_c': array([18], dtype=int64),

>>  'a_c': array([9], dtype=int64),

>>  'b': array([9], dtype=int64),

>>  'b_c': array([13], dtype=int64),

>>  'c': array([4], dtype=int64)}

'combinations(sequence, number)'number返回中所有元素组合的迭代器sequence。因此，要获得所有可能的组合，您必须迭代所有numbersfrom1到len(sequence。这就是第一个 for 循环的作用。

下一个for循环由两个迭代器组成：一个迭代器覆盖聚合数据的索引 ( combinations(data_agg.index, i+1))，一个迭代器覆盖每个变体中实际玩的游戏数量 ( combinations(data_agg.loc[:, 'no_of_games'].values, i+1))。因此comb应该始终是变体列表，并汇总每个变体所玩游戏数量的列表。这里请注意，要获取所有值，您必须使用.loc[:, 'no_games']，而不是.loc['no_games']，因为后者搜索名为 的索引'no_games'，而它是列名。

然后，我将字典的键设置为变体列表的组合字符串，并将值设置为玩过的游戏数量的元素之和。