Question: I have a normalized (DB wise) data set, where I have 3 columns (~5000 rows) e.g.: 

```
user        phrase  tfw
517187571   able    1
517187571   abroad  0.4
1037767202  abuse   0.272727
517187571   accuse  0.8
803230586   acknowledge 0.4
...

```

I need to turn this into a matrix like view where the rows will be the users and columns will be the phrases and inside the matrix there will be the values of tfw in the appropriate row/column index.
Anyone have any sharp idea how to do this efficiently in python?
A desired output will be (for the example above):

```
user/phrase   able   abroad   abuse    accuse   acknowledge
517187571     1      0        0        0        0
517187571     0      0.4      0        0        0
1037767202    0      0        0.272727 0        0
...

```

I tried to do it on MySQL DB in SQL query and came up with this genius query that doesn't work:

```
SELECT
CONCAT('SELECT user,',
GROUP_CONCAT(sums),
' FROM clustering_normalized_dataset GROUP BY user')
FROM (
 SELECT CONCAT('SUM(phrase=\'', phrase, '\') AS `', phrase, '`') sums
 FROM clustering_normalized_dataset
 GROUP BY phrase
 ORDER BY COUNT(*) DESC
 ) s
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

```

Answer: 
Using the library [`pandas`](http://pandas.pydata.org/), this is a one-linear with a simple pivot.

```
data = [
[517187571,   "able",1],
[517187571,   "abroad",  0.4],
[1037767202,  "abuse",   0.272727],
[517187571,   "accuse",  0.8],
[803230586,   "acknowledge", 0.4]]

import pandas as pd
df = pd.DataFrame(data,columns=("user","phrase","tfw"))
print df.pivot("user","phrase","tfw")

```

This gives

```
phrase      able  abroad     abuse  accuse  acknowledge
user                                                   
517187571      1     0.4       NaN     0.8          NaN
803230586    NaN     NaN       NaN     NaN          0.4
1037767202   NaN     NaN  0.272727     NaN          NaN

```

It is trivial to replace the `Nan`'s with 0.0, but sometimes it is nice to leave them in to indicate that you don't have data for that item. You can always sum over the valid ranges anyways. The *huge* advantage over the other methods like the one you propose is that the extra data isn't stored in memory.
5000 rows isn't actually that much data. You want an NxM matrix where N is len(distinct()).

It's a bit brute force but I'd probably build the matrix populated with 0s and then scan through the master list to plug in all the extra data you have.

Let's imagine you've just pulled out all the raw data from the db into python

```
raw = [
    [517187571, 'able', 1],
    [517187571, 'abroad', 0.4],
    [1037767202, 'abuse', 0.272727],
    [517187571, 'accuse', 0.8],
    [803230586, 'acknowledge', .4],
    ...
]

# find our row / column titles
users = sorted(set(r[0] for r in raw))
words = sorted(set(r[1] for r in raw))

# indexes so we can see which position in the matrix belongs to a given word / user
user_to_pos = {u:i for i, u in enumerate(users)}
word_to_pos = {u:i for i, u in enumerate(words)}

# make the empty matrix
matrix = []
for u in users:
    matrix.append([0] * len(words))

for user, word, tfw in raw:
    matrix[user_to_pos[user]][word_to_pos[word]] = tfw

```

If you were using numpy you could build that matrix more quickly, and if you were using pandas you could get it to do the column names for you (depending on what you're doing afterward it'll be worth looking into those libraries).