Feature request: aggregation without MultiIndex columns

[PanWu]

Currently the group-by-aggregation in pandas will create MultiIndex columns if there are multiple operation on the same column. However, this introduces some friction to reset the column names for fast filter and join. (If all operations could be chained together, analytics would be smoother)

df = pd.DataFrame([
        ['A', 1],
        ['A', 2],
        ['A', 3],
        ['B', 4],
        ['B', 5],
        ['B', 6]
    ], columns=['Key', 'Value'])

df1 = df.groupby('Key') \
    .agg({
            'Value': {'V1': 'sum', 'V2': 'count'}
        })

df1.columns = df1.columns.droplevel() # This line introduce friction
df1.query('V1 > V2')

Expected Output

It would be great if there is a simple alias function for columns (like the pyspark's implementation), such as

# Just one approach, there may be others more appropriate
df.groupby('Key') \
    .agg(
            pd.Series.sum('Value').alias('V1'),
            pd.Series.count('Value').alias('V2')
        ) \
    .query('V1 > V2')

[jreback]

In [14]: df.groupby('Key').Value.agg({'V1': 'sum', 'V2': 'count'}).query('V1 > V2')
Out[14]: 
     V2  V1
Key        
A     3   6
B     3  15

You simply need to specify an aggregation directly on the Series.

PySpark in general is NOT an inspiration as things to be honest are much clunkier.

[PanWu]

Thanks @jreback for the quick response. My previous example is over simplified, what about the following example

df = pd.DataFrame([
        ['A', 1, 85],
        ['A', 2, 45],
        ['A', 3, 23],
        ['B', 4, 76],
        ['B', 5, 43],
        ['B', 6, 56]
    ], columns=['Key', 'Value', 'Age'])

df1 = df.groupby('Key') \
    .agg({
            'Value': {'V1': 'sum', 'V2': 'count'},
            'Age': {'AvgAge': 'mean', 'StdAge': 'std'}
        })

Is there a way to handle multiple columns aggregation with multiple aggregation method in a convenient way?

[jreback]

http://pandas.pydata.org/pandas-docs/stable/groupby.html#aggregation

[wesm]

FWIW I would also like to see a new groupby API entry point (not requiring keyword args) that does not yield a row index.

[PanWu]

@jreback thanks, eventually figure out the schema is:

df1 = df.groupby('Key', as_index=False) \
    ['Value', 'Age'] \
    .agg({
            'Value': {'V1': 'sum', 'V2': 'count'},
            'Age': {'AvgAge': 'mean', 'StdAge': 'std'}
        })

@wesm that would be great! +1 on that