然后 Groupby 检查行匹配并计算该值的并发实例数

我有这个数据框

     car   color  years  max_years
0   audi   black      1          7
1   audi    blue      2          7
2   audi  purple      4          7
3   audi   black      6          7
4    bmw    blue      1          5
5    bmw    green     2          5
6    bmw    grey      5          5
7    bmw    blue     20          5
8   fiat   green      1          4
9   fiat   green      3          4
10  fiat   green      4          4
11  fiat   green     10          4

如果颜色条目为 1 年，我想计算该颜色在该汽车品牌组中出现的次数，直到该组的最大年数。

我想运行isin每个汽车品牌组的颜色条件，我认为我的问题是颜色列表不是grouby('car')因此评估适用于所有汽车

结果应该是：

0  audi       2
1   bmw       1
2  fiat       3

任何帮助，将不胜感激

import pandas as pd

car =  ['audi', 'audi', 'audi', 'audi', 'bmw', 'bmw', 'bmw', 'bmw', 'fiat', 'fiat', 'fiat', 'fiat']
color =  ['black', 'blue', 'purple', 'black', 'blue', 'green', 'grey', 'blue', 'green', 'green', 'green', 'green']
years = [1, 2, 4, 6, 1, 2, 5, 20, 1, 3, 4, 10, ]
max_years = [7, 7, 7, 7, 5, 5, 5, 5, 4, 4, 4, 4]

data = {'car': car, 'color': color, 'years': years, 'max_years': max_years}
df = pd.DataFrame(data=data)

colors =  df.loc[df.years == 1]['color'].values

colour_cars = df[(df.years <= df.max_years) & df['color'].isin(colors)].groupby(['car']).size().reset_index(name='colour_cars')

print(colour_cars)

想法就是使用Series.map http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.map.html by Series使用过滤后的 DataFrame 创建years == 1并按列进行比较color:

colors =  df.loc[df.years == 1].set_index('car')['color']

df1 = (df[(df.years <= df.max_years) & df['car'].map(colors).eq(df['color'])]
         .groupby('car')
         .size()
         .reset_index(name='colour_cars'))
print(df1)

    car  colour_cars
0  audi            2
1   bmw            1
2  fiat            3

或者你可以使用mask转换为整数Series.view http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.view.html，那么就需要计数Trues 值由sum并通过Series df['car'] to groupby:

colors =  df.loc[df.years == 1].set_index('car')['color']

df1 = (((df.years <= df.max_years) & df['car'].map(colors).eq(df['color']))
         .view('i1')
         .groupby(df['car'])
         .sum()
         .reset_index(name='colour_cars'))
print(df1)

    car  colour_cars
0  audi            2
1   bmw            1
2  fiat            3

不同的想法是测试每组的第一个颜色GroupBy.transform http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.transform.html with first（如果每组总是第一年，解决方案有效1):

df2 = (df[(df.years <= df.max_years)]
           .groupby('car')['color']
           .transform('first').eq(df['color'])
           .view('i1')
           .groupby(df['car'])
           .sum()
           .reset_index(name='colour_cars'))

print(df2)

    car  colour_cars
0  audi            2
1   bmw            1
2  fiat            3