Python Pandas groupby, with a date column with different values, then returns a dataframe with the date column filled with the latest date

Question

So I have a data like this:

I want to group them and sum the values of Month 0 - Month 3, I can achieve that using pandas groupby. The problem is the End date column has different values, and I want to take the latest date in the column. For this example, means I want the End date column to have the value 2020-09-25. As below:

How do I do this with pandas groupby? For your convenience, the variable for the columns names are below:

details_columns = [ "Person Name", "Bill rate", "Project ERP","Status", "Assignment", "Engagement Code", "End date"]
sum_columns = ["Month 0", "Month 1", "Month 2", "Month 3"]

I need the return value to be a DataFrame hoping anyone can help. Thanks!

Text data:

Person Name Bill rate Project ERP Status Assignment Engagement Code End date Current Month U% Month 1 U% Month 2 U% Month 3 U%
John Doe 3500000 0.58 Chargeable - Standard Project A 21572323 2020-08-22 0 0.5 0.3 0.2
John Doe 3500000 0.58 Chargeable - Standard Project A 21572323 2020-05-22 0.4 0.25 0 0
John Doe 3500000 0.45 Chargeable - Standard Project B 21579528 2020-09-25 0 0.7 0.7 0.7
John Doe 3500000 0.45 Chargeable - Standard Project B 21579528 2020-05-22 0.2 0.12 0 0
John Doe 3500000 0.45 Chargeable - Standard Project B 21579528 2020-04-03 0.1 0 0 0

Because latest is max value of datetime, is possible use solutions from dupe. — jezrael, Apr 17 '20 at 07:30
@jezrael thanks for the link, i checked it out. the problem is, i need to sum the columns of sum_columns variable. how should i do that? — Gerald Tambunan, Apr 17 '20 at 07:50
OK, so need groups by all columns in list `details_columns` withiut end and aggregate `sum`? Like `df1 = df.groupby([ "Person Name", "Bill rate", "Project ERP","Status", "Assignment", "Engagement Code"])[sum_columns].sum()` — jezrael, Apr 17 '20 at 07:54
this approach will remove the end date column, i need the end date column. sorry for bothering you — Gerald Tambunan, Apr 17 '20 at 07:57
OK, is possible create text version of data? Because I want create answer, but canot copy them — jezrael, Apr 17 '20 at 08:05
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/211854/discussion-between-gerald-tambunan-and-jezrael). — Gerald Tambunan, Apr 17 '20 at 08:20

score 0 · Accepted Answer · answered Apr 17 '20 at 08:36

Create dictionary d for sum columns and max value for column End date and then aggregate by GroupBy.agg, last is added DataFrame.reindex for same order of columns like original DataFrame:

cols = ["Person Name", "Bill rate", "Project ERP","Status", "Assignment","Engagement Code"]
sum_columns = ["Current Month U%", "Month 1 U%", "Month 2 U%","Month 3 U%"]

d = dict.fromkeys(sum_columns, 'sum')
d["End date"] = 'max'

df1 = df.groupby(cols, as_index=False).agg(d).reindex(df.columns, axis=1)
print (df1)
  Person Name  Bill rate  Project ERP       Status          Assignment  \
0    John Doe    3500000         0.45  Chargeable   Standard Project B   
1    John Doe    3500000         0.58  Chargeable   Standard Project A   

   Engagement Code    End date  Current Month U%  Month 1 U%  Month 2 U%  \
0         21579528  2020-09-25               0.3        0.82         0.7   
1         21572323  2020-08-22               0.4        0.75         0.3   

   Month 3 U%  
0         0.7  
1         0.2

Python Pandas groupby, with a date column with different values, then returns a dataframe with the date column filled with the latest date

1 Answers1