Applying more than one function to a pandas dataframe

Question

I am looking for a way to integrate more than one apply function from my raw data. Here is some simplified code.

import pandas as pd 

df = pd.DataFrame({'name':["alice","bob","charlene","alice","bob","charlene","alice","bob","charlene","edna" ],
                   'date':["2020-01-01","2020-01-01","2020-01-01","2020-01-01","2020-01-01","2020-01-01","2020-01-02","2020-01-01","2020-01-02","2020-01-01"],
                   'contribution': [5,5,10,20,30,1,5,5,10,100],
                   'payment-type': ["cash","transfer","cash","transfer","cash","transfer","cash","transfer","cash","transfer",]})
df['date'] = pd.to_datetime(df['date'])

def myfunction(input):
    output = input["name"].value_counts()
    output.index.set_names(['name_x'], inplace=True)
    return output

daily_count = df.groupby(pd.Grouper(key='date', freq='1D')).apply(myfunction)

print(daily_count.reset_index())

output:

        date    name_x  name
0 2020-01-01       bob     3
1 2020-01-01  charlene     2
2 2020-01-01     alice     2
3 2020-01-01      edna     1
4 2020-01-02  charlene     1
5 2020-01-02     alice     1

I would like to integrate the output from this code into the previous result.

def myfunction(input):
    output = input["contribution"].sum()
    # output.index.set_names(['name_x'], inplace=True)
    return output
    
daily_count = df.groupby([pd.Grouper(key='date', freq='1D'), "name"]).apply(myfunction)

Which would give me something like:

        date      name   num_contrubutions  total_pp
0 2020-01-01       bob                   3        25
1 2020-01-01  charlene                   2        40
2 2020-01-01     alice                   2        11
3 2020-01-01      edna                   1       100
4 2020-01-02  charlene                   1         5
5 2020-01-02     alice                   1        10

It's important for me to use apply() because I plan to do some API calls and database lookups in the functions.

ta, Andrew

by integrate u mean joining them together or using one apply to get those results directly? — ombk, Nov 30 '20 at 21:37
Well, I would love to see both approaches if possible but I had the idea of using one apply. — Andrew Holway, Nov 30 '20 at 21:48

score 2 · Answer 1 · answered Nov 30 '20 at 21:53

df.groupby(["date","name"])["contribution"].agg(["count","sum"]).reset_index().sort_values(by="count",ascending=False)

#output

     date       name    count   sum
1   2020-01-01  bob        3    40
0   2020-01-01  alice      2    25
2   2020-01-01  charlene   2    11
3   2020-01-01  edna       1    100
4   2020-01-02  alice      1    5
5   2020-01-02  charlene   1    10

So first, we group by date and name, then we select the column that we want to apply aggregations/calculations on, first we count each person's contribution. Then we sum them. After that to keep the shape of a normal dataframe, we reset_index and we sort_values by="count" in an descending way.

I'm afraid this doesn't work - as I say in the question I need to use apply because I need to combine data from other sources. — Andrew Holway, Nov 30 '20 at 22:08
@AndrewHolway hmmm, not sure i understand but anyway i will keep the answer — ombk, Nov 30 '20 at 22:09

Bill Huang · Answer 2 · 2020-11-30T23:30:29.340

groupby-agg is very powerful in such use case where multiple single-column aggregation functions are going to be computed within a single Groupby. The syntax is very flexible and straightforward, although not the most typing-saving.

Limitation: aggregation functions cannot take more than one column as input. If that is the case, one would have to fallback to .apply().

Demo

def myfunc(sr):
    """Just a customized function for demo purpose"""
    # N.B. cannot write sr.sum() somehow
    return np.sum(sr) / (np.std(sr) + 1)

df_out = df.groupby([pd.Grouper(key='date', freq='D'), "name"]).agg({
    # column: [func1, func2, ...]
    "contribution": [np.size,  # accepts 1) a function
                     "sum",    # or 2) a built-in function name
                     myfunc    # or 3) an externally defined function
                     ],
    "payment-type": [
        lambda sr: len(np.unique(sr))  # or 4) a lambda function
    ]
})

# postprocess columns and indexes
df_out.columns = ["num_contrubutions", "total_pp", "myfunc", "type_count"]
df_out.reset_index(inplace=True)

Result

                                                     # extra demo columns
        date      name  num_contrubutions  total_pp      myfunc  type_count
0 2020-01-01     alice                  2        25    2.941176           2
1 2020-01-01       bob                  3        40    3.128639           2
2 2020-01-01  charlene                  2        11    2.000000           2
3 2020-01-01      edna                  1       100  100.000000           1
4 2020-01-02     alice                  1         5    5.000000           1
5 2020-01-02  charlene                  1        10   10.000000           1

See also this post for an extensive discussions on more possibilities (e.g. NamedAgg).

Applying more than one function to a pandas dataframe

2 Answers2

Demo

Result