CODEWITHSUNDEEP
pythontwitterpandastwython
blahblahblah
blahblahblah

Reputation: 2437

Manipulate Pandas DataFrame containing dictionaries from Twitter API

I am working on a script that uses the Twitter API to pull recent statuses from a list of users. I am able to retrieve the data using the API however upon converting it to a DataFrame, I get columns that are storing dictionaries. I want to spread the indexes of those dictionaries to additional columns. Ultimately, I am trying to save all that information to a CSV.

Here is the code:

import twython
import time
import pandas as pd
import numpy as np

app_key = ''
app_secret = ''
oauth_token = ''
oauth_token_secret = ''

twitter = twython.Twython(app_key, app_secret, oauth_token, oauth_token_secret)

screen_names = ['@', '@'] #enter screen names of interest

tweets = []

for screen_name in screen_names:
    tweets.extend(twitter.get_user_timeline(screen_name=screen_name, count=200))
    time.sleep(5)

df = pd.DataFrame(tweets)

which returns a DataFrame (400,25). df[[2,3,5]] returns the following:

     created_at                       entities                                         favorite_count
0    Thu Jun 19 13:14:39 +0000 2014  {u'symbols': [], u'user_mentions': [], u'hasht...       0
1    Thu Jun 19 11:53:51 +0000 2014  {u'symbols': [], u'user_mentions': [{u'id': 18...       0
2    Thu Jun 19 11:53:25 +0000 2014  {u'symbols': [], u'user_mentions': [], u'hasht...       3
3    Thu Jun 19 11:49:34 +0000 2014  {u'symbols': [], u'user_mentions': [], u'hasht...       0
4    Thu Jun 19 11:01:31 +0000 2014  {u'symbols': [], u'user_mentions': [{u'id': 18...       0

How do I split up the entities column across additional columns? For example, I'd like symbols, user_mentions, hastags, etc. to become additional columns in df.

Any help is greatly appreciated.

Upvotes: 0

Views: 1480

Answers (2)

R. Max
R. Max

Reputation: 6710

I use this helper function to convert a dict of nested values (likely from an API) to a dict without nested values.

def flatten(d):
    for key in d.keys():
        if isinstance(d[key], list):
            value = d.pop(key)
            for i, v in enumerate(value):
                d.update(flatten({'%s__%s' % (key, i): v}))
        elif isinstance(d[key], dict):
            value = d.pop(key)
            d.update([('%s__%s' % (key, sub), v) for (sub, v) in flatten(value).items()])
    return d

Here is a example of what it does:

In [2]: d = {'user': 'foo', 'data': {'choices': [0,1,2], 'type': 'x1'}}

In [3]: flatten(d)
Out[3]: 
{'data__choices__0': 0,
 'data__choices__1': 1,
 'data__choices__2': 2,
 'data__type': 'x1',
 'user': 'foo'}

In your example, you will need to do:

df = pd.DataFrame([flatten(t) for t in tweets])

Upvotes: 2

blahblahblah
blahblahblah

Reputation: 2437

The following accomplishes what I asked in my question:

df_entities = pd.DataFrame(df['t_entities'].tolist())

df = df.join([df_entities, df_user])

Upvotes: 3

Related Questions