Making seperate columns from dictionary items in the same df in Python

user_13604719 0
Viewed 57

I have a huge dataframe (9530232x19) of twitter data. The first column includes a dictionary. I want to make seperate columns from that dictionary items in the same df. Also I have a dictionary in 'entities' column which I want to make a similar seperation. sample data I want to add the metrics as four new columns such as 'rtcount', 'reply_count', 'like_count', and 'quote_count', and entities['htype'] as a new column on the right of my existing df, without creating any more dataframes because this big df uses almost all of my 16 GB RAM and crashes occasionally. I know using a for loop for this big df is not an efficient way but I couldn't figure out how to do it otherwise.
Any help is very much appreciated.

htypedf=[]
t=[]
for i in range(0,len(d)):
    if i%100==0:
        print(i)
    htype=[]
    hasht=[]
    t=d[i:i+1]
    metrics=pd.Series(t['public_metrics'][0]).to_frame().T
    try:
        htype=list(map(lambda x : x['type'], t['entities'][0]['annotations']))
    except:
        htype=('NaN')

    d.iloc[i] = pd.concat([t, metrics, pd.DataFrame({'htype': [htype]})],axis=1)
pandas add dictionary python
1 Answers

Instead of your for loop, try this:

d = pd.concat([t.drop['public_metrics'], t['public_metrics'].apply(pd.Series)], axis=1)

Similar concept could be applied for fetching the htype, but handling that will depend on how you want the data retained. If you only want htype from the entities column, you could likely give the following a shot:

d = pd.concat([t.drop['entities'], t['entities'].apply(pd.Series)['htype']], axis=1)

To retain the entities column in addition to the new htype column, you should be able to use the following code instead (simply removing the drop function):

d = pd.concat([t, t['entities'].apply(pd.Series)['htype']], axis=1)

Let me know how this works for you!

New codeblock:

def fetch_htype(row):
    entities_dict = row['entities']
    if np.isnan(entities_dict):
        return pd.Series(data = '', index = ['htype'])
    else:
        return pd.Series(data = entities_dict['htype'], index = ['htype'])

d = pd.concat([d, t.apply(fetch_htype)], axis=1)
user_137587100
Related