pythonpandasnumpygraphlabsframe

Converting a unique columns into SFrame headers with corresponding values

I have a tab-separated file:

$ echo -e 'abc\txyz\t0.9\nefg\txyz\t0.3\nlmn\topq\t0.23\nabc\tjkl\t0.5\n' > test.txt
$ cat test.txt
abc xyz 0.9
efg xyz 0.3
lmn opq 0.23
abc jkl 0.5

$ python
>>> from sframe import SFrame
>>> sf = SFrame.read_csv('test.txt', header=False, delimiter='\t', column_type_hints=[unicode, unicode, float])
[INFO] sframe.cython.cy_server: SFrame v2.1 started. Logging /tmp/sframe_server_1479718846.log
>>> sf
Columns:
    X1  str
    X2  str
    X3  float

Rows: 4

Data:
+-----+-----+------+
|  X1 |  X2 |  X3  |
+-----+-----+------+
| abc | xyz | 0.9  |
| efg | xyz | 0.3  |
| lmn | opq | 0.23 |
| abc | jkl | 0.5  |
+-----+-----+------+
[4 rows x 3 columns]

The goal is to achieve a different SFrame where there'll be one unique row made up of 'X1' and the columns are values from 'X2', i.e.:

+-----+-----+-----+------+
|  X1 | xyz | opq |  jkl |
+-----+-----+-----+------+
| abc | 0.9 | 0.0 |  0.5 |
+-----+-----+-----+------+
| efg | 0.3 | 0.0 |  0.0 |
+-----+-----+-----+------+
| lmn | 0.0 | 0.23|  0.0 |
+-----+-----+-----+------+

I've tried doing it without SFrame:

>>> import io
>>> with io.open('test.txt', 'r', encoding='utf8') as fin:
...     for line in fin:
...             if line.strip():
...                     s,t,p = line.strip().split('\t')
...                     matrix[(s,t)] = float(p)
... 
>>> matrix
{(u'abc', u'jkl'): 0.5, (u'abc', u'xyz'): 0.9, (u'lmn', u'opq'): 0.23, (u'efg', u'xyz'): 0.3}

>>> col1, col2 = zip(*matrix.keys())
>>> [[matrix.get((c1,c2), 0.0) for c2 in col2] for c1 in col1]
[[0.5, 0.9, 0.0, 0.9], [0.5, 0.9, 0.0, 0.9], [0.0, 0.0, 0.23, 0.0], [0.0, 0.3, 0.0, 0.3]]
>>> import numpy as np
>>> np.array([[matrix.get((c1,c2), 0.0) for c2 in col2] for c1 in col1])
array([[ 0.5 ,  0.9 ,  0.  ,  0.9 ],
       [ 0.5 ,  0.9 ,  0.  ,  0.9 ],
       [ 0.  ,  0.  ,  0.23,  0.  ],
       [ 0.  ,  0.3 ,  0.  ,  0.3 ]])
>>> SFrame(np.array([[matrix.get((c1,c2), 0.0) for c2 in col2] for c1 in col1]))
Columns:
    X1  array

Rows: 4

Data:
+-----------------------+
|           X1          |
+-----------------------+
|  [0.5, 0.9, 0.0, 0.9] |
|  [0.5, 0.9, 0.0, 0.9] |
| [0.0, 0.0, 0.23, 0.0] |
|  [0.0, 0.3, 0.0, 0.3] |
+-----------------------+
[4 rows x 1 columns]

But that still don't get me the desired SFrame. How should I convert the unique columns into SFrame headers with corresponding values? I.e. achieve:

+-----+-----+-----+------+
|  X1 | xyz | opq |  jkl |
+-----+-----+-----+------+
| abc | 0.9 | 0.0 |  0.5 |
+-----+-----+-----+------+
| efg | 0.3 | 0.0 |  0.0 |
+-----+-----+-----+------+
| lmn | 0.0 | 0.23|  0.0 |
+-----+-----+-----+------+

There must be a simpler way to do this. Imagine that the unique no. of column elements can go up to 1,000,000 and resulting SFrame might be of size 1,000,000 X 1,000,000 thus the need for SFrame or HDF like data structure and not a numpy array or native python list of lists.

Solution

  • What you want to do is really trivial in pandas, using either df.pivot(index='X1', columns='X2', values='X3') or by doing df.set_index(['X1','X2']).unstack('X2') (see at end of this post).

    It seems like neither exist in SFrame. (I could be wrong, never used SFrame until now but I couldn't find any evidence in the documentation).

    You need to use SFrame.unstack() and SFrame.unpack() in order to achieve the desired result.

    from sframe import SFrame
sf = SFrame.read_csv('test.txt', header=False, delimiter='\t', column_type_hints=[unicode, unicode, float])

    Fist, unstack:

    sf2 = sf.unstack(['X2', 'X3'], new_column_name='dict_counts')
sf2

X1      dict_counts
efg     {'xyz': 0.3}
lmn     {'opq': 0.23}
abc     {'jkl': 0.5, 'xyz': 0.9}

    Then unpack:

    out = sf2.unpack('dict_counts', column_name_prefix='')
out

X1      jkl     opq     xyz
efg     None    None    0.3
lmn     None    0.23    None
abc     0.5     None    0.9

    Finally, you can fillna in order to replace None with 0 if you'd like:

    for c in out.column_names():
    out = out.fillna(c, 0)
out


X1      jkl     opq     xyz
efg     0.0     0.0     0.3
lmn     0.0     0.23    0.0
abc     0.5     0.0     0.9

    Another crude way of doing it might be to convert to it a pandas DataFrame in order to pivot it, but this might not work if your dataset is too big:

    import pandas as pd
from sframe import SFrame
sf = SFrame.read_csv('test.txt', header=False, delimiter='\t', column_type_hints=[unicode, unicode, float])
sf = SFrame(data=sf.to_dataframe().pivot(index='X1', columns='X2', values='X3').fillna(0).reset_index())