Pandas "columns of child subrecords (as lists of dicts)" to more traditional "SQL Join" output style

user_2321195 0
Viewed 105

If I have a DataFrame made by running the following code:

listDics = [
        {
                'PersonId':'1','First':'A','Last':'B',
                'SomeChildren':[{'Col1':'x','Col2':'y'},{'Col1':'xx','Col2':'yy'}],
                'MoreChildren':[{'MC1':'blahX','MC2':'blahY'},{'MC1':'blahXX','MC2':'blahYY'},{'MC1':'blahXXX','MC2':'blahYYY'}]
        },
        {
                'PersonId':'2','First':'C','Last':'D',
                'SomeChildren':[{'Col1':'m','Col2':'n'},{'Col1':'mm','Col2':'nn'},{'Col1':'mmm','Col2':'nnn'}],
                'MoreChildren':[{'MC1':'blahM','MC2':'blahN'}]
        }
        ]
import pandas
df = pandas.DataFrame(listDics)

Raw, it looks like this:

PersonId First Last                                       SomeChildren                                       MoreChildren
       1     A    B  [{'Col1': 'x', 'Col2': 'y'}, {'Col1': 'xx', 'C...  [{'MC1': 'blahX', 'MC2': 'blahY'}, {'MC1': 'bl...
       2     C    D  [{'Col1': 'm', 'Col2': 'n'}, {'Col1': 'mm', 'C...                 [{'MC1': 'blahM', 'MC2': 'blahN'}]

I want output that looks like this:

PersonId    First   Last    SomeChildren.Col1   SomeChildren.Col2   MoreChildren.MC1    MoreChildren.MC2
1           A       B       x                   y                   blahX               blahY
1           A       B       x                   y                   blahXX              blahYY
1           A       B       x                   y                   blahXXX             blahYYY
1           A       B       xx                  yy                  blahX               blahY
1           A       B       xx                  yy                  blahXX              blahYY
1           A       B       xx                  yy                  blahXXX             blahYYY
2           C       D       m                   n                   blahM               blahN
2           C       D       mm                  nn                  blahM               blahN
2           C       D       mmm                 nnn                 blahM               blahN

As well as output that looks like this:

FK  Col1    Col2
1   x       y
1   xx      yy
2   m       n
2   mm      nn
2   mmm     nnn

What is the most Pythonic way to produce those two DataFrames?

Although I can think of a lot of kludgey ways to do it (like doing repeated imports to Pandas of various fragments of the original list of dicts, merges of resulting component sub-data-frames, etc.), I'm struggling to see the "right" way to do produce my desired output.

Note: it's safe to presume that the "lists of dicts" inside outer cells will always share a set of keys from dict to dict. The data is a JSON dump from a relational database product's API, so all "lists of dicts" are rows of a table.

pandas python
1 Answers

I'm not sure whether this is too kludgey, but here's something:

import pandas as pd
from functools import reduce

df = pd.DataFrame(listDics)

cols = ['SomeChildren', 'MoreChildren']

def f(s):
    out = pd.concat([pd.DataFrame(x) for x in df[s]], keys = df.index)
    out = out.add_prefix(s + '.')
    out.index = out.index.get_level_values(0)

    return(out)

addl_dfs = list(map(f, cols))
df = df.drop(cols, axis = 1)

df_list = [df] + addl_dfs

df = reduce(lambda l, r: pd.merge(l, r, left_index = True, right_index = True), df_list)

One caveat here is that there's still merging, which you mention in your question, and the 'FK' here is the Index rather than PersonId. One benefit of this is that it should be flexible in the event of additional nested 'columns' like SomeChildren and MoreChildren.

In the smaller dataframe posted at the end of your question, the column names didn't have the prefixes. To remove them, you could use this (NB that it will not work if column names have periods to begin with):

for d in addl_dfs:
    d.columns = d.columns.str.split('.').str.get(1)
user_55571770
Related