使用dataframe和list之间的重叠字数创建新的dataframe列

Question

我在修复以下问题时遇到问题： 我有一个数据框，每一行上都有标记文本，看起来（有些东西）如下

index feelings           
1     [happy, happy, sad] 
2     [neutral, sad, mad] 
3     [neutral, neutral, happy]

和单词列表lst1=[happy, fantastic]，lst2=[mad, sad]，lst3=[neutral]，我想检查我的数据帧中每一行的单词在列表中出现的次数。因此，输出将如下所示：

index feelings                  occlst1 occlst2 occlst3      
1     [happy, happy, sad]       2      1        0
2     [neutral, sad, mad]       0      2        1
3     [neutral, neutral, happy] 1      0        2

因此，我想创建一个新列，并将dataframe单元格与列表进行比较

提前谢谢

Answer 1

您可以构建一个参考系列，将感受与列表id相匹配。然后explode+merge+pivot_table：

ref = pd.Series({e: 'occlist_%s' % (i+1) for i,l in enumerate([lst1, lst2, lst3]) for e in l}, name='cols')

## ref:
# happy        occlst1
# fantastic    occlst1
# mad          occlst2
# sad          occlst2
# neutral      occlst3
# Name: cols, dtype: object

df.merge((df.explode('feelings')  # lists to single rows
           # create a new column with list id
           .merge(ref, left_on='feelings', right_index=True)
           # reshape back to 1 row per original index
           .pivot_table(index='index', columns='cols', values='feelings', aggfunc='count', fill_value=0)
          ),
         left_on='index', right_index=True  # merge with original df
        )

NB.我在这里考虑到index是一列，如果是索引，则需要添加df.reset_index()步骤

输出：

   index                   feelings  occlist_1  occlist_2  occlist_3
0      1        [happy, happy, sad]          2          1          0
1      2        [neutral, sad, mad]          0          2          1
2      3  [neutral, neutral, happy]          1          0          2

输入：

df = pd.DataFrame({'index': [1, 2, 3],
                   'feelings': [['happy', 'happy', 'sad'],
                                ['neutral', 'sad', 'mad'],
                                ['neutral', 'neutral', 'happy']
                               ]})
lst1=['happy', 'fantastic']
lst2=['mad', 'sad']
lst3=['neutral']

Answer 2

使用collections.Counter

设置：

import pandas as pd
from collections import Counter  # Load 'Counter'

df = pd.DataFrame({'feelings': [['happy', 'happy', 'sad'],
                                ['neutral', 'sad', 'mad'],
                                ['neutral', 'neutral', 'happy']]})

lst1 = ['happy', 'fantastic']
lst2 = ['mad', 'sad']
lst3 = ['neutral']

# Create an intermediate dict
occ = {'occlst1': lst1, 'occlst2': lst2, 'occlst3': lst3}

更新：根据@mozway的建议

def count_occ(sr):
    return {col: sum([v for k, v in Counter(sr).items() if k in lst])
                     for col, lst in occ.items()}

df = pd.concat([df, df['feelings'].apply(count_occ).apply(pd.Series)], axis=1)

注意：为了可读性，我没有使用除feelings之外的任何其他列。但是concat函数从df还原所有列

输出：

>>> df
                    feelings  occlst1  occlst2  occlst3
0        [happy, happy, sad]        2        1        0
1        [neutral, sad, mad]        0        2        1
2  [neutral, neutral, happy]        1        0        2

Answer 3

您还可以使用：

my_lists = [lst1, lst2, st3]
occ = pd.DataFrame.from_records(df['feelings'].apply(lambda x: [pd.Series(x).isin(l).sum() for l in my_lists]).values, columns=['occlst1', 'occlst2', 'occlst3'])
df_occ = df.join(occ)

输出：

                    feelings  occlst1  occlst2  occlst3
0        [happy, happy, sad]        2        1        0
1        [neutral, sad, mad]        0        2        1
2  [neutral, neutral, happy]        1        0        2