如何在python中把json数据（来自一个列表）平铺成独立的列？

您所在的位置：网站首页 › python中+str+ › 如何在python中把json数据（来自一个列表）平铺成独立的列？

如何在python中把json数据（来自一个列表）平铺成独立的列？

2023-03-22 08:12| 来源: 网络整理| 查看: 265

你的问题在结构上类似于this one.我的答案是根据那里的一个答案调整的。

你需要将问题分解为以下步骤。

convert the rows in 'salesMetricsByAsin' containing a list of jsons to seperate rows flatten the json into seperate columns

对于步骤1，你可以使用df。explode('column_name')。对于第2步，你确实可以使用json_normalize()

Code:

import pandas as pd d = { 'asin' : ['B001U81442', 'B003SVYRNE'], 'salesMetricsByAsin' : [ [{'reportingDate': '2021-01-20', 'salesMetricsByDate': {'shippedRevenue': {'amount': 9.99, 'currencyCode': 'USD'}, 'shippedCOGS': {'amount': 5.25, 'currencyCode': 'USD'}, 'shippedUnits': 1, 'customerReturns': 0, 'freeReplacements': 0, 'preOrderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedRevenue': {'amount': 9.99, 'currencyCode': 'USD'}, 'orderedUnits': 1, 'preOrderedUnits': 0, 'shippedSubcategorySalesRank': 84, 'orderedSubcategorySalesRank': 83, 'replenishableOutOfStock': 0.0, 'lostBuyBox': 0.0, 'glanceViews': 3, 'conversionRate': 0.3333}}, {'reportingDate': '2021-01-21', 'salesMetricsByDate': {'shippedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedCOGS': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedUnits': 0, 'customerReturns': 0, 'freeReplacements': 0, 'preOrderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedUnits': 0, 'preOrderedUnits': 0, 'shippedSubcategorySalesRank': None, 'orderedSubcategorySalesRank': 83, 'replenishableOutOfStock': 0.0, 'lostBuyBox': 0.0, 'glanceViews': 0, 'conversionRate': 0.0}}], [{'reportingDate': '2021-06-28', 'salesMetricsByDate': {'shippedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedCOGS': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedUnits': 0, 'customerReturns': 1, 'freeReplacements': 0, 'preOrderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedUnits': 0, 'preOrderedUnits': 0, 'shippedSubcategorySalesRank': None, 'orderedSubcategorySalesRank': None, 'replenishableOutOfStock': 0.0, 'lostBuyBox': 0.0, 'glanceViews': 0, 'conversionRate': 0.0}}, {'reportingDate': '2021-06-29', 'salesMetricsByDate': {'shippedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedCOGS': {'amount': 0.0, 'currencyCode': 'USD'}, 'shippedUnits': 0, 'customerReturns': 0, 'freeReplacements': 0, 'preOrderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedRevenue': {'amount': 0.0, 'currencyCode': 'USD'}, 'orderedUnits': 0, 'preOrderedUnits': 0, 'shippedSubcategorySalesRank': None, 'orderedSubcategorySalesRank': None, 'replenishableOutOfStock': 0.0, 'lostBuyBox': 0.0, 'glanceViews': 0, 'conversionRate': 0.0}}], ], '3rd_col' : ['AMAZON_RETAIL', 'AMAZON_RE'] } df = pd.DataFrame(d) df_2 = df.explode('salesMetricsByAsin').reset_index() df_2.join(pd.json_normalize(df_2.pop('salesMetricsByAsin')))

Output:

index asin 3rd_col reportingDate salesMetricsByDate.shippedRevenue.amount salesMetricsByDate.shippedRevenue.currencyCode salesMetricsByDate.shippedCOGS.amount salesMetricsByDate.shippedCOGS.currencyCode salesMetricsByDate.shippedUnits salesMetricsByDate.customerReturns ... salesMetricsByDate.orderedRevenue.amount salesMetricsByDate.orderedRevenue.currencyCode salesMetricsByDate.orderedUnits salesMetricsByDate.preOrderedUnits salesMetricsByDate.shippedSubcategorySalesRank salesMetricsByDate.orderedSubcategorySalesRank salesMetricsByDate.replenishableOutOfStock salesMetricsByDate.lostBuyBox salesMetricsByDate.glanceViews salesMetricsByDate.conversionRate 0 0 B001U81442 AMAZON_RETAIL 2021-01-20 9.99 USD 5.25 USD 1 0 ... 9.99 USD 1 0 84.0 83.0 0.0 0.0 3 0.3333 1 0 B001U81442 AMAZON_RETAIL 2021-01-21 0.00 USD 0.00 USD 0 0 ... 0.00 USD 0 0 NaN 83.0 0.0 0.0 0 0.0000 2 1 B003SVYRNE AMAZON_RE 2021-06-28 0.00 USD 0.00 USD 0 1 ... 0.00 USD 0 0 NaN NaN 0.0 0.0 0 0.0000 3 1 B003SVYRNE AMAZON_RE 2021-06-29 0.00 USD 0.00 USD 0 0 ... 0.00 USD 0 0 NaN NaN 0.0 0.0 0 0.0000

【本文地址】

如何在python中把json数据（来自一个列表）平铺成独立的列？

如何在python中把json数据（来自一个列表）平铺成独立的列？

今日新闻

推荐新闻