I have a data frame with following structure:

Index(['first_post_date', 'followers_count', 'friends_count',
       'last_post_date','min_retweet', 'retweet_count', 'screen_name',
       'tweet_count',  'tweet_with_max_retweet', 'tweets', 'uid'],

Inside the tweets series, each cell is another data frame containing all the tweets of an user.

Index(['created_at', 'id', 'retweet_count', 'text'], dtype='object')

I want to convert this data frame to a multi-index frame, essentially by breaking the cell containing tweets. One index will be the uid, and another will be the id inside tweet.

How can I do that?

link to sample data


1 Answer 1


One way to pull the embedded dataframe up into the main dataframe and build a multi index is like:


def expand_tweets(tweets_df):
    tweets = []
    for uid, sub_df in tweets_df.set_index('uid').tweets.iteritems():
        sub_df['uid'] = uid
    return pd.concat(tweets).merge(
        tweets_df.drop('tweets', axis=1).reset_index(),
        how='outer', on='uid').set_index(['uid', 'id'])


  1. Pull all of the tweet dataframes out of main dataframe using uid as an index, and concat() them together with their uid.

  2. Then merge the main dataframe into the concatenated tweets dataframe.

  3. Set the desired index.

Test Code:

import json
import pandas as pd
with open('test.json') as f:
    df = pd.DataFrame(json.load(f))
df['tweets'] = df.tweets.apply(lambda x: pd.DataFrame(x))



uid         id                
1153859336  655060275025047552    Article on my new Haunted Stevenage book Paran...
            653912439940120576    Big thank you to @bobfmuk for interviewing me ...
            643709869908996096    Another interesting non-toadstool tweet today,...
            547107275681579008    @sisax67 Thanks, Simon. All the best to you &a...
            546693940024733696    Paul Adams @SkySportsDarts The Wanderer from W...
Name: text, dtype: object

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.