跨非空列连接字符串

Question

希望执行类似的操作，但即使存在空值但不包含空值，也要完成行聚合

import pandas as pd
import numpy as np

df = pd.DataFrame(data= {'Subject': ['X', 'G', 'H', 'M'],
                         'Col1': ['cat', 'dog', np.nan, 'horse'],
                         'Col2': [np.nan, 'black', 'brown', 'grey'],
                         'Col3': ['small', 'medium', 'large', 'large']})

df['Col4'] = df['Col1'] + ', ' + df['Col2'] + ', ' + df['Col3']

为了澄清，这是我正在寻找的结果数据帧

  Subject   Col1   Col2    Col3                Col4
0       X    cat    NaN   small          cat, small
1       G    dog  black  medium  dog, black, medium
2       H    NaN  brown   large        brown, large
3       M  horse   grey   large  horse, grey, large

Answer 1

可以对列轴使用apply、dropna和join：

df['Col4'] = df[['Col1', 'Col2', 'Col3']].apply(lambda x: ','.join(x.dropna()), axis=1)

正如预期的那样：

  Subject   Col1   Col2    Col3              Col4
0       X    cat    NaN   small         cat,small
1       G    dog  black  medium  dog,black,medium
2       H    NaN  brown   large       brown,large
3       M  horse   grey   large  horse,grey,large

对于像这样的小数据帧，它应该比@yatu的方法快30%左右，但对于较大的数据帧，另一种方法更好

Answer 2

一种方法是在第一级上^{}和^{}（这将删除缺少的值）^{}，并使用str.join进行聚合：

df['Col4'] = (df.set_index('Subject')
                .stack()
                .groupby(level=0, sort=False)
                .agg(', '.join)
                .values)

print(df)

  Subject   Col1   Col2    Col3                Col4
0       X    cat    NaN   small          cat, small
1       G    dog  black  medium  dog, black, medium
2       H    NaN  brown   large        brown, large
3       M  horse   grey   large  horse, grey, large

时间安排-

df_ = pd.concat([df]*1000, axis=0).reset_index(drop=True)

%timeit df_[['Col1', 'Col2', 'Col3']].apply(lambda x: ','.join(x.dropna()), axis=1)
# 743 ms ± 17.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit (df_.set_index('Subject').stack().groupby(level=0, sort=False).agg(', '.join).values)
# 5.73 ms ± 168 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)