CountTokenizing a field, turning into columns
<p>I'm working with data that look something like this:</p>

<pre><code>ID      PATH         GROUP  
11937   MM-YT-UJ-OO  GT  
11938   YT-RY-LM     TQ  
11939   XX-XX-OT     DX  
</code></pre>

<p>I'd like to tokenize the PATH column into n-grams and then one-hot encode those into their own columns so I'd end up with something like:</p>

<pre><code>ID     GROUP   MM  YT  UJ  OO  RY  LM  XX  OT  MM-YT  YT-UH ...  
11937  GT      1   1   1   1   0   0   0   0   1      1
</code></pre>

<p>I could also use counted tokens rather than one-hot, so 11939 would have a 2 in the XX column instead of a 1, but I can work with either.</p>

<p>I can tokenize the column quite easily with scikitlearn CountVectorizer, but then I have to cbind the <code>ID</code> and <code>GROUP</code> fields.  Is there a standard way to do this or a best practice that anyone has discovered?</p>

<p>A solution:</p>

<pre><code>df.set_index(['ID', 'GROUP'], inplace=True)
pd.get_dummies(df.PATH.str.split('-', expand=True).stack())\
              .groupby(level=[0,1]).sum().reset_index()
</code></pre>

<hr>

<p>Isolate the ID and GROUP columns as index. Then convert the string to cell items</p>

<pre><code>df.PATH.str.split('-', expand=True)
Out[37]: 
              0   1   2     3
ID    GROUP                  
11937 GT     MM  YT  UJ    OO
11938 TQ     YT  RY  LM  None
11939 DX     XX  XX  OT  None
</code></pre>

<p>Get them into a single column of data</p>

<pre><code>df.PATH.str.split('-', expand=True).stack()
Out[38]: 
ID     GROUP   
11937  GT     0    MM
              1    YT
              2    UJ
              3    OO
11938  TQ     0    YT
              1    RY
              2    LM
11939  DX     0    XX
              1    XX
              2    OT
</code></pre>

<p><code>get_dummies</code> bring the counter as columns spread accross rows</p>

<pre><code>pd.get_dummies(df.PATH.str.split('-', expand=True).stack())
Out[39]: 
               LM  MM  OO  OT  RY  UJ  XX  YT
ID    GROUP                                  
11937 GT    0   0   1   0   0   0   0   0   0
            1   0   0   0   0   0   0   0   1
            2   0   0   0   0   0   1   0   0
            3   0   0   1   0   0   0   0   0
11938 TQ    0   0   0   0   0   0   0   0   1
            1   0   0   0   0   1   0   0   0
            2   1   0   0   0   0   0   0   0
11939 DX    0   0   0   0   0   0   0   1   0
            1   0   0   0   0   0   0   1   0
            2   0   0   0   1   0   0   0   0
</code></pre>

<p>Group by the data per ID, GROUP (levels 0 and 1 in the index) to sum up the rows together and have one line per tuple. And finally reset the index to get ID and GROUP column back as regular columns.</p>

python pandas numpy scikit-learn