7
\$\begingroup\$

I have a data set with close to 6 million rows of user input. Specifically, users were supposed to type in their email addresses, but because there was not pattern validation put in place we have a few months worth of interesting input.

I've come up with a script that counts every character, then combines it that so I can see the distribution of all characters. This enables me to do further analysis and get a sense of the most common mistakes so I can begin to clean the data. My question is: how would you optimize the following for speed?

import pandas as pd
import numpy as np
from pandas import Series, DataFrame
from collections import Counter

df = pd.DataFrame({'input': ['Captain Jean-Luc Picard <picard@starfleet.com>','deanna.troi@starfleet.com','geordi @starfleet.com','data@starfleet.com','rik#er@starfleet.com'],
'metric1': np.random.randn(5).cumsum(),
'metric2': np.random.randn(5)})

l = []
for i in range(len(df.index.values)):
    l.append(dict(Counter(df.ix[i,'input'])))
dist = pd.DataFrame(l).fillna(0)
dist = dist.sum(axis=0)
print(dist)

I've run this over ~1/3 of my dataset, and it takes a while; it's still tolerable, I'm just curious if anyone could make it faster.

\$\endgroup\$
2
\$\begingroup\$

Since you are using Counter already, it should be faster to do the whole job with it:

c = Counter()
for i in range(len(df.index.values)):
    c.update(df.ix[i,'input'])

for k, v in c.items():
    print(k, v)
\$\endgroup\$
2
\$\begingroup\$

This is the shortest possibility:

from collections import Counter

dist = Counter(''.join(df.input.tolist()))

which results in

Counter({'a': 14, 'e': 14, 't': 13, 'r': 11, 'c': 8, 'o': 7, '.': 6, 'i': 6, '@': 5, 'd': 5, 'f': 5, 'm': 5, 'l': 5, 's': 5, ' ': 4, 'n': 4, 'p': 2, '#': 1, '-': 1, '<': 1, '>': 1, 'C': 1, 'J': 1, 'L': 1, 'P': 1, 'g': 1, 'k': 1, 'u': 1})

What ''.join(df.input.tolist()) does:

>>> ''.join(df.input.tolist())
'Captain Jean-Luc Picard <picard@starfleet.com>deanna.troi@starfleet.comgeordi @starfleet.comdata@starfleet.comrik#er@starfleet.com'

It joins all the strings in our list here. This one string can now be handed over to Counter.

dist is now a Counter object, which can be used just like a regular dictionary. However you can convert it just by dict(dist).

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.