Python Pandas 基于 case 语句进行分组

Question

这个问题已经有答案了:

How can I pivot a dataframe? (5 个回答)

已关闭 3 年前。

每个 child 有两行，我试图根据 child 的姓名进行分组，并将家长的姓名压平在同一行中。

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`

输出应该是

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

我尝试了这段代码，它可以帮助我仅展平“PARENT_FIRST_NAME”，但无法找到包含“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'})

Answer 1

这里有两种方法:

方法一:将索引设置为子级和父级的性别，然后取消堆叠性别。按性别对列进行排序并重命名，然后重置索引。

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  

方法 2:根据性别对数据帧进行子集化，然后合并回父级(母亲或父亲，视情况而定)。

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'])