Python Pandas group by based on case statement

Question

I have two rows for each child where I am trying to group by on Child Name and flatten Parent's name in Same row.

CHILD_FIRST_NAME CHILD_LAST_NAME PARENT_FIRST_NAME PARENT_LAST_NAME PARENT_GENDER
Sarah            Marshal         David             Marshal          M
Sarah            Marshal         Caren             Marshal          F
Kiran            Mishra          Raj               Mishra           M
Kiran            Mishra          Geetha            Mishra           F`

Output should be

CHILD_FIRST_NAME CHILD_LAST_NAME FATHER_FIRST_NAME FATHER_LAST_NAME MOTHER_FIRST_NAME MOTHER_LAST_NAME
Sarah            Marshal         David             Marshal          Caren             Marshal
Kiran            Mishra          Raj               Mishra           Geetha            Mishra

I tried this code, which helps me to flatten 'PARENT_FIRST_NAME' only but couldn't figure out an option to include 'PARENT_LAST_NAME'

df.groupby(['CHILD_FIRST_NAME','CHILD_LAST_NAME','PARENT_GENDER'])['PARENT_FIRST_NAME'].max().unstack().rename(columns={'M': 'FATHER_FIRST_NAME', 'F': 'MOTHER_FIRST_NAME'})

You can just groupby() by 'CHILD_FIRST_NAME', then creates two new datasets. The first with only fathers (df['parent_gender']=='M' ) and the other with only mothers. Make another groupby() by 'CHILD_FIRST_NAME' in each of the news dataframes. Finally, merge these three datasets using 'CHILD_FIRST_NAME' as key variable — Lucas, Oct 20 '19 at 19:47

Alexander · Accepted Answer · 2019-10-20T19:58:51.047

Here are two methods:

Method 1: Set the index to the child and gender of the parent, then unstack the gender. Resort the columns by gender and rename them, then reset the index.

df2 = (
    df
    .set_index(['CHILD_FIRST_NAME', 'CHILD_LAST_NAME', 'PARENT_GENDER'])
    .unstack()
    .sort_index(axis=1, level=1, ascending=False)
)
df2.columns = ['FATHER_FIRST_NAME', 'FATHER_LAST_NAME', 'MOTHER_FIRST_NAME', 'MOTHER_LAST_NAME']
df2 = df2.reset_index()

>>> df2
  CHILD_FIRST_NAME CHILD_LAST_NAME FATHER_FIRST_NAME FATHER_LAST_NAME  \
0            Sarah         Marshal             David          Marshal   
1            Kiran          Mishra               Raj           Mishra   

  MOTHER_FIRST_NAME MOTHER_LAST_NAME  
0             Caren          Marshal  
1            Geetha           Mishra

Method 2: Subset the dataframe based on gender and merge back the parent (mother or father, as the case may be).

df2 = df[['CHILD_FIRST_NAME', 'CHILD_LAST_NAME']].drop_duplicates()

df_temp = (
    df
    .loc[df['PARENT_GENDER'].eq('M')]
    .rename(columns={'PARENT_FIRST_NAME': 'FATHER_FIRST_NAME',
                     'PARENT_LAST_NAME': 'FATHER_LAST_NAME'})
    .drop(columns='PARENT_GENDER')
)
df2 = df2.merge(df_temp, on=['CHILD_FIRST_NAME', 'CHILD_LAST_NAME'])

df_temp = (
    df
    .loc[df['PARENT_GENDER'].eq('F')]
    .rename(columns={'PARENT_FIRST_NAME': 'MOTHER_FIRST_NAME',
                     'PARENT_LAST_NAME': 'MOTHER_LAST_NAME'})
    .drop(columns='PARENT_GENDER')
)
df2 = df2.merge(df_temp, on=['CHILD_FIRST_NAME', 'CHILD_LAST_NAME'])

Python Pandas group by based on case statement

1 Answers1