Read data from a map object within JSON into a Pandas Dataframe

user_711347 0
Viewed 431

I am trying to access data from a JSON file into a pandas dataframe and seem to be stuck on how to retrieve a data within a map of the JSON.

I want to retrieve the followers_count entity within the user object of this json into a dataframe.

JSON File (sample record) below:

{"created_at": "Tue Aug 01 16:23:56 +0000 2017", "id": 892420643555336193, "retweet_count": 12345, "favorite_count": 23456, "user": {"id": 4196983835, "followers_count": 3200889, "friends_count": 104}}

here is what I have in terms of code (doesnt work as I dont know how to fetch the followers_count within the user object :

        tweet_data_df = pd.read_json('tweet-json.txt', lines=True)
        #Doesnt work
        #tweet_data_df = tweet_data_df[['id', 'favorite_count', 'retweet_count', 'created_at', 'user''followers_count']]
        #works but not enough for me
        tweet_data_df = tweet_data_df[['id', 'favorite_count', 'retweet_count', 'created_at']]
        tweet_data_df.head(5)

Appreciate your help !

pandas dataframe python json
1 Answers

If json object (dictionary) has a depth = 2, (i.e. just 2 nested dictionaries) you can use .apply(pd.Series).

{"key": {"key2":{val1, val2}} # depth = 2
{"key": {"key2":{val1, "key3":{val2}} # depth > 2, depth = 3

pd.DataFrame(dic).apply(pd.Series).reset_index(drop = True)

Otherwise depth > 2 you can iterate through, its keys recursively

def shrink_depth(dic, output_dict: dict, pkey= None):
    if isinstance(dic, dict):
        for key in dic:
            if key not in output_dict.keys():
                output_dict[key] = []
            
            shrink_depth(dic[key], output_dict, key) # call
    
    elif isinstance(dic, (list, set)):
        for val in dic:
            output_dict[pkey].append(val)
    else:
        output_dict[pkey].append(dic)

# update: Add nested dictionaries to the (id) key
dic = {"created_at": "Tue Aug 01 16:23:56 +0000 2017", "id": 892420643555336193, "retweet_count": 12345, "favorite_count": 23456, 
               "user": {"id": {4196983835: 43424}, "followers_count": 3200889, "friends_count": 104}}

output = {}

shrink_depth(dic, output)

output

{'created_at': ['Tue Aug 01 16:23:56 +0000 2017'],
 'id': [892420643555336193],
 'retweet_count': [12345],
 'favorite_count': [23456],
 'user': [],
 4196983835: [43424],
 'followers_count': [3200889],
 'friends_count': [104]}
user_112731240
Related