python - 制作所有唯一单词的数据框及其计数和

Question

我有一个这样的数据框df1

id `  text                             c1      
1     Hello world how are you people    1 
2     Hello people I am fine  people    1
3     Good Morning people               -1
4     Good Evening                      -1

我想让 df2 这样，它只包含 df1 的所有单词及其计数(总出现次数)和

我想对 c1 列求和并在 df2 中为其创建一个新列(仅当该行中有单词时才求和)。

预期输出:

Word      Totalcount     Points  

hello        2             2             
world        1             1              
how          1             1              
are          1             1              
you          1             1              
people       3             1              
I            1             1             
am           1             1              
fine         1             1             
Good         2             -2            
Morning      1             -1            
Evening      1             -1       

Answer 1

首先通过 DataFrame.pop 提取列, Series.str.split , DataFrame.stack 系列 和 DataFrame.join到原来的，然后通过 DataFrame.drop_duplicates 删除重复项并按 GroupBy.agg 聚合使用计数和 sum:

s = (df.pop('text')
       .str.split(expand=True)
       .stack()
       .reset_index(1, drop=True)
       .rename('text'))

df1 = (df.join(s)
         .reset_index(drop=True)
         .drop_duplicates(['id','text'])
         .groupby('text', sort=False)['c1']
         .agg([('Totalcount','size'),('Points','sum')])
         .reset_index()
         .rename(columns={'text':'Word'}))

print (df1)
       Word  Totalcount  Points
0     Hello           2       2
1     world           1       1
2       how           1       1
3       are           1       1
4       you           1       1
5    people           3       1
6         I           1       1
7        am           1       1
8      fine           1       1
9      Good           2      -2
10  Morning           1      -1
11  Evening           1      -1

编辑:

为了更好的性能使用chain.from_iterable与 numpy.repeat :

from itertools import chain

splitted = [x.split() for x in df['text']]
lens = [len(x) for x in splitted]

df = pd.DataFrame({
    'Word' : list(chain.from_iterable(splitted)), 
    'id' : df['id'].values.repeat(lens),
    'c1' : df['c1'].values.repeat(lens)
})

df1 = (df.drop_duplicates(['id','Word'])
         .groupby('Word', sort=False)['c1']
         .agg([('Totalcount','size'),('Points','sum')])
         .reset_index())

print (df1)
       Word  Totalcount  Points
0     Hello           2       2
1     world           1       1
2       how           1       1
3       are           1       1
4       you           1       1
5    people           3       1
6         I           1       1
7        am           1       1
8      fine           1       1
9      Good           2      -2
10  Morning           1      -1
11  Evening           1      -1