读取JSON文件并将其格式化为CSV

Question

我必须读取一个json文件并提取数据以生成一个CSV文件。你知道吗

服务器是Redhat7，python是Python2.7.5

import time
import os
import sys
import json

with open('abcdc04_abcd11_ig_Host_metrics.json') as data_file:
    data = json.load(data_file)


with open('abcdc04_abcd11_ig_Host_metrics.txt', 'w') as f:

    for row in data:
        symmetrixID= row['symmetrixID']
        HostID= row['HostID']
        HostMBReads= row['HostMBReads']
        timestamp= row['timestamp']
        joined = ",".join([symmetrixID , HostID, HostMBReads , timestamp])
        f.write(joined)

结果是：

Traceback (most recent call last):
  File "./json_scv", line 23, in <module>
    symmetrixID= row['symmetrixID']
TypeError: string indices must be integers

我的输入json文件如下：

{
  "symmetrixID": "000123401234",
  "HostID": "jupiter_ig",
  "perf_data": [
    {
      "HostMBReads": 0.00024720083,
      "timestamp": 1553637300000,
      "Writes": 0.0,
      "ReadResponseTime": 0.15273508,
      "Reads": 0.06328341,
      "WriteResponseTime": 0.0,
      "ResponseTime": 0.15273508,
      "SyscallCount": 0.09326678,
      "HostMBWrites": 0.0,
      "HostIOs": 0.06328341,
      "MBs": 0.00024720083
    },
    {
      "HostMBReads": 0.0004939684,
      "timestamp": 1553637600000,
      "Writes": 0.0,
      "ReadResponseTime": 0.15828949,
      "Reads": 0.1264559,
      "WriteResponseTime": 0.0,
      "ResponseTime": 0.15828949,
      "SyscallCount": 0.123128116,
      "HostMBWrites": 0.0,
      "HostIOs": 0.1264559,
      "MBs": 0.0004939684
    },
    {
      "HostMBReads": 0.0,
      "timestamp": 1553637900000,
      "Writes": 0.0,
      "ReadResponseTime": 0.0,
      "Reads": 0.0,
      "WriteResponseTime": 0.0,
      "ResponseTime": 0.0,
      "SyscallCount": 0.2,
      "HostMBWrites": 0.0,
      "HostIOs": 0.0,
      "MBs": 0.0
    }
  ],
  "reporting_level": "Host"
}

我希望csv格式如下所示：

SymmID,HostName,TimeStamp,HostIOs,HostMBs,ResponseTime,Reads,Writes,HostMBReads,HostMBWrites,ReadResponseTime,WriteResponseTime SyscallCount
000123401234,jupiter_ig,1553637600000,0.12666667,0.000494792,0.15257895,0.12666667,0,0.000494792,0,0.15257895,0,0.21333334
000123401234,jupiter_ig, 1553637600000,0.1264559,0.000493968,0.15828949,0.1264559,0,0.000493968,0,0.15828949,0,0.123128116
000123401234,jupiter_ig,1553637600000,0 ,0,0,0,0,0,0,0,0,0.2

Answer 1

名为data的变量最终应该是一个字典，而不是一个列表。因此，当您尝试执行“for row in data:”时，您说的是“对字典中的每个键执行以下操作”，而不是对列表中的项执行以下操作！字典没有排序，但是不管哪个键首先被选为row，命令都会失败，因为它在其中找不到任何名为“symmetrixID”的东西。例如，如果HostID是循环中拾取的第一个键，那么row['symmetrixID']表示data['HostID']['symmetrixID']。你知道吗

如果你仔细看，字典里只有一个列表可以反复浏览，那就是data["perf_data"]。所以试试那里的循环。你知道吗

所以暂时把你的数据串起来：

s = """
{
  "symmetrixID": "000123401234", 
  "HostID": "jupiter_ig", 
  "perf_data": [
    {
      "HostMBReads": 0.00024720083, 
      "timestamp": 1553637300000, 
      "Writes": 0.0, 
      "ReadResponseTime": 0.15273508, 
      "Reads": 0.06328341, 
      "WriteResponseTime": 0.0, 
      "ResponseTime": 0.15273508, 
      "SyscallCount": 0.09326678, 
      "HostMBWrites": 0.0, 
      "HostIOs": 0.06328341, 
      "MBs": 0.00024720083
    }, 
    {
      "HostMBReads": 0.0004939684, 
      "timestamp": 1553637600000, 
      "Writes": 0.0, 
      "ReadResponseTime": 0.15828949, 
      "Reads": 0.1264559, 
      "WriteResponseTime": 0.0, 
      "ResponseTime": 0.15828949, 
      "SyscallCount": 0.123128116, 
      "HostMBWrites": 0.0, 
      "HostIOs": 0.1264559, 
      "MBs": 0.0004939684
    }, 
    {
      "HostMBReads": 0.0, 
      "timestamp": 1553637900000, 
      "Writes": 0.0, 
      "ReadResponseTime": 0.0, 
      "Reads": 0.0, 
      "WriteResponseTime": 0.0, 
      "ResponseTime": 0.0, 
      "SyscallCount": 0.2, 
      "HostMBWrites": 0.0, 
      "HostIOs": 0.0, 
      "MBs": 0.0
    }
  ], 
  "reporting_level": "Host"
}
"""

下面是我如何将数据格式化的：

import json
data = json.loads(s)

symmetrixID= data['symmetrixID']
HostID= data['HostID']
for row in data['perf_data']:
    HostMBReads = row['HostMBReads']
    timestamp = row['timestamp']
    joined = ",".join([str(c) for c in [symmetrixID, HostID, HostMBReads, timestamp]])
    print(joined)

注意，我更改了joined表达式。如果不首先将所有这些浮点值都更改为字符串，join将不起作用。无论如何，您可以用所需的写入命令替换print命令。你知道吗