如何有效地从JSON列中提取字段？

Question

考虑下面的例子

data1 = [{'type': 'one', 'delta': '1', 'time': '2019'}, {'type': 'two', 'delta': '1', 'time': '2018'}]
data2 = [{'type': 'one', 'delta': '1', 'time': '2013'}, {'type': 'two', 'delta': '1', 'time': '2012'}]


dftest = pd.DataFrame({'weirdjson' : [data1, data2]})
dftest['normalcol'] = 1

dftest

Out[79]: 
                                                                                        weirdjson  normalcol  time_type_one  time_type_two
0  [{'type': 'one', 'delta': '1', 'time': '2019'}, {'type': 'two', 'delta': '1', 'time': '2018'}]          1           2019           2018
1  [{'type': 'one', 'delta': '1', 'time': '2013'}, {'type': 'two', 'delta': '1', 'time': '2012'}]          1           2013           2012

本质上，我想创建两个列time_type_one和time_type_two，每个列都包含相应的time值（对于第一行：2019对于type one和2018对于type two）。你知道吗

我怎样才能在熊猫身上做到这一点？我有很多行，所以我正在寻找一些非常有效的。 谢谢！你知道吗

Answer 1

你可以试试这个：

df_new = pd.DataFrame().append([x[y] for x in dftest.weirdjson for y in range(len(dftest.weirdjson))])
df_new = df_new.pivot(columns='type', values=['delta', 'time']).apply(lambda x: pd.Series(x.dropna().values)) 
df_new.columns = ['_'.join(col) for col in df_new.columns.values] 

  delta_one delta_two time_one time_two
0         1         1     2019     2018
1         1         1     2013     2017

Answer 2

您可以使用explode，并构造一个新的dataframe和unstack类型，如下所示：

s = dftest.weirdjson.explode()
df_new = (pd.DataFrame({'type': s.str['type'], 'time': s.str['time']}) 
            .set_index('type', append=True).time.unstack().add_prefix('time_type_'))

Out[461]:
type time_type_one time_type_two
0             2019          2018
1             2013          2012

Answer 3

试试这个：

import json
import pandas as pd

data = [{'normalcol':1, 'weirdjsoncol':'[{"type": "one", "delta": "1", "time": "2019"}, {"type": "two", "delta": "1", "time": "2018"}]'}, {'normalcol':2, 'weirdjsoncol':'[{"type": "two", "delta": "1", "time": "2017"}, {"type": "one", "delta": "1", "time": "2013"}]'}]

df = pd.DataFrame(data)

df['time_type_one'] = df['weirdjsoncol'].apply(lambda x: next((i for i in json.loads(x) if i["type"] == "one"), None)["time"])

df['time_type_two'] = df['weirdjsoncol'].apply(lambda x: next((i for i in json.loads(x) if i["type"] == "two"), None)["time"])