Extract values belonging to a specific key from a column of JSON in pandas

user_10540860 0
Viewed 6472

For example, I have a dataframe like below:

    name      eventlist
0   a         [{'t': '1234', 'n': 'user_engagem1'},{'t': '2345', 'n': 'user_engagem2'},{'t': '3456', 'n': 'user_engagem3'}]
1   b         [{'t': '2345', 'n': 'user_engagem4'},{'t': '1345', 'n': 'user_engagem5'},{'t': '1356', 'n': 'user_engagem6'},{'t': '1345', 'n': 'user_engagem5'},{'t': '1359', 'n': 'user_engagem6'}]
2   c         [{'t': '1334', 'n': 'user_engagem3'},{'t': '2345', 'n': 'user_engagem4'},{'t': '3556', 'n': 'user_engagem2'}]

I trid with re.findall with a string, and it seems works, I get a result like ['1234', '2345', '3456'], but I can not applay it into dataframe

#code 1,apply to string successfully
str="[{'t': '1234', 'n': 'user_engagem'},{'t': '2345', 'n': 'user_engagem'},{'t': '3456', 'n': 'user_engagem'}]"
print(re.findall(r"t': '(.+?)', '", str))

#code 2,apply to dateframe doesn't work
df['t']=df['events'].str.findall(r"t': '(.+?)', '", df['events'])
print(list)

I want to get the result like 

    name      eventlist

0   a         ['1234', '2345', '3456']
1   b         ['2345', '1345','1234','1356', '1356']
2   c         ['1334', '2345', '3556']

or even better, I can get the result like

    name      t_first       t_last
0   a         1234           3456
1   b         2345           1359
2   c         1334           3556
pandas dictionary python json regex
3 Answers
df['eventlist'] = df['eventlist'].map(lambda x:[i['t'] for i in x])
df
     name                       eventlist
   0    a              [1234, 2345, 3456]
   1    b  [2345, 1345, 1356, 1345, 1359]
   2    c              [1334, 2345, 3556]

df['t_first'] = df['eventlist'][0]
df['t_last']=df['eventlist'].map(lambda x:x[len(x)-1])
df = df[['name','t_first','t_last']]
df
     name t_first t_last
   0    a    1234   3456
   1    b    2345   1359
   2    c    3456   3556
user_83292130

You can strings to convert list of dictionaries with ast.literal_eval and then get value by t with keys:

import ast

out = []
for x in df.pop('eventlist'):
    a = ast.literal_eval(x)
    out.append([a[0].get('t'), a[-1].get('t')])

Or use re.findall:

out = []
for x in df.pop('eventlist'):
    a = re.findall(r"t': '(.+?)', '", x)
    out.append([a[0], a[-1]])

print (out)
[['1234', '3456'], ['2345', '1359'], ['1334', '3556']]

Then create DataFrame and join to original:

df = df.join(pd.DataFrame(out, columns=['t_first','t_last'], index=df.index))
print (df)
  name t_first t_last
0    a    1234   3456
1    b    2345   1359
2    c    1334   3556

Another solution with findall and new columns by assign:

a = df.pop('eventlist').str.findall(r"t': '(.+?)'")
df = df.assign(t_first= a.str[0], t_last = a.str[-1])
user_29010020

str.findall needs one argument: The regex pattern.

# Call `pop` here to remove the "events" column.
v = df.pop('eventlist').str.findall(r"t': '(.+?)'")
print(v)

0                [1234, 2345, 3456]
1    [2345, 1345, 1356, 1345, 1359]
2                [1334, 2345, 3556]
Name: events, dtype: object

You can then load it into separate columns:

# More efficient than assigning if done in-place. 
df['t_first'] = v.str[0]
df['t_last'] = v.str[-1]
# Or, if you want to return a copy,
# df = df.assign(t_first=v.str[0], t_last=v.str[-1])

df

  name t_first t_last
0    a    1234   3456
1    b    2345   1359
2    c    1334   3556

Another, better option would be to pre-compile your pattern with re.compile and run it in a loop, extracting the first and last items from the findall result. 

import re

p = re.compile(r"t': '(.+?)'")
out = []
for name, string in zip(df.name, df.pop('eventlist')):
    a = p.findall(string)
    out.append([name, a[0], a[-1]])

pd.DataFrame(out, columns=['name', 't_first','t_last'], index=df.index)

  name t_first t_last
0    a    1234   3456
1    b    2345   1359
2    c    1334   3556

If you need to convert them to int, replace out.append([name, a[0], a[-1]]) with out.append([name, int(a[0]), int(a[-1])]).

The above solution assumes you will always have more than one match. If it is possible to only have a single match or no matches, you can modify the solution by checking the number of matches appending to count.

p = re.compile(r"t': '(.+?)'")
out = []
for name, string in zip(df.name, df.pop('eventlist')):
    first = second = np.nan
    if pd.notna(string):
        a = p.findall(string)
        if len(a) > 0:
            first = int(a[0])
            second = int(a[-1]) if len(a) > 1 else second

    out.append([name, first, second])

pd.DataFrame(out, columns=['name', 't_first','t_last'], index=df.index)

  name  t_first  t_last
0    a     1234    3456
1    b     2345    1359
2    c     1334    3556
user_49090870
Related