如何基于pandas数据框中的元数据字典创建相应的值？

Question

我有一个熊猫数据框，看起来像这样：

    Column1 Column2 Column3
1   apple    fruit  [{"tag_id":123123,'name':"juicy","weight":'1'},{"tag_id":55657672,'name':'Spain',"weight":"53"},{"tag_id":24356,'name':'the UK',"weight":"67"]

2   cat      animal [{"tag_id":1235,'name':"funny","weight":"10"},{"tag_id":4514,'name':'expensive',"weight":"56"
}]

3  English   language [{"tag_id":10010,'name':"culture","weight":"34"},{"tag_id":44123,"name"="COVID-19","weight":"5"}]

我想得到的是下面这个

       Column1 Column2 tag_id     name        weight
    1   apple    fruit  123123    juicy        1
    2   apple    fruit  55657     Spain        53
    3   apple    fruit  24356     the UK       67
    4   apple    fruit  24356     the UK       67
    5   cat      animal  1235     funny        10
    6   cat      animal  4514     expensive    56
    7   English  language  10010  culture      34
    8   English  language  44123  COVID-19      5

是的，我只是不知道如何转换字典数据并用键值赋值

谢谢

Answer 1

我们可以使用^{}和^{}的组合

explode：将列表中的每个元素转换为一行

json_normalize将dict转换为列

import pandas as pd
df = pd.DataFrame([['apple','fruit',
    [{"tag_id":123123,'name':"juicy","weight":'1'}, {"tag_id":55657672,'name':'Spain',"weight":"53"},{"tag_id":24356,'name':'the UK',"weight":"67"}]],
    ['cat','animal',[{"tag_id":1235,'name':"funny","weight":"10"},{"tag_id":4514,'name':'expensive',"weight":"56"}]]],columns=['col1','col2','col3'])

df = df.explode('col3').reset_index(drop=True)
tempdf = pd.json_normalize(df['col3'])
df = pd.concat([df,tempdf],axis=1)
df.drop('col3',axis=1,inplace=True)
print(df)

+  +    +    +     +     -+     +
|    | col1   | col2   |   tag_id | name      |   weight |
+====+========+========+==========+===========+==========+
|  0 | apple  | fruit  |   123123 | juicy     |        1 |
|  1 | apple  | fruit  | 55657672 | Spain     |       53 |
|  2 | apple  | fruit  |    24356 | the UK    |       67 |
|  3 | cat    | animal |     1235 | funny     |       10 |
|  4 | cat    | animal |     4514 | expensive |       56 |
+  +    +    +     +     -+     +