Update Pandas Cells based on Column Values and Other Columns
<p>I am looking to update many columns based on the values in one column; this is easy with a loop but takes far too long for my application when there are many columns and many rows. What is the most elegant way to get the desired counts for each letter?</p>

<p>Desired Output:</p>

<pre><code>   Things         count_A     count_B    count_C     count_D
['A','B','C']         1            1         1          0
['A','A','A']         3            0         0          0
['B','A']             1            1         0          0
['D','D']             0            0         0          2
</code></pre>

<p>The most elegant is definitely the CountVectorizer from sklearn. </p>

<p>I'll show you how it works first, then I'll do everything in one line, so you can see how elegant it is. </p>

<h3>First, we'll do it step by step:</h3>

<p>let's create some data</p>

<pre><code>raw = ['ABC', 'AAA', 'BA', 'DD']

things = [list(s) for s in raw]
</code></pre>

<p>Then read in some packages and initialize count vectorizer</p>

<pre><code>from sklearn.feature_extraction.text import CountVectorizer
import pandas as pd

cv = CountVectorizer(tokenizer=lambda doc: doc, lowercase=False)
</code></pre>

<p>Next we generate a matrix of counts</p>

<pre><code>matrix = cv.fit_transform(things)

names = ["count_"+n for n in cv.get_feature_names()]
</code></pre>

<p>And save as a data frame</p>

<pre><code>df = pd.DataFrame(data=matrix.toarray(), columns=names, index=raw)
</code></pre>

<p>Generating a data frame like this: </p>

<pre><code>    count_A count_B count_C count_D
ABC 1   1   1   0
AAA 3   0   0   0
BA  1   1   0   0
DD  0   0   0   2
</code></pre>

<h3>Elegant version:</h3>

<p>Everything above in one line</p>

<pre><code>df = pd.DataFrame(data=cv.fit_transform(things).toarray(), columns=["count_"+n for n in cv.get_feature_names()], index=raw)
</code></pre>

<h3>Timing:</h3>

<p>You mentioned that you're working with a rather large dataset, so I used the %%timeit function to give a time estimate. </p>

<p>Previous response by @piRSquared (which otherwise looks very good!) </p>

<pre><code>pd.concat([s, s.apply(lambda x: pd.Series(x).value_counts()).fillna(0)], axis=1)
</code></pre>

<p><code>100 loops, best of 3: 3.27 ms per loop</code></p>

<p>My answer:</p>

<pre><code>pd.DataFrame(data=cv.fit_transform(things).toarray(), columns=["count_"+n for n in cv.get_feature_names()], index=raw)
</code></pre>

<p><code>1000 loops, best of 3: 1.08 ms per loop</code></p>

<p>According to my testing, <em>CountVectorizer</em> is about 3x faster. </p>

python pandas apply