大查询页签的API请求

Question

我面临一个问题，关于如何使用python修改JSON文件

JSON请求：

{
    "reports": [{
            "data": {
                "rows": [{
                        "metrics": [{
                                "values": ["27.8", "4", "4", "6.95", "1.0", "0.0", "3.8834951456310676"]
                            }
                        ],
                        "dimensions": ["TEST1", "20180725"]
                    }, {
                        "metrics": [{
                                "values": ["75.0", "12", "12", "6.25", "1.0", "0.0", "3.4782608695652173"]
                            }
                        ],
                        "dimensions": ["TEST2", "20180725"]
                    }
                ],
                "maximums": [{
                        "values": ["1665.0", "140", "126", "65.0", "3.0", "0.0", "50.0"]
                    }
                ],
                "minimums": [{
                        "values": ["0.0", "0", "0", "0.0", "0.0", "0.0", "0.0"]
                    }
                ],
                "isDataGolden": true,
                "totals": [{
                        "values": ["27045.99", "3274", "2831", "8.260839951130116", "1.1564818085482163", "0.0", "4.949387227049424"]
                    }
                ],
                "rowCount": 358
            },
            "columnHeader": {
                "dimensions": ["ga:productName", "ga:date"],
                "metricHeader": {
                    "metricHeaderEntries": [{
                            "type": "CURRENCY",
                            "name": "ga:itemRevenue"
                        }, {
                            "type": "INTEGER",
                            "name": "ga:itemQuantity"
                        }, {
                            "type": "INTEGER",
                            "name": "ga:uniquePurchases"
                        }, {
                            "type": "CURRENCY",
                            "name": "ga:revenuePerItem"
                        }, {
                            "type": "FLOAT",
                            "name": "ga:itemsPerPurchase"
                        }, {
                            "type": "CURRENCY",
                            "name": "ga:productRefundAmount"
                        }, {
                            "type": "PERCENT",
                            "name": "ga:buyToDetailRate"
                        }
                    ]
                }
            }
        }
    ]
}

寻找：

基于“维度”和“度量基准”的矩阵值

修改（或重新创建）报告的干净方法是什么

LINE1 - {"ga:productName": "NAME","ga:date": "NAME","ga:itemRevenue": "value1", "ga:itemQuantity": "value2", ... }
LINE2 - {"ga:productName": "NAME","ga:date": "NAME","ga:itemRevenue": "value1", "ga:itemQuantity": "value2", ... }

编辑1:

{
"ga:productName": "NAME", #from dimension 
"ga:date": "NAME", #from dimension 
"ga:itemRevenue": "value1", #from metricHeaderEntries 
"ga:itemQuantity": "value2", #from metricHeaderEntries 
... 
}
{
"ga:productName": "NAME2", #from dimension 
"ga:date": "NAME2", #from dimension 
"ga:itemRevenue": "value3", #from metricHeaderEntries 
"ga:itemQuantity": "value4", #from metricHeaderEntries 
... 
}

以这种方式工作的价值：

