Column A value based on groupby aggregation of Column B

user_14550300 0
Viewed 44

I have a pandas dataframe with donation information, example below:

DonorID  DonationID     Date        Amount
a1234    3767           1/1/2019    $100
a1234    2193           2/1/2019    $50
a1234    0915           3/1/2019    $75
b5678    4562           5/1/2019    $45
b5678    8965           4/1/2019    $80

I'm using the groupby function to calculate the total per DonorID and min date but would also like to calculate the amount of the earliest donation and DonationID by DonorID.

df2 = df1.groupby(['DonorID'], as_index=False ).agg( {'Amount':sum,'Date':min})

Current output is:

DonorID      Amount         Date
a1234        $225           1/1/2019
b5678        $125           4/1/2019

The desired output would be along the lines of:

DonorID      Amount          Date               First Gift Amount      DonationID
a1234        $225            1/1/2019           $100                   3767
b5678        $125            4/1/2019           $80                    8965

Any help would be greatly appreciated!

pandas-groupby pandas python
3 Answers

You might want something like this:

df.sort_values('Date').groupby(['DonorID'], as_index=False)\
  .agg({'Amount':['sum', 'first'], 'Date': 'min', 'DonationID': 'first'})
user_7549910

There's 2 ways you can approach this.

  1. You can first sort your dataframe on the "date" column. This will ensure that when you use the aggregation method "first" that you will get whichever value that corresponds with the minimum date.
df2 = (df1
       .sort_values("Date")
       .groupby(["DonorID"], as_index=False)
       .agg(
           amount=("Amount", "sum"),
           date=("Date", "min"),
           first_amount=("Amount", "first"),
           donation_id=("DonationID", "first"))
      )

print(df2)
  DonorID  amount       date  first_amount  donation_id
0   a1234     225 2019-01-01           100         3767
1   b5678     125 2019-04-01            80         8965
  1. If you don't want to use sort_values (for whatever reason). You can assign a temporary column to get the idxmin() of your "Date" column. Then you can use map to extract the related values from your original dataframe:
df2 = (df1
       .groupby(["DonorID"], as_index=False)
       .agg(
           amount=("Amount", "sum"),
           date=("Date", "min"),
           min_date_idx=("Date", "idxmin"))
       .assign(
           first_amount=lambda d: d["min_date_idx"].map(df1["Amount"]),
           donation_id=lambda d: d["min_date_idx"].map(df1["DonationID"])
       ))

print(df2)

  DonorID  amount       date  min_date_idx  first_amount  donation_id
0   a1234     225 2019-01-01             0           100         3767
1   b5678     125 2019-04-01             4            80         8965
user_142784480

Here is a way using the agg() method and unpacking a dictionary. This allows you to have column names with spaces in them.

df['Date'] = pd.to_datetime(df['Date'])
df['Amount'] = df['Amount'].str.replace('$','')
df['Amount'] = pd.to_numeric(df['Amount'])
df.sort_values(by='Date',ascending=True).groupby('DonorID').agg(**{'Amount': ('Amount','sum'),'Date':('Date','first'),'First Gift Amount': ('Amount','first'),'DonationID':('DonationID','first')})
user_138021150
Related