将 Google Analytics API 转换为 CSV [英] Google Analytics API into CSV

问题描述

我正在尝试为我的 Google Analytics 帐户构建 API，以将数据导出为 CSV.我的身份验证代码可以工作，但我现在正在努力以我想要的格式打印数据.

I am trying to build an API for my Google Analytics Account to export the data into a CSV. I have the Authentication code working, but I am struggling with now printing the data in the format I would like.

暂时我只拉维度国家、维度城市和度量会话.(但是，当我开始工作时，这些会改变.)现在，它会打印:

For the time being, I am only pulling dimension country, dimension city, and metric session. (However these will change when I get this working.) Right now, it prints:

Date Range(0)
ga:sessions: 2
ga:country:United States
ga:city:Los Angeles
...

但是，我想把它排成一行:

However, I would like to have this in a line:

date Range   sessions    country     city
0            2           USA         Los Angeles
...

我需要使用 Python 中的哪些代码?下面是我所拥有的.

What code in Python do I need to use? Below is what I have.

def initialize_analyticsreporting():
  parser = argparse.ArgumentParser(
      formatter_class=argparse.RawDescriptionHelpFormatter,
      parents=[tools.argparser])
  flags = parser.parse_args([])

http = credentials.authorize(httplib2.Http())
service = build('analytics', 'v4', http=http, discoveryServiceUrl=('https://analyticsreporting.googleapis.com/$discovery/rest'))


def get_report(service):
    return service.reports().batchGet(
        body={
            'reportRequests':[
                {
                   "viewId": "ga:52783868",
                   "dimensions": [{
                         "name": "ga:country"},
                        {"name": "ga:city"}],

                    "metrics": [{
                         "expression": "ga:sessions"}],
                     "dateRanges": [{
                         "startDate": "2017-04-10",
                         "endDate": "2017-04-12"}]
                }
            ]
        }
    ).execute()

countries = []
cities = []
val = []

def print_reponse(response):

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

        for row in 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):           
                for metricHeader, value in zip(metricHeaders, values.get('values')):
                    print(metricHeader.get('name')+':'+value)

def main():
    analytics = initialize_analyticsreporting()
    response = get_report(service)
    print_reponse(response)



if __name__ == '__main__':
    main()

推荐答案

按照lrnzcig的建议，我们可以用pandas解析数据，然后导出到csv文件.

As lrnzcig's suggestion, we could parse the data with pandas and then export to csv file.

首先导入pandas和json_normalize

import pandas as pd
from pandas.io.json import json_normalize

使用该函数解析数据

def parse_data(response):

  reports = response['reports'][0]
  columnHeader = reports['columnHeader']['dimensions']
  metricHeader = reports['columnHeader']['metricHeader']['metricHeaderEntries']

  columns = columnHeader
  for metric in metricHeader:
    columns.append(metric['name'])

  data = json_normalize(reports['data']['rows'])
  data_dimensions = pd.DataFrame(data['dimensions'].tolist())
  data_metrics = pd.DataFrame(data['metrics'].tolist())
  data_metrics = data_metrics.applymap(lambda x: x['values'])
  data_metrics = pd.DataFrame(data_metrics[0].tolist())
  result = pd.concat([data_dimensions, data_metrics], axis=1, ignore_index=True)

  return result

结果看起来像......

The result will look like ...

      ga:country         ga:city    ga:sessions
0      (not set)       (not set)             64
1      Argentina       (not set)              1
2      Australia        Adelaide              3
3      Australia        Brisbane              9
...

最后调用函数 to_csv 将数据保存为 csv 文件

Finally, call function to_csv to save data as csv file

result.to_csv('result.csv')

这篇关于将 Google Analytics API 转换为 CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！