How to parse a nested column in a df column?

user_3604009 0
Viewed 142

Is there a smart pythonic way to parse a nested column in a pandas dataframe like this one to 3 different columns? So for example the column could look like this:

col1
[{'name': 'amount', 'value': 1}, {'name': 'frequency', 'value': 2}, {'name': 'freq_unit', 'value': 'month'}]
[{'name': 'amount', 'value': 3}, {'name': 'frequency', 'value': 1}, {'name': 'freq_unit', 'value': 'month'}]

And the expected result should be these 3 columns:

amount  frequency   freq_unit
1       2           month
3       1           month

That's just level 1. I have the level 2: What if the elements in the list still have the same names (amount, frequency and freq_unit) but the order could change? Could the code in the answer deal with this?

col1
[{'name': 'amount', 'value': 1}, {'name': 'frequency', 'value': 2}, {'name': 'freq_unit', 'value': 'month'}]
[{'name': 'amount', 'value': 3}, {'name': 'freq_unit', 'value': 'month'}, {'name': 'frequency', 'value': 1}]

Code for reproduce the data. Really look forward to see how the community would solve this. Thank you

data = {'col1':[[{'name': 'amount', 'value': 1}, {'name': 'frequency', 'value': 2}, {'name': 'freq_unit', 'value': 'month'}],
                [{'name': 'amount', 'value': 3}, {'name': 'frequency', 'value': 1}, {'name': 'freq_unit', 'value': 'month'}]]}
df = pd.DataFrame(data)
pandas dataframe nested parsing
2 Answers

A combination of list comprehension, itertools.chain, and collections.defaultdict could help out here:

from itertools import chain
from collections import defaultdict

data = defaultdict(list)

phase1 = [[(data["name"], data["value"]) 
           for data in entry] 
           for entry in df.col1
          ]

phase1 = chain.from_iterable(phase1)

for key, value in phase1:
    data[key].append(value)

pd.DataFrame(data)

 
    amount  frequency   freq_unit
0      1       2        month
1      3       1        month

The above is verbose: @piRSquared's comment is much simpler, with a list comprehension:

pd.DataFrame([{x["name"]: x["value"] for x in lst} for lst in df.col1])

Another idea, but very unnecessary, is to use a list comprehension, combined with Pandas' string methods:

outcome = [(df.col1.str[num].str["value"]
              .rename(df.col1.str[num].str["name"][0])
            )
           for num in range(df.col1.str.len()[0])
           ]

pd.concat(outcome, axis = 'columns')

@piRsquared's solution is the simplest, in my opinion.

user_71757130

You can write a function that will parse each cell in your Series and return a properly formatted Series and use apply to tuck the iteration away:

>>> def custom_parser(record):
...     clean_record = {rec["name"]: rec["value"] for rec in record}
...     return pd.Series(clean_record)

>>> df["col1"].apply(custom_parser)
   amount  frequency freq_unit
0       1          2     month
1       3          1     month
user_142784480
Related