Faster solution to get values in list of dictionaries and add row in pandas dataframe for observations with multiple dictionaries

user_14386187 0
Viewed 54

I have a data frame that I scraped using Tweepy looks like this:

index  user  entities.user.mentions  
1      w     [{'screen_name': 'a','name': '1'},{'screen_name': 'b','name': '2',}]
2      x     [{'screen_name': 'c','name': '3'}]
3      y     [{'screen_name': 'e','name': '5'},{'screen_name': 'f','name': '6',}]
4      z     [{'screen_name': 'g','name': '7'}]

I want to create a column that has the value of the key 'screen_name'. In cases where there are multiple dictionaries in a row in 'entities.user.mentions', I would like to create another row with the same 'user' next the corresponding 'screenname'. This is what I'd like the new data frame to look like:

index  user  mention  
1      w     a
2      w     b
3      x     c
4      y     e
5      y     f
6      z     g

I was able to do it with the following for loops, but it is taking much longer (5 minutes) than I want to run:

for x in range(len(df['entities.user.mentions'])):
    d1 = {}
    for y in range(len(df['emu'][x])):
        d1[y] = df['entities.user.mentions'][x][y]['screen_name']
        df['entities.user.mentions'][x] = d1

Then:

final_df = pd.DataFrame()
for x in range(len(df)):
    for y in range(len(df['emu_sn'][x])):
        a = df.iloc[x,]
        a['entities.user.mentions'] = df['entities.user.mentions'][x][y]
        final_df = final_df.append(a)

Would appreciate another solution that works faster than the above. Thank you in advance.

pandas networkx python
2 Answers

try explode and assign

df_final = (df.explode('entities.user.mentions')
              .assign(mention=lambda x: x['entities.user.mentions'].str['screen_name'])
              .drop('entities.user.mentions',1))

Out[141]:
   index user mention
0      1    w       a
0      1    w       b
1      2    x       c
2      3    y       e
2      3    y       f
3      4    z       g
user_101892140

Using pandas.DataFrame.explode, you can do this in 4 lines.

new_df = df.explode('entities.user.mentions')
new_df['mention'] = new_df['entities.user.mentions'].apply(lambda x: x.get('screen_name'))
new_df.drop('entities.user.mentions', axis=1, inplace=True)
print (new_df)

Your output will be as follows:

  user mention
0    w       a
0    w       b
1    x       c
2    y       e
2    y       f
3    z       g

If you want to keep your entities.user.mentions, then dont drop the column.

user_138739800
Related