Pandas高效地计算列中的唯一值，然后找到该列的最大值

Question

数据帧（df）看起来像：

    Date                Caller  Called
0   2011-01-01 00:00:00 Sarah   Claire
1   2011-01-01 00:00:00 Sarah   Ryan
2   2011-01-01 00:00:00 Sarah   Alex
3   2011-01-02 00:00:00 Sarah   Max
4   2011-01-02 00:00:00 Sarah   Phoebe


number_date =  df.groupby(['Caller',pd.DatetimeIndex(df['Date']).normalize()])
number_date['Called'].unique().apply(lambda x: len(x))

这符合我的期望。她1号打了3个电话，2号打了2个电话：

^{pr2}$

如何将其修改为：

^{3}$

Answer 1

您可以尝试^{}和{a2}：

print df
        Date  Caller  Called
0 2011-01-01   Sarah  Claire
1 2011-01-01   Sarah    Ryan
2 2011-01-01  Sarah1    Ryan
3 2011-01-01  Sarah1   Ryan1
4 2011-01-01  Sarah1    Ryan
5 2011-01-01   Sarah    Alex
6 2011-01-02   Sarah     Max

number_date=df.groupby(['Caller',pd.DatetimeIndex(df['Date']).normalize()])['Called'].nunique()
              .groupby(level=0).nlargest(1).reset_index(level=1, drop=True).reset_index()

number_date.columns = ['Caller','Date', 'MaxCallsOneDay']
print number_date
   Caller       Date  MaxCallsOneDay
0   Sarah 2011-01-01               3
1  Sarah1 2011-01-01               2

如果不需要列Date，请使用^{}：

^{pr2}$

计时-len(df) = 7k：

In [531]: %timeit df.groupby(['Caller',pd.DatetimeIndex(df['Date']).normalize()])['Called'].unique().apply(lambda x: len(x)).groupby(level=0).nlargest(1).reset_index(level=1, drop=True).reset_index()
The slowest run took 4.80 times longer than the fastest. This could mean that an intermediate result is being cached 
10 loops, best of 3: 8.58 ms per loop

In [532]: %timeit df.groupby(['Caller',pd.DatetimeIndex(df['Date']).normalize()])['Called'].nunique().groupby(level=0).nlargest(1).reset_index(level=1, drop=True).reset_index()
100 loops, best of 3: 7.07 ms per loop

In [547]: %timeit df.groupby(['Caller',pd.DatetimeIndex(df['Date']).normalize()])['Called'].nunique().groupby(level=0).max().reset_index(name='MaxCallsOneDay')
100 loops, best of 3: 3.52 ms per loop

Answer 2

在现有的数据帧上再次分组，使用level=[0]指定索引中用于分组的第一级（即调用者）：

# existing dataframe
df = pd.DataFrame(
    {'Called': ['Claire', 'Ryan', 'Ryan', 'Ryan1', 'Ryan', 'Alex', 'Max'],
     'Caller': ['Sarah', 'Sarah', 'John', 'John', 'John', 'Sarah', 'Sarah'],
     'Date': ['2011-01-01'] * 6 + ['2011-01-02']})

# Group on dataframe.
gb = df.groupby(['Caller', pd.DatetimeIndex(df['Date']).normalize()]).Called.count()

>>> gb.groupby(level=[0]).max()
Caller
John     3
Sarah    3
Name: Called, dtype: int64