How do I convert a dataframe consisting of a column of sentences and a column of scores into one with a column of words and average scores?
Question: I have a Pandas dataframe that resembles this:

    
    
    sentence              score
    "This is a sentence." 5
    "Another sentence?"   8
    

And I want one that resembles this:

    
    
    word       total_score  count  normalized_score
    "sentence" 13           2      6.5
    "this"     5            1      5
    

etc.

How should I go about doing this? My thought is to remove all non-alphanumeric
characters, then use
[split()](http://www.tutorialspoint.com/python/string_split.htm) on all of the
cells containing sentences, then combine those words into a set, then use that
set to iterate through the original dataframe, counting the number of times a
word is used and the corresponding scores. This, however, seems inelegant and
potentially incredibly inefficient. Is there a better way to do this?

Note: Don't worry about stop words and assume all words are separated by
spaces

Edit:

The head of the actual data (after applying `wide = df.apply(lambda x:
pd.Series(x['score'], index=x['sentence']), axis=1)` ) is:

    
    
       score                                                                      title
    0      1                           [javascript, kml, compressor, for, google, maps]
    1      3             [ktbyte, challenge, programming, game, for, 9, 15, year, olds]
    2      4                              [worldometers, real, time, world, statistics]
    3      1                                                [apple, s, sales, policies]
    4     72                  [report, suggests, 21, hours, is, the, ideal, work, week]
    5      3  [new, paper, shows, how, to, get, control, without, injecting, new, code]
    

Strangely, unutbu's solution works for the first 5 rows, but not when the
sixth is added. When the sixth is added, Python returns `ValueError: cannot
reindex from a duplicate axis` (which seems to be Panda's vaguely defined
catch-all error for reindexing).


Answer: You could use `df.itertuples` to iterate through the rows of `df` and build a
long-format DataFrame of the form:

    
    
    In [86]: longframe
    Out[86]: 
       score      word
    0      5      This
    1      5        is
    2      5         a
    3      5  sentence
    4      8   Another
    5      8  sentence
    6      8  sentence
    

Once you have the data in this format, you could group by `word` and sum the
scores for each word, and use `value_counts` to count the frequency of each
word.

* * *
    
    
    import pandas as pd
    df = pd.DataFrame(
        {'score': [5, 8], 'sentence': ["This is a sentence.", "Another sentence sentence?"]})
    df['sentence'] = df['sentence'].str.findall(r'\w+')
    
    longframe = pd.DataFrame([(row.score, word) for row in df.itertuples() 
                              for word in row.sentence], 
                             columns=['score', 'word'])
    score = longframe.groupby('word')['score'].sum()
    count = longframe['word'].value_counts()
    result = pd.DataFrame({'score':score, 'count':count, 'normalized_score':score/count})
    result = result.reset_index()
    result = result.rename(columns={'index':'word'})
    print(result)
    

yields

    
    
           word  count  normalized_score  score
    0   Another      1               8.0      8
    1      This      1               5.0      5
    2         a      1               5.0      5
    3        is      1               5.0      5
    4  sentence      3               7.0     21
    

