英文:
Python read from JSON file value and write to csv modify some values
问题
以下是您提供的代码的翻译部分：
# 打开 JSON 文件并将数据加载到变量中
with open('personal.json') as json_file:
data = json.load(json_file)
employee_data = data['results']
print(inventory_data)
data_file = open('data_file.csv', 'a')
# 创建 CSV 写入器对象
csv_writer = csv.writer(data_file)
count = 0
for emp in employee_data:
if count == 0:
header = emp.keys()
csv_writer.writerow(header)
count += 1
csv_writer.writerow(emp.values())
data_file.close()
JSON 示例：
{
"count": 1100,
"next": "https://any.com/?limit=10&offset=10",
"previous": null,
"results": [
{
"list_no": "00011",
"item_no": "MZVL2256HC",
"upc_code": "",
"manufacturer_no": "MZVL2256HCHQ-00",
"manufacturer": "SAMSUNGOEM",
"category": "SSD",
"product_name": "Samsung PM9A1 - SSD - 256 GB - PCIe 4.0 x4 (NVMe)",
"price": 56.65,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.06,
"unit": 300,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "N",
"inventory": {
"": 50
}
},
// 其他结果...
]
}
关于您的问题，您想要在CSV文件中的“inventory”列中显示合并后的值，您可以使用以下方法来实现：
for emp in employee_data:
if count == 0:
header = emp.keys()
csv_writer.writerow(header)
count += 1
# 处理inventory列
inventory_values = emp['inventory'].values()
inventory_sum = sum(inventory_values)
emp['inventory'] = inventory_sum
csv_writer.writerow(emp.values())
通过这种方式，您将在CSV文件中使用合并后的值来替代原始的inventory字典。希望这有助于解决您的问题。
英文:
I have a JSON file loaded and written to CSV in this way:
# Opening JSON file and loading the data
# into the variable data
with open('personal.json') as json_file:
data = json.load(json_file)
employee_data = data['results']
#inventory_data = data['inventory']
print (inventory_data)
#print(f'Total users: {res.json().get("total")}')
# now we will open a file for writing
data_file = open('data_file.csv', 'a')
# create the csv writer object
csv_writer = csv.writer(data_file)
# Counter variable used for writing
# headers to the CSV file
count = 0
for emp in employee_data:
if count == 0:
# Writing headers of CSV file
header = emp.keys()
csv_writer.writerow(header)
count += 1
# Writing data of CSV file
csv_writer.writerow(emp.values())
data_file.close()
Works perfect there is no problem at all. This is the example of some JSON file created by this:
{
"count": 1100,
"next": "https://any.com/?limit=10&offset=10",
"previous": null,
"results": [
{
"list_no": "00011",
"item_no": "MZVL2256HC",
"upc_code": "",
"manufacturer_no": "MZVL2256HCHQ-00",
"manufacturer": "SAMSUNGOEM",
"category": "SSD",
"product_name": "Samsung PM9A1 - SSD - 256 GB - PCIe 4.0 x4 (NVMe)",
"price": 56.65,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.06,
"unit": 300,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "N",
"inventory": {
"": 50
}
},
{
"list_no": "00012",
"item_no": "MZVL2512HC",
"upc_code": "",
"manufacturer_no": "MZVL2512HCJQ-00",
"manufacturer": "SAMSUNGOEM",
"category": "SSD",
"product_name": "Samsung PM9A1 MZVL2512HCJQ - SSD - 512 GB - PCIe 4.0 x4 (NVMe)",
"price": 70.04,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.06,
"unit": 300,
"length": 23.0,
"width": 17.0,
"height": 10.0,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "N",
"inventory": {
"": 55
}
},
{
"list_no": "00013",
"item_no": "MZVL21T0HC",
"upc_code": "",
"manufacturer_no": "MZVL21T0HCLR-00A00",
"manufacturer": "SAMSUNGOEM",
"category": "SSD",
"product_name": "Samsung PM9A1 MZVL21T0HCLR - SSD - 1 TB - PCIe 4.0 x4 (NVMe)",
"price": 105.06,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.06,
"unit": 300,
"length": 23.0,
"width": 17.0,
"height": 10.0,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "N",
"inventory": {
"": 52,
"M": 0
}
},
{
"list_no": "00014",
"item_no": "MZVL22T0HB",
"upc_code": "",
"manufacturer_no": "MZVL22T0HBLB-00A00",
"manufacturer": "SAMSUNGOEM",
"category": "SSD",
"product_name": "Samsung PM9A1 - SSD - 2 TB - PCIe 4.0 x4 (NVMe)",
"price": 187.46,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.06,
"unit": 300,
"length": 23.0,
"width": 17.0,
"height": 10.0,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "N",
"inventory": {
"": 58
}
},
{
"list_no": "00102",
"item_no": "MT48RD64A1",
"upc_code": "649528921666",
"manufacturer_no": "MTC40F2046S1RC48BA1R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 64 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered",
"price": 297.67,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.09,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "Y",
"is_domestic_only": "Y",
"inventory": {
"": 36,
"L": 0
}
},
{
"list_no": "00104",
"item_no": "MT48RD32A1",
"upc_code": "649528921598",
"manufacturer_no": "MTC20F2085S1RC48BA1R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered",
"price": 164.8,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.1,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "N",
"is_domestic_only": "N",
"inventory": {
"": 53
}
},
{
"list_no": "00105",
"item_no": "MT48RS32A2",
"upc_code": "649528921529",
"manufacturer_no": "MTC20F1045S1RC48BA2R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered",
"price": 164.8,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.1,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "N",
"is_domestic_only": "Y",
"inventory": {
"": 38
}
},
{
"list_no": "00108",
"item_no": "MT48RS16A1",
"upc_code": "649528921376",
"manufacturer_no": "MTC10F1084S1RC48BA1R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 16 GB - DIMM 288-pin - 4800 MHz / PC5-38400",
"price": 95.79,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.09,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "N",
"is_domestic_only": "Y",
"inventory": {
"": 364,
"L": 50
}
},
{
"list_no": "00116",
"item_no": "MT48E32A1",
"upc_code": "649528931481",
"manufacturer_no": "MTC20C2085S1EC48BA1R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - unbuffered",
"price": 164.8,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.09,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.65,
"package": "BULK",
"specorder": "N",
"is_domestic_only": "Y",
"inventory": {
"": 32,
"N": 0,
"L": 0
}
},
{
"list_no": "00118",
"item_no": "MT48E16A1",
"upc_code": "649528931429",
"manufacturer_no": "MTC10C1084S1EC48BA1R",
"manufacturer": "MICRON",
"category": "MEM",
"product_name": "Micron - DDR5 - module - 16 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - unbuffered",
"price": 95.79,
"instant_rebate": "",
"instant_rebate_item_no": "",
"weight": 0.09,
"unit": 100,
"length": 14.75,
"width": 12.0,
"height": 3.75,
"package": "BULK",
"specorder": "N",
"is_domestic_only": "Y",
"inventory": {
"": 21
}
}
]
}
The issue I have is with one column created in the csv called inventory I have some values as:
"inventory": {"": 52, "N": 57, "L": 34, "M": 15}
or
"inventory": {"": 21}
Can be clearly seen in the JSON text. What I want is that all instances in inventory to that instances I want to make a sum of all that values appear there and that sum that is the value to send to the column inventory. Why? Because actually it sends me to csv as that I copied literal and they don’ work for the purpose. In primer example the value in inventory would be 52+57+34+15=158 and second example 21 and like that with all values. How can I modify the code to do that?
Thank you
I tried to investigate how to call values. For example:
employee_data = data['results']
This variable reads the values in the JSON file as count, results, etc but after results I don’t know how to call the values as list_no, inventory, etc.
答案1
得分: 0
你可以使用以下代码计算库存的总和并返回data['results']中inventory字典的所有值。然后，你可以通过迭代data['results']中的单个数据i来获取i['list_no']数据。这些代码应该可以工作：
import json
# 打开JSON文件并加载数据到变量data中
with open('personal.json') as json_file:
data = json.load(json_file)
employee_data = data['results']
for i in employee_data:
# 计算库存数据的总和
sumOfInventory = sum(i['inventory'].values())
# 打印一些数据
print('List_No:', i['list_no'])
print('Inventory:', i['inventory'])
print('Sum of Inventory:', sumOfInventory)
print('----------------------')
# 将总和插入JSON数据中
i['sumOfInventory'] = sumOfInventory
# 打印新的JSON数据
print('新的JSON数据:')
print(employee_data)
print('')
控制台输出应该如下所示：
List_No : 00011
Inventory : {'': 50}
Sum of Inventory : 50
----------------------
List_No : 00012
Inventory : {'': 55}
Sum of Inventory : 55
----------------------
List_No : 00013
Inventory : {'': 52, 'M': 0}
Sum of Inventory : 52
----------------------
List_No : 00014
Inventory : {'': 58}
Sum of Inventory : 58
----------------------
List_No : 00102
Inventory : {'': 36, 'L': 0}
Sum of Inventory : 36
----------------------
List_No : 00104
Inventory : {'': 53}
Sum of Inventory : 53
----------------------
List_No : 00105
Inventory : {'': 38}
Sum of Inventory : 38
----------------------
List_No : 00108
Inventory : {'': 364, 'L': 50}
Sum of Inventory : 414
----------------------
List_No : 00116
Inventory : {'': 32, 'N': 0, 'L': 0}
Sum of Inventory : 32
----------------------
List_No : 00118
Inventory : {'': 21}
Sum of Inventory : 21
----------------------
新的JSON数据 :
[{'list_no': '00011', 'item_no': 'MZVL2256HC', 'upc_code': '', 'manufacturer_no': 'MZVL2256HCHQ-00', 'manufacturer': 'SAMSUNGOEM', 'category': 'SSD', 'product_name': 'Samsung PM9A1 - SSD - 256 GB - PCIe 4.0 x4 (NVMe)', 'price': 56.65, 'instant_rebate': '', 'ins...
英文:
You can calculate the sum of inventory with return all values from inventory dict from data['results']. Then you can call list_no data with iterate data['results'] with single data i and make i['list_no'] to get the data. These codes should be works:
import json
# Opening JSON file and loading the data
# into the variable data
with open('personal.json') as json_file:
data = json.load(json_file)
employee_data = data['results']
for i in employee_data:
# calculate sum of Inventory data
sumOfInventory = sum(list(i['inventory'].values()))
# print a few data
print('List_No :', i['list_no'])
print('Inventory :', i['inventory'])
print('Sum of Inventory :', sumOfInventory)
print('----------------------')
# insert that sum into the json data
i['sumOfInventory'] = sumOfInventory
#print new json data
print('new json data :')
print(employee_data)
print('')
#inventory_data = data['inventory']
# print (inventory_data)
#print(f'Total users: {res.json().get("total")}')
# now we will open a file for writing
data_file = open('data_file.csv', 'a')
# create the csv writer object
csv_writer = csv.writer(data_file)
# Counter variable used for writing
# headers to the CSV file
count = 0
for emp in employee_data:
if count == 0:
# Writing headers of CSV file
header = emp.keys()
csv_writer.writerow(header)
count += 1
# Writing data of CSV file
csv_writer.writerow(emp.values())
data_file.close()
The console output must be like this :
List_No : 00011
Inventory : {'': 50}
Sum of Inventory : 50
----------------------
List_No : 00012
Inventory : {'': 55}
Sum of Inventory : 55
----------------------
List_No : 00013
Inventory : {'': 52, 'M': 0}
Sum of Inventory : 52
----------------------
List_No : 00014
Inventory : {'': 58}
Sum of Inventory : 58
----------------------
List_No : 00102
Inventory : {'': 36, 'L': 0}
Sum of Inventory : 36
----------------------
List_No : 00104
Inventory : {'': 53}
Sum of Inventory : 53
----------------------
List_No : 00105
Inventory : {'': 38}
Sum of Inventory : 38
----------------------
List_No : 00108
Inventory : {'': 364, 'L': 50}
Sum of Inventory : 414
----------------------
List_No : 00116
Inventory : {'': 32, 'N': 0, 'L': 0}
Sum of Inventory : 32
----------------------
List_No : 00118
Inventory : {'': 21}
Sum of Inventory : 21
----------------------
new json data :
[{'list_no': '00011', 'item_no': 'MZVL2256HC', 'upc_code': '', 'manufacturer_no': 'MZVL2256HCHQ-00', 'manufacturer': 'SAMSUNGOEM', 'category': 'SSD', 'product_name': 'Samsung PM9A1 - SSD - 256 GB - PCIe 4.0 x4 (NVMe)', 'price': 56.65, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.06, 'unit': 300, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'Y', 'is_domestic_only': 'N', 'inventory': {'': 50}, 'sumOfInventory': 50}, {'list_no': '00012', 'item_no': 'MZVL2512HC', 'upc_code': '', 'manufacturer_no': 'MZVL2512HCJQ-00', 'manufacturer': 'SAMSUNGOEM', 'category': 'SSD', 'product_name': 'Samsung PM9A1 MZVL2512HCJQ - SSD - 512 GB - PCIe 4.0 x4 (NVMe)', 'price': 70.04, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.06, 'unit': 300, 'length': 23.0, 'width': 17.0, 'height': 10.0, 'package': 'BULK', 'specorder': 'Y', 'is_domestic_only': 'N', 'inventory': {'': 55}, 'sumOfInventory': 55}, {'list_no': '00013', 'item_no': 'MZVL21T0HC', 'upc_code': '', 'manufacturer_no': 'MZVL21T0HCLR-00A00', 'manufacturer': 'SAMSUNGOEM', 'category': 'SSD', 'product_name': 'Samsung PM9A1 MZVL21T0HCLR - SSD - 1 TB - PCIe 4.0 x4 (NVMe)', 'price': 105.06, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.06, 'unit': 300, 'length': 23.0, 'width': 17.0, 'height': 10.0, 'package': 'BULK', 'specorder': 'Y', 'is_domestic_only': 'N', 'inventory': {'': 52, 'M': 0}, 'sumOfInventory': 52}, {'list_no': '00014', 'item_no': 'MZVL22T0HB', 'upc_code': '', 'manufacturer_no': 'MZVL22T0HBLB-00A00', 'manufacturer': 'SAMSUNGOEM', 'category': 'SSD', 'product_name': 'Samsung PM9A1 - SSD - 2 TB - PCIe 4.0 x4 (NVMe)', 'price': 187.46, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.06, 'unit': 300, 'length': 23.0, 'width': 17.0, 'height': 10.0, 'package': 'BULK', 'specorder': 'Y', 'is_domestic_only': 'N', 'inventory': {'': 58}, 'sumOfInventory': 58}, {'list_no': '00102', 'item_no': 'MT48RD64A1', 'upc_code': '649528921666', 'manufacturer_no': 'MTC40F2046S1RC48BA1R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 64 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered', 'price': 297.67, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.09, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'Y', 'is_domestic_only': 'Y', 'inventory': {'': 36, 'L': 0}, 'sumOfInventory': 36}, {'list_no': '00104', 'item_no': 'MT48RD32A1', 'upc_code': '649528921598', 'manufacturer_no': 'MTC20F2085S1RC48BA1R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered', 'price': 164.8, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.1, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'N', 'is_domestic_only': 'N', 'inventory': {'': 53}, 'sumOfInventory': 53}, {'list_no': '00105', 'item_no': 'MT48RS32A2', 'upc_code': '649528921529', 'manufacturer_no': 'MTC20F1045S1RC48BA2R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - registered', 'price': 164.8, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.1, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'N', 'is_domestic_only': 'Y', 'inventory': {'': 38}, 'sumOfInventory': 38}, {'list_no': '00108', 'item_no': 'MT48RS16A1', 'upc_code': '649528921376', 'manufacturer_no': 'MTC10F1084S1RC48BA1R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 16 GB - DIMM 288-pin - 4800 MHz / PC5-38400', 'price': 95.79, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.09, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'N', 'is_domestic_only': 'Y', 'inventory': {'': 364, 'L': 50}, 'sumOfInventory': 414}, {'list_no': '00116', 'item_no': 'MT48E32A1', 'upc_code': '649528931481', 'manufacturer_no': 'MTC20C2085S1EC48BA1R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 32 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - unbuffered', 'price': 164.8, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.09, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.65, 'package': 'BULK', 'specorder': 'N', 'is_domestic_only': 'Y', 'inventory': {'': 32, 'N': 0, 'L': 0}, 'sumOfInventory': 32}, {'list_no': '00118', 'item_no': 'MT48E16A1', 'upc_code': '649528931429', 'manufacturer_no': 'MTC10C1084S1EC48BA1R', 'manufacturer': 'MICRON', 'category': 'MEM', 'product_name': 'Micron - DDR5 - module - 16 GB - DIMM 288-pin - 4800 MHz / PC5-38400 - unbuffered', 'price': 95.79, 'instant_rebate': '', 'instant_rebate_item_no': '', 'weight': 0.09, 'unit': 100, 'length': 14.75, 'width': 12.0, 'height': 3.75, 'package': 'BULK', 'specorder': 'N', 'is_domestic_only': 'Y', 'inventory': {'': 21}, 'sumOfInventory': 21}]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论