Make a dataframe of all unique words with their count and

Question

I have a dataframe df1 like this

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

I want to make df2 such that, it contain all the words of df1only once with their count (total occurrence) and

I want to sum c1 column and make a new column of it in df2 (sum only if a word is in that row).

Expected output:

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

First extract column by DataFrame.pop, Series.str.split,DataFrame.stack for Series and DataFrame.join to original, then remove duplicates by DataFrame.drop_duplicates and aggregate by GroupBy.agg with counts and 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

EDIT:

For better performance use chain.from_iterable with 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