Groupby of different columns with different aggreagation with cumsum with another dataset

You can use .groupby() and named aggregation to transform df2 to the same layout as df1 and then append the result to df1, followed by another round of groupby() and aggregation, as follows:

df3 = (df2.groupby(['ID', 'Date'], as_index=False, sort=False)
          .agg(A_sum=('A', 'sum'), A_count=('A', 'count'), 
               B_sum=('B', 'sum'), B_count=('A', 'count'), 
               A_last=('A', 'last'), B_last=('B', 'last'))
      )

df_out = (df1.append(df3)
             .groupby('ID', as_index=False)
             .agg({'Date': 'last', 
                   'A_sum': 'sum', 'A_count': 'sum', 
                   'B_sum': 'sum', 'B_count': 'sum', 
                   'A_last': 'last', 'B_last': 'last'})
         )

Result:

print(df_out)


    ID    Date  A_sum  A_count  B_sum  B_count  A_last  B_last
0  abc  02/jan     50        4     48        4      14      13
1  xyz  02/jan    118        6    100        6      20      16

Slighlty lenghty way

>>> import pandas as pd
>>> from io import StringIO
>>>
>>> df1 = pd.read_csv(StringIO("""ID    Date     A_sum  A_count   B_sum   B_count  A_last  B_last
... abc   01/jan    26       2        25       2       0      0
... xyz   01/jan    54       3        45       3       4      6"""), sep="\s+")
>>>
>>>
>>> df2 = pd.read_csv(StringIO("""ID     Date     Time      A         B
... abc   02/jan     11       10        10
... abc   02/jan     12       14        13
... xyz   02/jan      1       26        24
... xyz   02/jan      2       18        15
... xyz   02/jan      3       20        16"""), sep="\s+")
>>>
>>>
>>>
>>> df2["A_sum"]   = df2.groupby("ID")["A"].transform("sum")
>>> df2["A_count"] = df2.groupby("ID")["A"].transform("count")
>>> df2["A_last"]  = df2.groupby("ID")["A"].transform("last")
>>>
>>> df2["B_sum"]   = df2.groupby("ID")["B"].transform("sum")
>>> df2["B_count"] = df2.groupby("ID")["B"].transform("count")
>>> df2["B_last"]  = df2.groupby("ID")["B"].transform("last")
>>>
>>> del df2["Time"]
>>> del df2["A"]
>>> del df2["B"]
>>>
>>> df2 = df2.groupby("ID").apply(lambda x: x.iloc[-1])
>>>
>>> df3 = pd.concat([df1, df2])
>>>
>>> df3.groupby('ID').agg({"Date": 'last', 'A_sum': 'sum', 'B_sum' : 'sum', 'A_count': 'sum', 'B_count': 'sum', 'A_last': 'last', 'B_last': 'last'})
       Date  A_sum  B_sum  A_count  B_count  A_last  B_last
ID
abc  02/jan     50     48        4        4      14      13
xyz  02/jan    118    100        6        6      20      16

for i in cols:
   df3 = (df2.groupby(['ID', 'Date'], as_index=False).agg(i+'_Num'=(i, 'sum'),i+'_denom'=(i,'count'),i+'_last'=(i, 'last'))
   final = (df1.append(df3).groupby('ID', as_index=False).agg({i+'_Num':'sum',i+'_denom':'sum', i+'_Last': 'last'}))
But it is not working

Use:

#https://stackoverflow.com/a/67800033/2901002
cols = ['A','B']

df11 = df2.groupby(['ID','Date'])[cols].agg(['sum','count'])
df11.columns = df11.columns.map(lambda x: f'{x[0]}_{x[1]}')

df22 = df2.groupby(['ID','Date'])[cols].last().add_suffix('_last')

df3 = pd.concat([df11, df22], axis=1).reset_index(level=1)
print (df3)
       Date  A_sum  A_count  B_sum  B_count  A_last  B_last
ID                                                         
abc  02/jan     24        2     23        2      14      13
xyz  02/jan     64        3     55        3      20      16

Filter only columns from df1 for sum:

df33 = df1.filter(regex='ID|_sum|count').set_index('ID')
print (df33)
     A_sum  A_count  B_sum  B_count
ID                                 
abc     26        2     25        2
xyz     54        3     45        3

Join together, sum and assign missing date if necessary:

df = pd.concat([df3, df33]).sum(level=0).astype(int).assign(Date = df3['Date']).set_index('Date', append=True).reset_index()
print (df)
    ID    Date  A_sum  A_count  B_sum  B_count  A_last  B_last
0  abc  02/jan     50        4     48        4      14      13
1  xyz  02/jan    118        6    100        6      20      16

You can concat both the df's and then you can use groupby:

cols = df1.columns
df1 = df1[['ID','Date','A_sum', 'B_sum']]
df2 = df2.drop('Time', 1)
df1.columns = df2.columns
merged_df  = pd.concat([df1, df2]).groupby(['ID']).agg({'A' : [sum , 'count', 'last'], 'B' : [sum , 'count', 'last'], 'Date': 'last'})
merged_df.columns = merged_df.columns.map('_'.join)

OUTPUT:

     A_sum  A_count  A_last  B_sum  B_count  B_last Date_last
ID                                                           
abc     50        3      14     48        3      13    02/jan
xyz    118        4      20    100        4      16    02/jan