How to combine two pandas series?-CodePudding

I have a dataframe which looks like this

        Date           Description Deposits Withdrawls Balance
0       21-Aug-2020    Cash        0.00     10612.14   53063.19
...     ...            ...         ...      ...        ...
1000000 15-Nov-2155    Cheque      0.00     12345.00   55511.00

After performing this two operations

res1 = df.groupby('Date')['Withdrawls'].mean()
res2 = df.groupby(['Date', 'Description'])['Withdrawls'].mean()

I get two Series which look like this:

print(res1)
Date
2020-08-21  77189.019273
...
2155-11-15  125627.741943
Name: Withdrawls, Length: 49394, dtype: float64

and this:

print(res2)
Date       Description
2020-08-21 ATM          76862.370556
           Bill         44174.444545
           ...          ...
2155-11-15 Purchase     95530.093750
           RTGS         306642.666000
           ...          ...
Name: Withdrawls, Length: 650468, dtype: float64

What I need is to combine them like this. The empty description is a result from res1 and others are results from res2. How do I do it? (Assume that we also have series which get min and max for each grouping like res1 and res2. For simplicity you can just assume there is no Min and Max columns)

        Date           Type                          Min      Max        Avg
0       21-Aug-2020    Description="Reversal"        0.00     10612.14   53063.19
...     ...            ...                           ...      ...        ...
1000000 15-Nov-2155                                  0.00     12345.00   55511.00

CodePudding user response：

You can create 2 DataFrames df11, df22 first, add column Description, join together and sorting per multiple columns:

print (df)
                Date Description  Deposits  Withdrawls   Balance
0        21-Aug-2020        Cash       0.0        10.0  53063.19
0        21-Aug-2020        Cash       0.0       114.0  53063.19
0        21-Aug-2020        Cash       0.0       107.0  53063.19
1000000  15-Nov-2155      Cheque       0.0     12345.0  55511.00

df11 = (df.groupby(['Date', 'Description'])['Withdrawls']
          .agg(['min','max','mean'])
          .reset_index())

df22 = df.groupby('Date')['Withdrawls'].agg(['min','max','mean']).reset_index()

df = (pd.concat([df11, df22.reindex(df11.columns, fill_value='', axis=1)])
        .sort_values(['Date','Description'], ascending=[True, False], ignore_index=True))
print (df)
          Date Description      min      max     mean
0  15-Nov-2155      Cheque  12345.0  12345.0  12345.0
1  15-Nov-2155              12345.0  12345.0  12345.0
2  21-Aug-2020        Cash     10.0    114.0     77.0
3  21-Aug-2020                 10.0    114.0     77.0