Python Pandas - add two groupby functions to one df-CodePudding

DF has data like -

students = [{'Class':'Class 8', 'Div': 'A', 'Name':'Ankit', 'Marks':94, 'Practical_Marks':48},
            {'Class':'Class 8', 'Div': 'A', 'Name':'Swapnil', 'Marks':92, 'Practical_Marks':50},
            {'Class':'Class 9', 'Div': 'A', 'Name':'Priya', 'Marks':96, 'Practical_Marks':49},
            {'Class':'Class 9', 'Div': 'B', 'Name':'Shivangi', 'Marks':98, 'Practical_Marks':42}]

I have to code it in Python Pandas and group by data as two separate lists for common class and division. Need output as below f-

CodePudding user response：

def create_prac_theory_marks(d):
    df_theory = d.drop(columns=['Class','Div','Marks'])
    theory = [ row.to_dict() for _, row in df_theory.iterrows()]
    df_prac = d.drop(columns=['Class','Div','Practical_Marks'])
    prac = [ row.to_dict() for _, row in df_prac.iterrows()]
    return [theory,prac]

students = [{'Class':'Class 8', 'Div': 'A', 'Name':'Ankit', 'Marks':94, 'Practical_Marks':48},
            {'Class':'Class 8', 'Div': 'A', 'Name':'Swapnil', 'Marks':92, 'Practical_Marks':50},
            {'Class':'Class 9', 'Div': 'A', 'Name':'Priya', 'Marks':96, 'Practical_Marks':49},
            {'Class':'Class 9', 'Div': 'B', 'Name':'Shivangi', 'Marks':98, 'Practical_Marks':42}]

df = pd.DataFrame(students)
df2 = df.groupby(['Class','Div']).apply(lambda x: create_prac_theory_marks(x)).reset_index()
df2 = df2.join(pd.DataFrame(df2[0].to_list(), columns=['Marks', 'Practical Marks'])).drop(columns=[0])

df2 has the output

     Class Div                                              Marks  \
0  Class 8   A  [{'Name': 'Ankit', 'Practical_Marks': 48}, {'N...   
1  Class 9   A         [{'Name': 'Priya', 'Practical_Marks': 49}]   
2  Class 9   B      [{'Name': 'Shivangi', 'Practical_Marks': 42}]   

                                     Practical Marks  
0  [{'Name': 'Ankit', 'Marks': 94}, {'Name': 'Swa...  
1                   [{'Name': 'Priya', 'Marks': 96}]  
2                [{'Name': 'Shivangi', 'Marks': 98}]

CodePudding user response：

The following snippet should do the job for you:

import pandas as pd

# Sample Data
students = [{'Class':'Class 8', 'Div': 'A', 'Name':'Ankit', 'Marks':94, 'Practical_Marks':48}, {'Class':'Class 8', 'Div': 'A', 'Name':'Swapnil', 'Marks':92, 'Practical_Marks':50}, {'Class':'Class 9', 'Div': 'A', 'Name':'Priya', 'Marks':96, 'Practical_Marks':49}, {'Class':'Class 9', 'Div': 'B', 'Name':'Shivangi', 'Marks':98, 'Practical_Marks':42}]
 
# Load data as dataframe
df = pd.DataFrame(students)

# Group-by Class & Div for marks and export as record-oriented dict
marks_df = df.groupby(['Class', 'Div'])[['Name', 'Marks']].apply(lambda x: x.to_dict(orient='record')).reset_index().rename({0: 'Marks'}, axis=1)
# Group by Class & Div for practical marks and export as record-oriented dict
 
practical_marks_df = df.groupby(['Class', 'Div'])[['Name', 'Practical_Marks']].apply(lambda x: x.to_dict(orient='record')).reset_index().rename({0: 'Practical_Marks'}, axis=1)
 
# Merge the two group results into single Dataframe
result_df = marks_df.merge(practical_marks_df, on=['Class', 'Div'])