python - 如何编写获取此 JSON 数据并将其转换为数据表的 Python 脚本
问题描述
`获取此 JSON 数据并将其转换为数据表的 Python 脚本
`下面是JSON数据
{"inappproduct": [{"packageName": "game",
"sku": "game_product1",
"status": "active",
"purchaseType": "managedUser",
"defaultPrice": {"priceMicros": "69990000", "currency": "CAD"},
"prices": {"DZ": {"priceMicros": "6325000000", "currency": "DZD"},
"AU": {"priceMicros": "79990000", "currency": "AUD"},
"CA": {"priceMicros": "69990000", "currency": "CAD"},
"IT": {"priceMicros": "54990000", "currency": "EUR"},
"JP": {"priceMicros": "6000000000", "currency": "JPY"},
"RU": {"priceMicros": "3790000000", "currency": "RUB"},
"SG": {"priceMicros": "68980000", "currency": "SGD"},
"KR": {"priceMicros": "65000000000", "currency": "KRW"},
"GB": {"priceMicros": "48990000", "currency": "GBP"},
"US": {"priceMicros": "49990000", "currency": "USD"},
"listings": {"en-US": {"title": "currency_1",
"description": "In-game currency"}},
"defaultLanguage": "en-US"}},
{"packageName": "game",
"sku": "game_bundle1",
"status": "active",
"purchaseType": "managedUser",
"defaultPrice": {"priceMicros": "139990000", "currency": "CAD"},
"prices": {"DZ": {"priceMicros": "12750000000", "currency": "DZD"},
"AU": {"priceMicros": "159990000", "currency": "AUD"},
"CA": {"priceMicros": "139990000", "currency": "CAD"},
"IT": {"priceMicros": "109990000", "currency": "EUR"},
"JP": {"priceMicros": "11800000000", "currency": "JPY"},
"RU": {"priceMicros": "7490000000", "currency": "RUB"},
"SG": {"priceMicros": "148980000", "currency": "SGD"},
"KR": {"priceMicros": "130000000000", "currency": "KRW"},
"GB": {"priceMicros": "99990000", "currency": "GBP"},
"US": {"priceMicros": "99990000", "currency": "USD"},
"listings": {"en-US": {"title": "bundle",
"description": "In-game bundle"}},
"defaultLanguage": "en-US"}}]}
The following are some considerations:
● 列 packageName 可以从 JSON 键“packageName”
● The column sku can be found from the JSON key “sku”
中找到 ● 列 countryCode 可以从包含两个的 JSON 键中找到-
letter country codes nested within the JSON key “prices”
● 列货币可以从 JSON 键“currency”中找到 ` ● 列 price 可以是从 JSON 键“priceMicros”中找到。价格等于“priceMicros”除以 1,000,000。
`the result should look like below
packageName sku countryCode currency price
game game_product1 CA CAD 69.99
game game_product1 US USD 48.99
. . . . .
. . . . .
. . . . .
game game_bundle1 GB GBP 99.99
解决方案
我会使用pandas
和列表理解,所以pandas
如果你还没有安装,请安装包。
我将为您的“JSON 数据”命名。
my_json = {
"inappproduct": [{"packageName": "game",
"sku": "game_product1",
"status": "active",
"purchaseType": "managedUser",
"defaultPrice": {"priceMicros": "69990000", "currency": "CAD"},
"prices": {"DZ": {"priceMicros": "6325000000", "currency": "DZD"},
"AU": {"priceMicros": "79990000", "currency": "AUD"},
"CA": {"priceMicros": "69990000", "currency": "CAD"},
"IT": {"priceMicros": "54990000", "currency": "EUR"},
"JP": {"priceMicros": "6000000000", "currency": "JPY"},
"RU": {"priceMicros": "3790000000", "currency": "RUB"},
"SG": {"priceMicros": "68980000", "currency": "SGD"},
"KR": {"priceMicros": "65000000000", "currency": "KRW"},
"GB": {"priceMicros": "48990000", "currency": "GBP"},
"US": {"priceMicros": "49990000", "currency": "USD"},
"listings": {"en-US": {"title": "currency_1",
"description": "In-game currency"}},
"defaultLanguage": "en-US"}},
{"packageName": "game",
"sku": "game_bundle1",
"status": "active",
"purchaseType": "managedUser",
"defaultPrice": {"priceMicros": "139990000", "currency": "CAD"},
"prices": {"DZ": {"priceMicros": "12750000000", "currency": "DZD"},
"AU": {"priceMicros": "159990000", "currency": "AUD"},
"CA": {"priceMicros": "139990000", "currency": "CAD"},
"IT": {"priceMicros": "109990000", "currency": "EUR"},
"JP": {"priceMicros": "11800000000", "currency": "JPY"},
"RU": {"priceMicros": "7490000000", "currency": "RUB"},
"SG": {"priceMicros": "148980000", "currency": "SGD"},
"KR": {"priceMicros": "130000000000", "currency": "KRW"},
"GB": {"priceMicros": "99990000", "currency": "GBP"},
"US": {"priceMicros": "99990000", "currency": "USD"},
"listings": {"en-US": {"title": "bundle",
"description": "In-game bundle"}},
"defaultLanguage": "en-US"}}]}
接着:
>>> import pandas as pd
>>> records = [
... {
... "packageName": x["packageName"],
... "sku": x["sku"],
... "countryCode": k,
... "currency": v["currency"],
... "price": str(float(v["priceMicros"])/1e6)
... }
... for x in my_json["inappproduct"]
... for k, v in x["prices"].items()
... if isinstance(v, dict) and v.get("currency") is not None
... ]
>>> app_df = pd.DataFrame(records)
>>> app_df = app_df.reindex(columns=["packageName", "sku", "countryCode", "currency", "price"])
>>> app_df
packageName sku countryCode currency price
0 game game_product1 CA CAD 69.99
1 game game_product1 JP JPY 6000.0
2 game game_product1 IT EUR 54.99
3 game game_product1 DZ DZD 6325.0
4 game game_product1 GB GBP 48.99
5 game game_product1 RU RUB 3790.0
6 game game_product1 US USD 49.99
7 game game_product1 KR KRW 65000.0
8 game game_product1 AU AUD 79.99
9 game game_product1 SG SGD 68.98
10 game game_bundle1 CA CAD 139.99
11 game game_bundle1 JP JPY 11800.0
12 game game_bundle1 IT EUR 109.99
13 game game_bundle1 DZ DZD 12750.0
14 game game_bundle1 GB GBP 99.99
15 game game_bundle1 RU RUB 7490.0
16 game game_bundle1 US USD 99.99
17 game game_bundle1 KR KRW 130000.0
18 game game_bundle1 AU AUD 159.99
19 game game_bundle1 SG SGD 148.98
推荐阅读
- python - 如何在 Python 中“相乘”数据帧?
- azure - Azure Kusto 查询输出格式
- linker - Visual Studion 2019 中的 LNK 2038 和 LNK 2001 问题
- html - 在 css 媒体查询中将照片和内容堆叠在一起
- python - 无法在 JIRA 和 Python 之间建立连接
- archive - 以编程方式归档当前从 Safari Web 扩展在 Safari 中打开的页面(Chrome 中 chrome.pageCapture API 的替代方案)
- c# - 从多部分/表单数据内容的 ASP.NET Core HttpContext.Request 读取 excel 文件?
- apache - Apache 服务器和 Payara 服务器会话在每次通过域名向网页发出请求后重新创建
- javascript - 开源 React App 在 npm install 和 npm start 后编译失败
- python - 如何在没有 os 的情况下访问环境变量值?