了解如何通过在每列的任何行中获取value='yes'来按名称组合行

Question

我需要将行与列“name”合并，表中的某些行中有值为“yes”的不同列，如下所示

下面的模板给出了输入和预期输出：

name    department  feature1    feature2    feature3
x1         cs                      yes        yes
x1         cs         yes       
x1         ec           
x2         cs         yes          yes
x2         ec                      yes  

我需要得到的输出是：

x1         cs        yes            yes       yes
x1         ec           
x2         cs        yes            yes
x2         ec                       yes 

建议请使用Python和熊猫。你知道吗

Answer 1

您可以使用：

#if want filter only `yes` values 
cols = df.columns.difference(['name','department'])
df[cols] = df[cols] == 'yes'
print (df)
  name department  feature1  feature2  feature3
0   x1         cs     False      True      True
1   x1         cs      True     False     False
2   x1         ec     False     False     False
3   x2         cs      True      True     False
4   x2         ec     False      True     False

然后^{}通过dict聚合^{}和最后一个^{}：

df= df.groupby(['name','department']) \
      .max() \
      .replace({True:'yes',False:np.nan}) \
      .reset_index()

print (df)
  name department feature1 feature2 feature3
0   x1         cs      yes      yes      yes
1   x1         ec      NaN      NaN      NaN
2   x2         cs      yes      yes      NaN
3   x2         ec      NaN      yes      NaN

感谢您的评论，AChampion，也可以使用^{}：

df= df.groupby(['name','department']) \
      .any() \
      .replace({True:'yes',False:np.nan}) \
      .reset_index()

print (df)
  name department feature1 feature2 feature3
0   x1         cs      yes      yes      yes
1   x1         ec      NaN      NaN      NaN
2   x2         cs      yes      yes      NaN
3   x2         ec      NaN      yes      NaN

如果所有值仅为yes和NaNs，则也适用：

df = df.fillna('').groupby(['name', 'department']).max().reset_index()
print (df)
  name department feature1 feature2 feature3
0   x1         cs      yes      yes      yes
1   x1         ec                           
2   x2         cs      yes      yes         
3   x2         ec               yes         

编辑：

您可以使用聚合函数通过dict comprehension创建自定义dict，并使用^{}：

d = {'feature3': ['yes', np.nan, np.nan, np.nan, np.nan], 
     'feature2': ['yes', np.nan, np.nan, 'yes', 'yes'], 
     'name': ['x1', 'x1', 'x1', 'x2', 'x2'], 
     'count': [10.0, 30.0, np.nan, 20.0, 3.0],
     'feature1': [np.nan, 'yes', np.nan, 'yes', np.nan], 
     'department': ['cs', 'cs', 'ec', 'cs', 'ec'], 
     'description': ['xsdepartment1', 'xsdepartment2', np.nan, 'department1', 'department3']}

c = ['name','department','feature1','feature2','feature3','count','description']
df = pd.DataFrame(d, columns = c)
print (df)
  name department feature1 feature2 feature3  count    description
0   x1         cs      NaN      yes      yes   10.0  xsdepartment1
1   x1         cs      yes      NaN      NaN   30.0  xsdepartment2
2   x1         ec      NaN      NaN      NaN    NaN            NaN
3   x2         cs      yes      yes      NaN   20.0    department1
4   x2         ec      NaN      yes      NaN    3.0    department3

cols = df.columns.difference(['name','department','count','description'])

f = lambda x: tuple(x)
d = {x:'max' for x in cols}
d['count'] = f
d['description'] = f
print (d)
{'feature3': 'max', 
'feature1': 'max', 
'feature2': 'max', 
'description': <function <lambda> at 0x000000000F6FC598>, 
'count': <function <lambda> at 0x000000000F6FC598>}

---

df[cols] = df[cols] == 'yes'
print (df)
  name department  feature1  feature2  feature3  count    description
0   x1         cs     False      True      True   10.0  xsdepartment1
1   x1         cs      True     False     False   30.0  xsdepartment2
2   x1         ec     False     False     False    NaN            NaN
3   x2         cs      True      True     False   20.0    department1
4   x2         ec     False      True     False    3.0    department3

df = df.groupby(['name', 'department']).agg(d).reset_index()
df[cols] = df[cols].replace({True:'yes',False:np.nan})
print (df)
  name department feature3 feature1 feature2                     description  \
0   x1         cs      yes      yes      yes  (xsdepartment1, xsdepartment2)   
1   x1         ec      NaN      NaN      NaN                          (nan,)   
2   x2         cs      NaN      yes      yes                  (department1,)   
3   x2         ec      NaN      NaN      yes                  (department3,)   

          count  
0  (10.0, 30.0)  
1        (nan,)  
2       (20.0,)  
3        (3.0,)