如何在python中将这个json多列表写入单独的列中

Question

我有这个JSON文件：

{"a": [{"Name": "name1",
"number": "number1",
"defaultPrice": {"p": "232", "currency": "CAD"},
"prices": {"DZ": {"p": "62", "currency": "RMB"},
 "AU": {"p": "73", "currency": "AUD"},
"lg": "en"}},
{"Name": "name2",
"number": "number2",
 "defaultPrice": {"p": "233", "currency": "CAD"},
 "prices": {"DZ": {"p": "63", "currency": "RMB"},
 "US": {"p": "72", "currency": "USD"},
 "Lg": "en"}}]}

现在我得到一个包含name，number，defaultprices，prices的表，但是prices列有三行，prices 63需要从键p"p": "63", "currency": "RMB".读取

但我希望得到一个价格和货币分开列的表格，我用了这个：

ndf=pd.concat（[pd.Series（x）表示x的价格]，轴=1）

但得到一个错误的答案：

 0                                                  1
 DZ           {"p": "232", "currency": "CAD"}  {"p": "62", "currency": "RMB"}
 AU           {"p": "233", "currency": "CAD"}    {"p": "63","currency":"RMB"}

有没有办法纠正这个，这样我就可以得到这个预期的输出

Name    Number   Code  currency
name1   number1   AU    AUD      
name1   number1   DZ    RMB      

非常感谢

Answer 1

您可以在defaultPrice列上使用apply(pd.Series)，将其拆分为单独的列，然后将其连接回原始数据帧

prices = {"a": [{"Name": "name1",
"number": "number1",
"defaultPrice": {"p": "232", "currency": "CAD"},
"prices": {"DZ": {"p": "62", "currency": "RMB"},
 "AU": {"p": "73", "currency": "AUD"},
"lg": "en"}},
{"Name": "name2",
"number": "number2",
 "defaultPrice": {"p": "233", "currency": "CAD"},
 "prices": {"DZ": {"p": "63", "currency": "RMB"},
 "US": {"p": "72", "currency": "USD"},
 "Lg": "en"}}]}

ndf = pd.DataFrame(prices['a'])
pd.concat([ndf, ndf['defaultPrice'].apply(pd.Series)], axis=1).drop('defaultPrice', axis=1)

但是，您的prices列仍然是字典列表。但是，由于您没有提到您希望如何处理它，所以我只保留了它的原样（不包括在输出中）

输出：

Name    number  p   currency
name1   number1 232 CAD
name2   number2 233 CAD

Answer 2

json字符串：

j = {"a": [{ "Name": "name1",
             "number": "number1",
             "defaultPrice":  {"p": "232", "currency": "CAD"},
             "prices": {"DZ": {"p": "62", "currency": "RMB"},
                        "AU": {"p": "73", "currency": "AUD"},
                        "lg": "en"
                       }
             },
            {"Name": "name2",
             "number": "number2",
             "defaultPrice":  {"p": "233", "currency": "CAD"},
             "prices": {"DZ": {"p": "63", "currency": "RMB"},
                        "US": {"p": "72", "currency": "USD"},
                        "Lg": "en"
                       }
            }
          ]}

获取所需输出的代码：

country_codes = set()
for d in j['a']:
  c = d['prices'].keys()
  country_codes.update(c)

country_codes = sorted([i for i in country_codes if not i in ['lg','Lg']])
country_codes

meta = ['Name','number'] + [['prices',c,'p'] for c in country_codes] + [['prices',c,'currency'] for c in country_codes] 

df = json_normalize(j['a'], record_path = 'prices', meta = meta,errors='ignore')
df = df.rename(columns={0: 'countryCode'})
df = df[~df['countryCode'].isin(['lg','Lg'])]

for idx, row in df.iterrows():
    country = row['countryCode']
    col_price = df.columns[df.columns.str.contains(country+'.p')][0]
    col_currency = df.columns[df.columns.str.contains(country+'.currency')][0]
    price = row[col_price]
    currency = row[col_currency]
    df.loc[idx,'price'] = price
    df.loc[idx,'currency'] = currency

df = df[['Name','number','countryCode', 'currency', 'price']]


df

这将提供：

    Name   number countryCode currency price
0  name1  number1          DZ      RMB    62
1  name1  number1          AU      AUD    73
3  name2  number2          DZ      RMB    63
4  name2  number2          US      USD    72