如何将字典列表转换为数据帧？

Question

这是我拥有的一个字典列表，它将被转换为数据帧。我尝试使用多索引，但无法转换整个数据帧

response = [{
"name": "xyz",
"empId": "007",
"details": [{
        "address": [{
            "street": "x street",
            "city": "x city"
        }, {
            "street": "xx street",
            "city": "xx city"
        }],
        "country": "xxz country"
    },
    {
        "address": [{
            "street": "y street",
            "city": "y city"
        }, {
            "street": "yy street",
            "city": "yy city"
        }],
        "country": "yyz country"
    }
]
}]

我使用以下代码将字典的内部列表获取到数据帧：

for i in details:    
    Country = i['country']

    street =[]
    city = []
    index = pd.MultiIndex.from_arrays([[Country]*len(i['address']), list(range(1,len(i['address'])+1))], names=['Country', 'SL No'])
    df=pd.DataFrame(columns=["Street","City"],index=index)
    if i['address']:
        for row in i['address']:
            street.append(row['street'])
            city.append(row['city'])

    df["Street"]=street
    df["City"]=city

    frames.append(df)
df_final=pd.concat(frames)

获得的产出：

Country     SL No   Street     City
xxz country 1       x street   x city
            2      xx street  xx city
yyz country 1       y street   y city
            2      yy street  yy city

如何将字典列表转换为数据帧，同时保留所有信息

我想要的最终输出：

Name    EmpId    Country        Street     City
xyz     007      xxz country    x street   x city
                                xx street  xx city
                 yyz country    y street   y city
                                yy street  yy cit

Answer 1

据我所知，没有简单的方法可以做到这一点，因为您的数据包含多个级别的列表。虽然有点复杂，但下面的方法应该是可行的。代码将以迭代方式^{}列出并将字典转换为带有^{}的列

df = pd.DataFrame.from_records(response)
df = df.explode('details', ignore_index=True)
df = pd.concat([df, pd.json_normalize(df['details'])], axis=1)
df = df.explode('address', ignore_index=True)
df = pd.concat([df, pd.json_normalize(df['address'])], axis=1)
df = df.drop(columns=['details', 'address'])

结果:

  name empId      country     street     city
0  xyz   007  xxz country   x street   x city
1  xyz   007  xxz country  xx street  xx city
2  xyz   007  yyz country   y street   y city
3  xyz   007  yyz country  yy street  yy city

注意：对于早于1.1.0的pandas版本，explode没有ignore_index参数。相反，在explode之后使用reset_index(drop=True)

此外，在旧版本中，您需要使用pd.io.json.json_normalize而不是pd.json_normalize

Answer 2

将^{}与^{}一起使用：

df = pd.json_normalize(response,
                       record_path=['details','address'],
                       meta=['name','empId', ['address','country']]
                       )

df = df.set_index(['name','empId','address.country'])
print (df)
                               street     city
name empId address.country                    
xyz  007   xxz country       x street   x city
           xxz country      xx street  xx city
           yyz country       y street   y city
           yyz country      yy street  yy city

对于较旧版本，请使用：

df = pd.io.json.json_normalize(response,
                               record_path=['details','address'],
                               meta=['name','empId', ['address','country']]
                       )

编辑：

使用多个值进行测试，效果良好：

response = [{
"name": "xyz",
"empId": "007",
"details": [{
        "address": [{
            "street": "x street",
            "city": "x city"
        }, {
            "street": "xx street",
            "city": "xx city"
        }],
        "country": "xxz country"
    },
    {
        "address": [{
            "street": "y street",
            "city": "y city"
        }, {
            "street": "yy street",
            "city": "yy city"
        }],
        "country": "yyz country"
    }
]
},
            {
"name": "xyz1",
"empId": "0071",
"details": [{
        "address": [{
            "street": "x street1",
            "city": "x city1"
        }, {
            "street": "xx stree1t",
            "city": "xx city1"
        }],
        "country": "xxz country"
    },
    {
        "address": [{
            "street": "y street",
            "city": "y city"
        }, {
            "street": "yy street",
            "city": "yy city"
        }],
        "country": "yyz country"
    }
]
}]

---

df = pd.json_normalize(response,
                       record_path=['details','address'],
                       meta=['name','empId', ['address','country']]
                       )

df = df.set_index(['name','empId','address.country'])

---

print (df)
                                street      city
name empId address.country                      
xyz  007   xxz country        x street    x city
           xxz country       xx street   xx city
           yyz country        y street    y city
           yyz country       yy street   yy city
xyz1 0071  xxz country       x street1   x city1
           xxz country      xx stree1t  xx city1
           yyz country        y street    y city
           yyz country       yy street   yy city