Extracting contents of dictionary contained in Pandas dataframe to make new dataframe columns

Question

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.

Answer 1

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.

Answer 2

You just need to extract the list of dictionaries and then create a new dataframe from this list and then finally merge dataframes.

run_info = list(df['run_info'])    # extract the list of dictionaries
df_runinfo = pd.DataFrame(run_info).fillna(0).astype(int) .   # create a new dataframe
df = pd.concat([df, df_runinfo], axis=1)    # merge with original dataframe

or simply:

df = pd.concat([df, pd.DataFrame(list(df['run_info'])).fillna(0).astype(int)], axis=1)

Answer 3

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)

Answer 4

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