Handling of nested JSON records · Issue #1067 · pandas-dev/pandas (original) (raw)

Is there a simple way of grabbing nested keys when constructing a Pandas Dataframe from JSON. Using the example JSON from below, how would I build a Dataframe that uses this column_header = ['id_str', 'text', 'user.screen_name'], (i.e. how do I get the 'screen_name' from the 'user' key without flattening the JSON).

Thanks,

Vishal

{   u'_id': ObjectId('4f8b95e8a504d022e2000000'),
    u'contributors': None,
    u'coordinates': None,
    u'created_at': u'Mon Apr 16 03:45:44 +0000 2012',
    u'entities': {   u'hashtags': [],
                     u'urls': [   {   u'display_url': u'stks.co/3M4X',
                                      u'expanded_url': u'http://stks.co/3M4X',
                                      u'indices': [72, 92],
                                      u'url': u'http://t.co/mGdTYusF'}],
                     u'user_mentions': []},
    u'favorited': False,
    u'geo': None,
    u'id': 191734090783916032L,
    u'id_str': u'191734090783916032',
    u'in_reply_to_screen_name': None,
    u'in_reply_to_status_id': None,
    u'in_reply_to_status_id_str': None,
    u'in_reply_to_user_id': None,
    u'in_reply_to_user_id_str': None,
    u'place': None,
    u'possibly_sensitive': False,
    u'possibly_sensitive_editable': True,
    u'processed_metadata': {   u'created_date': datetime.datetime(2012, 4, 16, 3, 45, 44, tzinfo=<bson.tz_util.FixedOffset object at 0x104d63790>),
                               u'search_queries': [   u'$AAPL',
                                                      u'$MSFT',
                                                      u'$GOOG'],
                               u'source': u'Twitter Streaming API'},
    u'retweet_count': 0,
    u'retweeted': False,
    u'source': u'<a href="http://stocktwits.com" rel="nofollow">StockTwits Web</a>',
    u'text': u'Interesting infographic on the internet and evolution of social media \u2794 http://t.co/mGdTYusF <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>F</mi><mi>B</mi></mrow><annotation encoding="application/x-tex">FB </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6833em;"></span><span class="mord mathnormal" style="margin-right:0.05017em;">FB</span></span></span></span>GOOG <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>T</mi><mi>W</mi><mi>I</mi><mi>T</mi></mrow><annotation encoding="application/x-tex">TWIT </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6833em;"></span><span class="mord mathnormal" style="margin-right:0.13889em;">T</span><span class="mord mathnormal" style="margin-right:0.13889em;">W</span><span class="mord mathnormal" style="margin-right:0.07847em;">I</span><span class="mord mathnormal" style="margin-right:0.13889em;">T</span></span></span></span>LNKD $AOL',
    u'truncated': False,
    u'user': {   u'_id': u'speculatethemkt',
                 u'contributors_enabled': False,
                 u'created_at': u'Tue Nov 30 02:28:20 +0000 2010',
                 u'default_profile': False,
                 u'default_profile_image': False,
                 u'description': u"I'm a 22-year old full-time forex trader living the location independent lifestyle. Author of The Trading Elite \u2794 http://amzn.to/I0nacY",
                 u'favourites_count': 1,
                 u'follow_request_sent': None,
                 u'followers_count': 19658,
                 u'following': None,
                 u'friends_count': 596,
                 u'geo_enabled': False,
                 u'id': 221226895,
                 u'id_str': u'221226895',
                 u'is_translator': False,
                 u'lang': u'en',
                 u'listed_count': 6,
                 u'location': u'Portland, OR',
                 u'name': u'Jared M.',
                 u'notifications': None,
                 u'processed_metadata': {   u'created_date': datetime.datetime(2012, 4, 16, 3, 45, 44, tzinfo=<bson.tz_util.FixedOffset object at 0x104d63790>),
                                            u'search_queries': [   u'$AAPL',
                                                                   u'$MSFT',
                                                                   u'$GOOG'],
                                            u'source': u'Twitter Streaming API'},
                 u'profile_background_color': u'4f4f4f',
                 u'profile_background_image_url': u'http://a0.twimg.com/profile_background_images/438353849/TWITTER-BG.jpg',
                 u'profile_background_image_url_https': u'https://si0.twimg.com/profile_background_images/438353849/TWITTER-BG.jpg',
                 u'profile_background_tile': False,
                 u'profile_image_url': u'http://a0.twimg.com/profile_images/2039368182/twitpic1_normal.png',
                 u'profile_image_url_https': u'https://si0.twimg.com/profile_images/2039368182/twitpic1_normal.png',
                 u'profile_link_color': u'bd0000',
                 u'profile_sidebar_border_color': u'eeeeee',
                 u'profile_sidebar_fill_color': u'efefef',
                 u'profile_text_color': u'333333',
                 u'profile_use_background_image': True,
                 u'protected': False,
                 u'screen_name': u'speculatethemkt',
                 u'show_all_inline_media': True,
                 u'statuses_count': 492,
                 u'time_zone': u'Pacific Time (US & Canada)',
                 u'url': u'http://www.speculatethemarkets.com',
                 u'utc_offset': -28800,
                 u'verified': False}}