Aggregate data when dealing with both continuous and categorical data

Andrea Erőss 0
Viewed 25

I have the following data:

enter image description here

There are six customers who took out loans multiple times offered by Agents. Agents serve multiple customers. For example Agent 306 provided loans to Customer 1 and 2. Similarly, Agent 309 provided loans to Customer 5 and 6. I would like to aggregate the amount of loans each customer took and get something like in the table below. It is important I see the gender of the customer and agent after aggregation.

enter image description here

I tried the following code:


import pandas as pd

data = {'Customer_ID': [1, 1, 1, 2, 2, 3, 4, 4, 5, 5, 5, 5, 6, 6],
      'Loan': [200, 250, 300, 400, 300, 500, 150, 150, 400, 250, 150, 300, 200, 200],
     'CustomerGender': ['M', 'M', 'M', 'F', 'F', 'M', 'M', 'M', 'F', 'F', 'F', 'F', 'F', 'F'],
     'Agent_ID': [306, 306, 306, 306, 306, 307, 308, 308, 309, 309, 309, 309, 309, 309], 
     'AgentGender': ['F', 'F', 'F', 'M', 'M','M', 'M', 'M', 'F', 'F', 'F', 'F', 'F', 'F']}
 
# transform to dataframe

data = pd.DataFrame(data)


# declare the two gender columns categorical

data['AgentGender']=data['AgentGender'].astype('category')

data['CustomerGender']=data['CustomerGender'].astype('category')
 
# aggregate the data by Customer_ID to see the total amount of loan each customer took.

data.groupby(data['Customer_ID']).sum()

What I get is the following:

enter image description here

How can I aggregate by Customer_ID and be able to see the gender of customers and agents please? Many thanks.

aggregate group-by python
1 Answers

choose all the columns you need within a groupby and sum Loan amount

df.groupby(['Customer_ID','Customer_Gender','Agent_ID', 'Agent_Gender'])['Loan'].sum().reset_index()

or if you are converting the columns to categories, add observed=True parameter

df.groupby(['Customer_ID','Customer_Gender','Agent_ID', 'Agent_Gender'], observed=True)['Loan'].sum().reset_index()


    Customer_ID     Customer_Gender     Agent_ID    Agent_Gender    Loan
0   1                       M                306    F                750
1   2                       F                306    M                700
2   3                       M                307    M                500
3   4                       M                308    M                300
4   5                       F                309    F                1100
5   6                       F                309    F                400
userCaFe0
Related