无法规范化Pandas中的嵌套Json

Question

我正在拼命地尝试规范化这个包含json结构的pandas列

ENTRID           PAYLOAD
486473  "{""ratingInformation"":{""rating"": 5.0, ""rating_raw"": 5.0, ""numberOfRating"": 9}, ""serviceFeatures"":{""delivery"": true, ""amenities"": [{""Akzeptierte Karten"": ""Kredit, EC"", ""Lieferung möglich"": ""Ja"", ""Parkplatz & Parkhaus"": ""Kundenparkplatz""}]}, ""starRatings"": {""numOneStarRatings"":0,""numTwoStarRatings"":0,""numThreeStarRatings"":0,""numFourStarRatings"":1,""numFiveStarRatings"":8}}"
76975   "{""ratingInformation"":{""rating"": 5.0, ""rating_raw"": 5.0, ""numberOfRating"": 9}, ""serviceFeatures"":{""amenities"": [{""Reservierung möglich"": ""Kein Lieferservice""}]}, ""starRatings"": {""numOneStarRatings"":0,""numTwoStarRatings"":0,""numThreeStarRatings"":0,""numFourStarRatings"":1,""numFiveStarRatings"":8}}"

我的代码如下所示：

df = pd.read_csv('MatchingEID_reduced.csv', sep=';', encoding = 'utf-8')
json_struct = json.loads(df.to_json(orient="records"))
df_flat = pd.json_normalize(json_struct)

问题： 我的结果与以前的结构相同-&燃气轮机；名为'ENTRID'和'PAYLOAD'的两列

你知道怎么处理吗

Answer 1

您需要确保负载是JSON，然后再尝试对其进行规范化

import json
df = pd.read_csv(io.StringIO("""ENTRID           PAYLOAD
486473  "{""ratingInformation"":{""rating"": 5.0, ""rating_raw"": 5.0, ""numberOfRating"": 9}, ""serviceFeatures"":{""delivery"": true, ""amenities"": [{""Akzeptierte Karten"": ""Kredit, EC"", ""Lieferung möglich"": ""Ja"", ""Parkplatz & Parkhaus"": ""Kundenparkplatz""}]}, ""starRatings"": {""numOneStarRatings"":0,""numTwoStarRatings"":0,""numThreeStarRatings"":0,""numFourStarRatings"":1,""numFiveStarRatings"":8}}"
76975   "{""ratingInformation"":{""rating"": 5.0, ""rating_raw"": 5.0, ""numberOfRating"": 9}, ""serviceFeatures"":{""amenities"": [{""Reservierung möglich"": ""Kein Lieferservice""}]}, ""starRatings"": {""numOneStarRatings"":0,""numTwoStarRatings"":0,""numThreeStarRatings"":0,""numFourStarRatings"":1,""numFiveStarRatings"":8}}"
"""), sep="\s+")
pd.json_normalize(df
                  .assign(PAYLOAD=lambda dfa: dfa["PAYLOAD"].apply(lambda x: json.loads(x)))
                  .to_dict(orient="records")
                 )