Convert columns of words and weight into sparse matrix

Question

I have a table like below:

DocumentId Words Weight

1   alpha   2.5
1   beta    4.7
1   gamma   3
2   beta    8
2   gamma   2
3   alpha   5
4   apha    2
4   gamma   6

I want it to convert it to

DocumentId  alpha   beta   gamma
       1       2.5  4.7    3
       2       0    8      2
       3       5    0      0
       4       2    0      6

The issue is I have around 60,000 thousands unique words and 7 millions documents.

Is there a efficient way to convert this?

Please add the text representation of the dataframe. Something that can be copied into a text editor — Dani Mesejo, Jan 11 '19 at 20:32
I have tried pivoting using pivot function from pandas but it fails miserably — Sahil Dahiya, Jan 11 '19 at 20:38

score 0 · Answer 1 · answered Jan 11 '19 at 20:40

Just had to do this myself. The proper terminology is converting from a longitudinal format to a wide format. You'll want to use df.pivot(). Based off the columns you want to reduce and the new column headers you want, your code will be

df.pivot(index = 'DocumentId', columns = 'Words')
df.fillna(0)

Convert columns of words and weight into sparse matrix

1 Answers1