list of lists to pandas dataframe

Question

I'm trying to parse a list of nested lists to a pandas dataframe.

This is a sample of the list:

>>>result[1] { "account_currency": "BRL", "account_id": "1600343406676896", "account_name": "aaa", "buying_type": "AUCTION", "campaign_id": "aaa", "campaign_name": "aaaL", "canvas_avg_view_percent": "0", "canvas_avg_view_time": "0", "clicks": "1", "cost_per_total_action": "8.15", "cpm": "60.820896", "cpp": "61.278195", "date_start": "2017-10-08", "date_stop": "2017-10-15", "device_platform": "desktop", "frequency": "1.007519", "impression_device": "desktop", "impressions": "134", "inline_link_clicks": "1", "inline_post_engagement": "1", "objective": "CONVERSIONS", "outbound_clicks": [ { "action_type": "outbound_click", "value": "1" } ], "platform_position": "feed", "publisher_platform": "facebook", "reach": "133", "social_clicks": "1", "social_impressions": "91", "social_reach": "90", "spend": "8.15", "total_action_value": "0", "total_actions": "1", "total_unique_actions": "1", "unique_actions": [ { "action_type": "landing_page_view", "value": "1" }, { "action_type": "link_click", "value": "1" }, { "action_type": "page_engagement", "value": "1" }, { "action_type": "post_engagement", "value": "1" } ], "unique_clicks": "1", "unique_inline_link_clicks": "1", "unique_outbound_clicks": [ { "action_type": "outbound_click", "value": "1" } ], "unique_social_clicks": "1" }

When I transform it to a pandas dataframe, I get:

>>>df = pd.DataFrame(result) >>>df .... unique_actions \ NaN [{u'value': u'1', u'action_type': u'landing_pa... NaN [{u'value': u'2', u'action_type': u'landing_pa... [{u'value': u'4', u'action_type': u'landing_pa... NaN

Unique actions and someother filter are not normalized.

How can I normalize it to the same granularity?

what does "normalize to the same granularity" mean? what do you actually want your result to look like? — Paul H
– Paul H, Commented Oct 15, 2017 at 4:09
@Parfait got it. How can I turn that in a transposed column? — Filipe Ferminiano
– Filipe Ferminiano, Commented Oct 15, 2017 at 4:42
what does a transposed column look like? You need to type out your desired result into the question — Paul H
– Paul H, Commented Oct 15, 2017 at 6:04

John Zwinck · Accepted Answer · 2017-10-15 04:44:17Z

1

You can use json_normalize, something like this:

pd.io.json.json_normalize(df.unique_actions)

answered Oct 15, 2017 at 4:44

John Zwinck

252k44 gold badges346 silver badges459 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Filipe Ferminiano Over a year ago

I get this error: AttributeError: 'float' object has no attribute 'itervalues'

Parfait · Accepted Answer · 2017-10-15 05:08:37Z

Consider json_normalize passing in your nested lists as the record_path and all other indicators as meta. However, because you have multiple nested lists, json carries information for three dataframes:

from pandas.io.json import json_normalize merge_fields = ['account_currency', 'account_id', 'account_name', 'buying_type', 'campaign_id', 'campaign_name', 'canvas_avg_view_percent', 'canvas_avg_view_time', 'clicks', 'cost_per_total_action', 'cpm', 'cpp', 'date_start', 'date_stop', 'device_platform', 'frequency', 'impression_device', 'impressions', 'inline_link_clicks', 'inline_post_engagement', 'objective', 'platform_position', 'publisher_platform', 'reach', 'social_clicks', 'social_impressions', 'social_reach', 'spend', 'total_action_value', 'total_actions', 'total_unique_actions', 'unique_clicks', 'unique_inline_link_clicks', 'unique_social_clicks'] unique_actions_df = json_normalize(result[1], record_path='unique_actions', meta=merge_fields) outbound_clicks_df = json_normalize(result[1], record_path='outbound_clicks', meta=merge_fields) unique_outbound_clicks_df = json_normalize(result[1], record_path='unique_outbound_clicks', meta=merge_fields)

Are you passing in exactly what you are posting, result[1] or another item? If structure is same across list items, you need to loop through result.

Collectives™ on Stack Overflow

list of lists to pandas dataframe

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related