python - 通过 Python 将 JSON 转换为 CSV
问题描述
我有一个以下格式的 JSON 对象。本质上，它们是一些产品信息。
{
"6923492595127": {
"upcName": {
"en": "1",
"hi": "",
"ja": "",
"zh": ""
},
"productName": {
"en": "Excellence",
"hi": "",
"ja": "",
"zh": ""
},
"thumbnailImageUrl": null,
"heroImageUrl": null,
"swatchImageUrl": null,
"shadeArray": [
{
"histogram": {
"r": "0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 0 58 59 60 61 62 63 64 0 66 67 68 0 70 0 72 73 0 75 0 77 78 79 80 81 0 83 0 85 86 87 88 0 90 0 92 93 0 95 0 0 98 0 100 101 0 103 104 0 106 0 108 109 0 111 0 113 0 115 116 0 0 119 0 121 0 123 124 0 126 0 0 0 0 0 132 133 134 135 0 0 0 0 140 141 142 0 0 145 146 147 0 0 150 0 0 153 0 0 0 157 0 0 160 161 0 0 0 165 0 0 0 0 0 0 172 0 0 0 0 0 0 0 0 0 182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"g": "0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 0 58 59 60 61 62 63 64 0 66 67 68 0 70 0 72 73 0 75 0 77 78 79 80 81 0 83 0 85 86 87 88 0 90 0 92 93 0 95 0 0 98 0 100 101 0 103 104 0 106 0 108 109 0 111 0 113 0 115 116 0 0 119 0 121 0 123 124 0 126 0 0 0 0 0 132 133 134 135 0 0 0 0 140 141 142 0 0 145 146 147 0 0 150 0 0 153 0 0 0 157 0 0 160 161 0 0 0 165 0 0 0 0 0 0 172 0 0 0 0 0 0 0 0 0 182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"b": "0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 0 58 59 60 61 62 63 64 0 66 67 68 0 70 0 72 73 0 75 0 77 78 79 80 81 0 83 0 85 86 87 88 0 90 0 92 93 0 95 0 0 98 0 100 101 0 103 104 0 106 0 108 109 0 111 0 113 0 115 116 0 0 119 0 121 0 123 124 0 126 0 0 0 0 0 132 133 134 135 0 0 0 0 140 141 142 0 0 145 146 147 0 0 150 0 0 153 0 0 0 157 0 0 160 161 0 0 0 165 0 0 0 0 0 0 172 0 0 0 0 0 0 0 0 0 182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"c": "61484 41185 37054 36648 27046 28373 27107 26508 13034 20094 20425 18989 11940 11254 10920 10084 9373 9214 8437 8326 4030 7893 7688 3781 6739 3298 6865 6022 2890 2945 5707 2748 2616 4846 2386 2138 1908 1920 1749 3485 1566 1597 1582 1524 1448 1443 1341 1275 1231 1134 1041 953 863 912 893 812 766 0 734 715 683 601 596 534 511 0 456 446 377 0 747 0 308 265 0 286 0 256 236 207 197 188 0 156 0 159 115 138 94 0 123 0 82 112 0 162 0 0 146 0 75 43 0 39 28 0 46 0 33 28 0 16 0 30 0 23 16 0 0 28 0 16 0 10 25 0 10 0 0 0 0 0 8 18 5 7 0 0 0 0 6 5 3 0 0 3 3 3 0 0 4 0 0 2 0 0 0 3 0 0 2 1 0 0 0 4 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"totalpixels": 549705
},
"intensity": 0.8,
"shine": 0,
"effect": 0,
"family": "",
"blendGradientTopOffset": 0,
"blendGradientBottomOffset": 0,
"warning_type": null
}
]
},
"6923492597510": {
"upcName": {
"en": "3",
"hi": "",
"ja": "",
"zh": ""
},
"productName": {
"en": "Excellence",
"hi": "",
"ja": "",
"zh": ""
},
"thumbnailImageUrl": null,
"heroImageUrl": null,
"swatchImageUrl": null,
"shadeArray": [
{
"histogram": {
"r": "0 4 7 10 13 16 18 21 22 24 26 27 0 30 31 33 0 34 36 0 38 0 40 41 0 44 46 0 48 49 0 51 0 53 55 0 57 0 59 0 61 63 0 65 0 68 0 69 0 72 0 74 75 0 0 78 80 0 0 82 0 85 0 88 0 90 0 91 0 93 0 96 0 98 0 100 0 101 0 104 0 106 0 0 110 0 112 0 114 115 0 0 118 119 0 0 122 123 0 0 125 0 126 0 0 131 0 0 134 134 0 0 139 0 0 139 0 0 144 0 146 0 0 0 150 0 152 0 0 156 158 156 0 0 160 0 0 0 0 0 0 0 174 169 172 0 0 174 0 0 0 0 0 181 0 184 0 183 0 0 188 0 188 191 0 0 0 196 0 0 0 0 0 200 0 205 0 0 0 0 0 210 0 214 0 0 0 220 0 0 0 0 0 0 0 0 0 0 0 0 229 0 0 0 0 0 0 0 0 0 0 243 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"g": "0 0 0 0 0 0 0 0 2 3 5 6 0 8 9 10 0 12 13 0 15 0 16 18 0 19 21 0 22 24 0 26 0 27 29 0 30 0 32 0 34 35 0 37 0 39 0 41 0 43 0 44 46 0 0 49 50 0 0 53 0 55 0 57 0 59 0 61 0 63 0 65 0 67 0 69 0 71 0 73 0 75 0 0 77 0 79 0 81 83 0 0 86 86 0 0 90 90 0 0 94 0 96 0 0 99 0 0 103 103 0 0 105 0 0 109 0 0 112 0 114 0 0 0 118 0 121 0 0 124 124 124 0 0 129 0 0 0 0 0 0 0 136 137 138 0 0 141 0 0 0 0 0 149 0 149 0 152 0 0 155 0 155 157 0 0 0 159 0 0 0 0 0 168 0 167 0 0 0 0 0 173 0 179 0 0 0 181 0 0 0 0 0 0 0 0 0 0 0 0 197 0 0 0 0 0 0 0 0 0 0 208 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"b": "0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 0 4 5 0 6 0 8 9 0 11 12 0 13 15 0 16 0 18 19 0 21 0 22 0 24 26 0 27 0 29 0 31 0 32 0 34 36 0 0 38 39 0 0 41 0 43 0 45 0 47 0 49 0 50 0 52 0 54 0 56 0 59 0 60 0 62 0 0 64 0 66 0 68 70 0 0 73 74 0 0 76 78 0 0 81 0 85 0 0 85 0 0 89 91 0 0 92 0 0 97 0 0 98 0 100 0 0 0 103 0 106 0 0 107 109 113 0 0 115 0 0 0 0 0 0 0 116 123 122 0 0 126 0 0 0 0 0 131 0 133 0 138 0 0 138 0 143 140 0 0 0 145 0 0 0 0 0 150 0 153 0 0 0 0 0 159 0 157 0 0 0 161 0 0 0 0 0 0 0 0 0 0 0 0 173 0 0 0 0 0 0 0 0 0 0 183 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"c": "492493 5608 5037 4811 4046 1920 3528 1706 1566 1597 1582 1524 0 1448 1443 1341 0 1275 1231 0 1134 0 1041 953 0 863 912 0 893 812 0 766 0 734 715 0 683 0 601 0 596 534 0 511 0 456 0 446 0 377 0 386 361 0 0 308 265 0 0 286 0 256 0 236 0 207 0 197 0 188 0 156 0 159 0 115 0 138 0 94 0 123 0 0 82 0 112 0 90 72 0 0 81 65 0 0 75 43 0 0 39 0 28 0 0 46 0 0 33 28 0 0 16 0 0 30 0 0 23 0 16 0 0 0 28 0 16 0 0 10 15 10 0 0 10 0 0 0 0 0 0 0 8 8 15 0 0 7 0 0 0 0 0 6 0 5 0 3 0 0 3 0 3 3 0 0 0 4 0 0 0 0 0 2 0 3 0 0 0 0 0 2 0 1 0 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0",
"totalpixels": 549705
},
"intensity": 0.791413,
"shine": 0,
"effect": 0,
"family": "",
"blendGradientTopOffset": 0,
"blendGradientBottomOffset": 0,
"warning_type": null
}
]
}
}
我想将它们转换为带有以下标题和行的 CSV。
+ID+--+upcName_en+...+productName_en+...+thumbnailImageUrl+heroImageUrl+swatchImageUrl+shadeArray_histogram_r+...+shadeArray_intensity+...+shadeArray_warning_type
+6923492595127+1+Excellence+...+null+null+null+0 1 2 3 4 5 6 7 8 9 10...+0.8+...+null
+6923492597510+3+Excellence+...+null+null+null+0 1 2 3 4 5 6 7 8 9 10...+0.8+...+null
我尝试使用从其他链接引用的以下代码来展平 JSON，但结果不是我想要的。
import pandas
import json
from pandas.io.json import json_normalize
def flatten_json(y):
out = {}
def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + '_')
i += 1
else:
out[name[:-1]] = x
flatten(y)
return out
# Opening JSON file
filename = 'somefile'
with open(filename+'.json', encoding='utf8') as file:
data = json.load(file)
file.close()
flat = flatten_json(data)
with open(filename+'.csv', 'w') as output :
for (k,v) in flat.items() :
try :
output.write(k + ' | ' + v + '\n')
except :
output.write(k + ' | ' + '\n')
output.close()
解决方案
您可以遍历 JSON 并将数据逐行添加到 CSV：
import csv
import json
with open('data.json', 'r') as f:
data = json.load(f)
with open('result.csv', 'w', newline='') as f:
w = csv.writer(f)
for product_id, product in data.items():
w.writerow([product_id,
product['upcName']['en'],
product['productName']['en'],
product['thumbnailImageUrl'],
product['heroImageUrl'],
product['swatchImageUrl'],
product['shadeArray'][0]['histogram']['r'],
product['shadeArray'][0]['intensity'],
product['shadeArray'][0]['warning_type']])
输出：
6923492595127,1,Excellence,,,,0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 0 58 59 60 61 62 63 64 0 66 67 68 0 70 0 72 73 0 75 0 77 78 79 80 81 0 83 0 85 86 87 88 0 90 0 92 93 0 95 0 0 98 0 100 101 0 103 104 0 106 0 108 109 0 111 0 113 0 115 116 0 0 119 0 121 0 123 124 0 126 0 0 0 0 0 132 133 134 135 0 0 0 0 140 141 142 0 0 145 146 147 0 0 150 0 0 153 0 0 0 157 0 0 160 161 0 0 0 165 0 0 0 0 0 0 172 0 0 0 0 0 0 0 0 0 182 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0,0.8,
6923492597510,3,Excellence,,,,0 4 7 10 13 16 18 21 22 24 26 27 0 30 31 33 0 34 36 0 38 0 40 41 0 44 46 0 48 49 0 51 0 53 55 0 57 0 59 0 61 63 0 65 0 68 0 69 0 72 0 74 75 0 0 78 80 0 0 82 0 85 0 88 0 90 0 91 0 93 0 96 0 98 0 100 0 101 0 104 0 106 0 0 110 0 112 0 114 115 0 0 118 119 0 0 122 123 0 0 125 0 126 0 0 131 0 0 134 134 0 0 139 0 0 139 0 0 144 0 146 0 0 0 150 0 152 0 0 156 158 156 0 0 160 0 0 0 0 0 0 0 174 169 172 0 0 174 0 0 0 0 0 181 0 184 0 183 0 0 188 0 188 191 0 0 0 196 0 0 0 0 0 200 0 205 0 0 0 0 0 210 0 214 0 0 0 220 0 0 0 0 0 0 0 0 0 0 0 0 229 0 0 0 0 0 0 0 0 0 0 243 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0,0.791413,
推荐阅读
- node.js - console.log 为某些字段返回“对象”，而不是正文数据 [spotify-web-api-node]
- c - 如何在整数数组参数前添加空格？
- jenkins - 从共享库调用函数不起作用
- c - 如果 Spy++ 正在运行，为什么 Qt 应用程序只创建窗口句柄？
- javascript - node.js 中的 SOAP 请求
- javascript - 纯js下拉菜单中的搜索结果
- android - Android 应用漏洞 - HostnameVerifier，不在代码库中的任何位置
- delphi - 使用 Delphi 消息队列避免重复触发昂贵的操作
- ruby-on-rails - 将现有数据库中的数据播种到 Rails 中的新数据库
- firebase - Firestore UID 会被劫持吗？