5
\$\begingroup\$

I created a Pandas dataframe from a MongoDB query.

c = db.runs.find().limit(limit)
df = pd.DataFrame(list(c))

Right now one column of the dataframe corresponds to a document nested within the original MongoDB document, now typed as a dictionary. The dictionary is in the run_info column.

I would like to extract some of the dictionary's values to make new columns of the data frame. Is there a general way to do this? If not, what is the best brute force method specific to my data?

Here is my brute force approach, and it's horrendous. Even for a dataframe of only 200 rows, this is taking several minutes:

run_info = df['run_info']
# want to extra values from dictionary stored in column df['run_info']
# some values are within dictionaries nested within top dictionary
# these are 'author' and 'weather'
for i in range(len(df['run'])):
    g = run_info[i]
    if g.get('weather'):
        for name in weather_list:
            if g.get('weather').get(name):
                try:
                    df.set_value(i, name, g['weather'][name])
                except:
                    pass
    if g.get('author'):
        for name in author_list:
            if g.get('author').get(name):
                try:
                    df.set_value(i, name, g['author'][name])
                except:
                    pass
    for name in name_list:
        if g.get(name):
            try:
                if name is 'local_start_time':
                    df.set_value(i, name, g[name][0:19])
                else:
                    df.set_value(i, name, g[name])
            except:
                pass

I'd appreciate any and all suggestions for how to improve the speed here. Also I am using the try...except because once in a while the encoding on some funky character is causing a ValueError.

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

I suspect the try-except in the loops are the main cause of the slowness. It's not clear at which point you get ValueError due to "funky" data. It would be good to clean that up, get rid of the try-catch, and I think your solution will get noticeably faster.

Another small thing, that might not make a noticeable difference at all, you have some repeated lookups, like in this snippet:

if g.get('weather'):
    for name in weather_list:
        if g.get('weather').get(name):

Here, the g.get('weather') bit is executed twice. It would be better to save the result of the first call, and then reuse that. Apply this logic to other similar places. Although it might not make a practical difference, avoiding duplicated logic is a good practice.

\$\endgroup\$
  • \$\begingroup\$ You are right - I was torn about whether to keep the repeated lookup or not, but there's no real gain to keeping it except slightly cleaner code. The problem with getting rid of the try...except is that this is from an external API, I have no control over the formatting, and new problems are always popping up from incomplete unicode encoding. I will give it another go, but it's been tough for me. \$\endgroup\$ – sunny Jun 17 '15 at 22:22
  • \$\begingroup\$ I suggest to ask on stack overflow about the value error and how to resolve without resorting to try-except \$\endgroup\$ – janos Jun 18 '15 at 3:56
2
\$\begingroup\$

Efficient and elegant:

tf = pd.DataFrame([
        {'id': 1, 'nested': {'a': 1, 'b': 2} },
        {'id': 2, 'nested': {'a': 2, 'b': 4} },
        {'id': 3, 'nested': {'a': 3, 'b': 6} },
        {'id': 4, 'nested': {'a': 4}},
    ])

def unpack(df, column, fillna=None):
    ret = None
    if fillna is None:
        ret = pd.concat([df, pd.DataFrame((d for idx, d in df[column].iteritems()))], axis=1)
        del ret[column]
    else:
        ret = pd.concat([df, pd.DataFrame((d for idx, d in df[column].iteritems())).fillna(fillna)], axis=1)
        del ret[column]
    return ret

unpack(tf, 'nested', 0)

will yield

   id  a  b
0   1  1  2
1   2  2  4
2   3  3  6
3   4  4  0

and seems to be quite efficient

tf = pd.DataFrame([
enter code here
        {'id': i, 'nested': {'a': i, 'b': i*2} }
        for i in xrange(100000)
    ])
%timeit unpack(tf, 'nested') # gives 
10 loops, best of 3: 95 ms per loop

If you want to create a projection (select subset of the keys in the nested dict) you can use apply before unpack or a column projection on the dataframe created inside unpack.

The main advantages of the above solution are:

  • it is much more generic - does not depend on the keys in your nested document
  • it is efficient - uses (presumably optimized) pandas methods where-ever possible and generators/iterators
  • handles keys that do not exist only in some nested documents and lets you specify the way they should be handled (fillna value or NaN)
  • can be converted to a one-liner for the sake of brevity
  • does not reinvent anything
  • uses naming consistent with other libraries (dato (graphlab create), SFrame.unpack method)
\$\endgroup\$
1
\$\begingroup\$

I'll suggest a small modification to the function by @JohnnyM to account for situations where the nested column name (in this case 'nested') is identical to one of the subordinate levels (e.g. 'a'):

def unpack(df, column, fillna=None):
    ret = None
    if fillna is None:
        tmp = pd.DataFrame((d for idx, d in df[column].iteritems()))
        ret = pd.concat([df.drop(column,axis=1), tmp], axis=1)
    else:
        tmp = pd.DataFrame((d for idx, d in 
        df[column].iteritems())).fillna(fillna)
        ret = pd.concat([df.drop(column,axis=1), tmp], axis=1)
    return ret
\$\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.