"metrics": [{"values": ["27.8", "4", "4", "6.95", "1.0", "0.0","3.8834951456310676"] #headers in metricHeaderEntries 
"dimensions": ["TEST1", "20180725"] #header in dimension 

或者类似的（我对总数等不感兴趣）

寻找解决方案/样品/解释如何做到这一点，与BQ将接受它的方式

额外：

我了解从JSON请求获取数据的方法，例如：

responce[][][]

但这种情况对我来说太棘手了

示例：

理想情况下，这张桌子应该是这样的 THIS IS HOW IDEALLY TABLE SHOULD LOOK LIKE

这是谷歌提供的打印数据的方式（但需要将其转换为我上面解释的格式）

def print_response(response):
  for report in response.get('reports', []):
    columnHeader = report.get('columnHeader', {})
    dimensionHeaders = columnHeader.get('dimensions', [])
    metricHeaders = columnHeader.get('metricHeader', {}).get('metricHeaderEntries', [])

    for row in report.get('data', {}).get('rows', []):
      dimensions = row.get('dimensions', [])
      dateRangeValues = row.get('metrics', [])

      for header, dimension in zip(dimensionHeaders, dimensions):
        print header + ': ' + dimension

      for i, values in enumerate(dateRangeValues):
        print 'Date range: ' + str(i)
        for metricHeader, value in zip(metricHeaders, values.get('values')):
          print metricHeader.get('name') + ': ' + value

Answer 1

所以你的编辑仍然和你的JSON不匹配；在维度中，您有一个值列表，而不是key:value 你知道吗

"dimensions": [
          "ga:productName",
          "ga:date"
        ],

这意味着你没有任何价值，所以你的例子是不正确的。 在“metricHeaderEntries”中，您有以下内容：

"metricHeader": {
          "metricHeaderEntries": [
            {
              "type": "CURRENCY",
              "name": "ga:itemRevenue"
            },
            {
              "type": "INTEGER",
              "name": "ga:itemQuantity"
            },
            {
              "type": "INTEGER",
              "name": "ga:uniquePurchases"
            },
            {
              "type": "CURRENCY",
              "name": "ga:revenuePerItem"
            },
            {
              "type": "FLOAT",
              "name": "ga:itemsPerPurchase"
            },
            {
              "type": "CURRENCY",
              "name": "ga:productRefundAmount"
            },
            {
              "type": "PERCENT",
              "name": "ga:buyToDetailRate"
            }
          ]
        }

因此，即使是这种情况也与您的示例不匹配，因为在“metricHeaderEntries”下，您没有示例中显示的任何“ga:itemRevenue”或“ga:itemQuantity”值

在任何情况下，您都可以使用与python字典相同的方式来实现JSON，因此，对于dictionary，您可以按键选择元素；对于list，您可以按索引选择元素

如果我有时间的话，我会从你的例子中取一些值来解决你的问题，即使你给出的节点不是正确的

回答：

我解决了你的问题，即使我硬编码的键值，而不是从原始的JSON只是为了让你了解它的工作原理；请让我知道您是否期望：

new_list=[]

l=a["reports"][0]["data"]["rows"]#get to "rows" key from a, where a is your JSON readed as dictionary
for i in l:#iterate rows key for search the needed values for each lines
    dict_line={}#create a dictionary for each line
    dict_line["ga:productName"]=i["dimensions"][0]#add to the dictionary dict_lineth key ga:productName and the product name as value
    dict_line["ga:date"]=i["dimensions"][1]#add to the dictionary dict_lineth key ga:date and the product date as value
    j= (i["metrics"][0]['values'])#for each product line I create a key node and value
    dict_line["ga:itemRevenue"]=j[0]
    dict_line["ga:itemQuantity"]=j[1]
    dict_line["ga:uniquePurchases"]=j[2]
    dict_line["ga:revenuePerIte"]=j[3]
    dict_line["ga:itemsPerPurchase"]=j[4]
    dict_line["ga:productRefundAmount"]=j[5]
    dict_line["ga:buyToDetailRate"]=j[6]
    new_list.append(dict_line)

print (new_list)

结果如下：

[
  {
    "ga:productName": "TEST1",
    "ga:itemRevenue": "27.8",
    "ga:uniquePurchases": "4",
    "ga:date": "20180725",
    "ga:revenuePerIte": "6.95",
    "ga:productRefundAmount": "0.0",
    "ga:itemQuantity": "4",
    "ga:itemsPerPurchase": "1.0",
    "ga:buyToDetailRate": "3.8834951456310676"
  },
  {
    "ga:productName": "TEST2",
    "ga:itemRevenue": "75.0",
    "ga:uniquePurchases": "12",
    "ga:date": "20180725",
    "ga:revenuePerIte": "6.25",
    "ga:productRefundAmount": "0.0",
    "ga:itemQuantity": "12",
    "ga:itemsPerPurchase": "1.0",
    "ga:buyToDetailRate": "3.4782608695652173"
  }
]