Python pandas idxmax for multiple indexes in a dataframe
Question: I have a series that looks like this:

    
    
                delivery
    2007-04-26  706           23
    2007-04-27  705           10
                706         1089
                708           83
                710           13
                712           51
                802            4
                806            1
                812            3
    2007-04-29  706           39
                708            4
                712            1
    2007-04-30  705            3
                706         1016
                707            2
    ...
    2014-11-04  1412          53
                1501           1
                1502           1
                1512           1
    2014-11-05  1411          47
                1412        1334
                1501          40
                1502         433
                1504         126
                1506         100
                1508           7
                1510           6
                1512          51
                1604           1
                1612           5
    Length: 26255, dtype: int64
    

where the query is: `df.groupby([df.index.date, 'delivery']).size()`

For each day, I need to pull out the delivery number which has the most
volume. I feel like it would be something like:

    
    
    df.groupby([df.index.date, 'delivery']).size().idxmax(axis=1)
    

However, this just returns me the idxmax for the entire dataframe; instead, I
need the second-level idmax (not the date but rather the delivery number) for
each day, not the entire dataframe (ie. it returns a vector).

Any ideas on how to accomplish this?


Answer: Suppose you have this series:

    
    
                delivery
    2001-01-02  0           2
                1           3
                6           2
                7           2
                9           3
    2001-01-03  3           2
                6           1
                7           1
                8           3
                9           1
    dtype: int64
    

If you want _one delivery_ per date with the maximum value, you could use
`idxmax`:

    
    
    dates = series.index.get_level_values(0)
    series.loc[series.groupby(dates).idxmax()]
    

yields

    
    
                delivery
    2001-01-02  1           3
    2001-01-03  8           3
    dtype: int64
    

If you want _all deliveries_ per date with the maximum value, [use `transform`
to generate a boolean mask](http://stackoverflow.com/a/27868335/190597):

    
    
    mask = series.groupby(dates).transform(lambda x: x==x.max()).astype('bool')
    series.loc[mask]
    

yields

    
    
                delivery
    2001-01-02  1           3
                9           3
    2001-01-03  8           3
    dtype: int64
    

* * *

This is the code I used to generate `series`:

    
    
    import pandas as pd
    import numpy as np
    
    np.random.seed(1)
    N = 20
    rng = pd.date_range('2001-01-02', periods=N//2, freq='4H')
    rng = np.random.choice(rng, N, replace=True)
    rng.sort()
    df = pd.DataFrame(np.random.randint(10, size=(N,)), columns=['delivery'], index=rng)
    series = df.groupby([df.index.date, 'delivery']).size()
    

