python - Pandas 将字典列表(GA 输出)转换为有意义的数据帧

Question

我一直在与这个逻辑作斗争。我正在以这种格式从谷歌分析中获取每个用户每个站点的数据。 (因此，这些是一位用户在该网站上执行的所有事件)我无法更改接收数据的格式。

问题:我正在对所有用户运行一个循环，并为每个用户获取此输出。我想将这些数据放入数据框中以便以后使用。我遇到的问题是 'activities':[{.....},{......}] 部分，我无法弄清楚以有意义的方式存储所有这些数据。

{'sampleRate': 1,
 'sessions': [{'activities': [{'activityTime': '2020-01-08T16:00:44.399101Z',
                               'activityType': 'PAGEVIEW',
                               'campaign': '(not set)',
                               'channelGrouping': 'Direct',
                               'customDimension': [{'index': 1}],
                               'hostname': 'company.domain.com',
                               'keyword': '(not set)',
                               'landingPagePath': '/login',
                               'medium': '(none)',
                               'pageview': {'pagePath': '/thepath',
                                            'pageTitle': 'thecurrentwebpage'},
                               'source': '(direct)'},
                              {'activityTime': '2020-01-08T15:58:43.077293Z',
                               'activityType': 'PAGEVIEW',
                               'campaign': '(not set)',
                               'channelGrouping': 'Direct',
                               'customDimension': [{'index': 1}],
                               'hostname': 'company.domain.com',
                               'keyword': '(not set)',
                               'landingPagePath': '/login',
                               'medium': '(none)',
                               'pageview': {'pagePath': '/theotherpath',
                                            'pageTitle': 'thecurrentwebpage'},
                               'source': '(direct)'}],
               'dataSource': 'web',
               'deviceCategory': 'desktop',
               'platform': 'Windows',
               'sessionDate': '2020-01-08',
               'sessionId': '1578491x03d'},
              {'activities': [{'activityTime': '2019-12-28T21:58:48.993944Z',
                               'activityType': 'PAGEVIEW',
                               'campaign': '(not set)',.....

预期输出:

每个用户的数据都存储在按如下方式组织的表中:

如果图片中存在一些逻辑错误，我很乐意更改我所拥有的。我只需要数据即可工作。

PS:我需要在 LucidChart 中使用 SQL 和 ERD，我以前从未以这种格式操作数据。任何帮助，将数据(其结构类似于上面的示例)放入数据帧中。

编辑:

两种不同类型事件的示例(事件始终分类为“页面浏览”或“事件”):

{'activityTime':
                               # Pageview activity
                               '2020-01-08T15:48:38.012671Z',
                               'activityType': 'PAGEVIEW',
                               'campaign': '(not set)',
                               'channelGrouping': 'Direct',
                               'customDimension': [{'index': 1}],
                               'hostname': 'company.domain.com',
                               'keyword': '(not set)',
                               'landingPagePath': '/login',
                               'medium': '(none)',
                               'pageview': {'pagePath': '/login',
                                            'pageTitle': 'titleofthepage'},
                               'source': '(direct)'},

                              # Event activity
                              {'activityTime': '2020-01-08T15:48:37.915105Z',
                               'activityType': 'EVENT',
                               'campaign': '(not set)',
                               'channelGrouping': 'Direct',
                               'customDimension': [{'index': 1}],
                               'event': {'eventAction': 'Successfully Logged '
                                                        'In',
                                         'eventCategory': 'Auth',
                                         'eventCount': '1',
                                         'eventLabel': '(not set)'},
                               'hostname': 'company.domain.com',
                               'keyword': '(not set)',
                               'landingPagePath': '/login',
                               'medium': '(none)',
                               'source': '(direct)'}]

Answer 1

例如，您可以这样做:

import pandas as pd
import json
str = """{"sampleRate": 1,
 "sessions": [{"activities": [{"activityTime": "2020-01-08T16:00:44.399101Z",
                               "activityType": "PAGEVIEW",
                               "campaign": "(not set)",
                               "channelGrouping": "Direct",
                               "customDimension": [{"index": 1}],
                               "hostname": "company.domain.com",
                               "keyword": "(not set)",
                               "landingPagePath": "/login",
                               "medium": "(none)",
                               "pageview": {"pagePath": "/thepath",
                                            "pageTitle": "thecurrentwebpage"},
                               "source": "(direct)"},
                              {"activityTime": "2020-01-08T15:48:37.915105Z",
                               "activityType": "EVENT",
                               "campaign": "(not set)",
                               "channelGrouping": "Direct",
                               "customDimension": [{"index": 1}],
                               "event": {"eventAction": "Successfully Logged In",
                                         "eventCategory": "Auth",
                                         "eventCount": "1",
                                         "eventLabel": "(not set)"},
                               "hostname": "company.domain.com",
                               "keyword": "(not set)",
                               "landingPagePath": "/login",
                               "medium": "(none)",
                               "source": "(direct)"}],
               "dataSource": "web",
               "deviceCategory": "desktop",
               "platform": "Windows",
               "sessionDate": "2020-01-08",
               "sessionId": "1578491x03d"}]}"""


data = json.loads(str)

session_keys = "sessionId,dataSource,deviceCategory,platform,sessionDate,DB_id".split(",")
event_keys = "activityTime,eventCategory,eventCount,eventLabel,eventAction".split(",")
pageview_keys = "activityTime,pageTitle,pagePath".split(",")

sessions = {k:[] for k in session_keys}
events = {k:[] for k in event_keys}
pageviews = {k:[] for k in pageview_keys}
activities = {"sessionId":[],"activityTime":[]}

for session in data["sessions"]:
    for easy_key in session_keys[:5]:
        sessions[easy_key] += [session[easy_key]]
    for activity in session["activities"]:
        activity_time = activity["activityTime"]
        activities["sessionId"] += [session["sessionId"]]
        activities["activityTime"] += [activity_time]
        if activity["activityType"] == "PAGEVIEW":
            pageviews["activityTime"] += [activity_time]
            pageviews["pageTitle"] += [activity["pageview"]["pageTitle"]]
            pageviews["pagePath"] += [activity["pageview"]["pagePath"]]
        elif activity["activityType"] == "EVENT":
            events["activityTime"] += [activity_time]
            events["eventAction"] += [activity["event"]["eventAction"]]
            events["eventCategory"] += [activity["event"]["eventCategory"]]
            events["eventCount"] += [activity["event"]["eventCount"]]
            events["eventLabel"] += [activity["event"]["eventLabel"]]
        else:
            print("Unknown Activity: {}".format(activity["activityType"]))

    sessions["DB_id"] += [0]

df_session = pd.DataFrame.from_dict(sessions)
df_session.set_index('sessionId', inplace=True)
df_event = pd.DataFrame.from_dict(events)
df_event.set_index('activityTime', inplace=True)
df_pageview = pd.DataFrame.from_dict(pageviews)
df_pageview.set_index('activityTime', inplace=True)
df_activities = pd.DataFrame.from_dict(activities)

输出每个DF:

#df_session:

            dataSource deviceCategory platform sessionDate  DB_id
sessionId                                                        
1578491x03d        web        desktop  Windows  2020-01-08      0



#df_activities:
     sessionId                 activityTime
0  1578491x03d  2020-01-08T16:00:44.399101Z
1  1578491x03d  2020-01-08T15:48:37.915105Z



#df_event:
                            eventCategory eventCount eventLabel             eventAction
activityTime                                                                           
2020-01-08T15:48:37.915105Z          Auth          1  (not set)  Successfully Logged In



#df_pageview:
                                     pageTitle  pagePath
activityTime                                            
2020-01-08T16:00:44.399101Z  thecurrentwebpage  /thepath

输出示例连接

#As example for a join, I only want the event data
df_sa = df_activities.join(df_session, on="sessionId").join(df_event,on="activityTime",how="right") 

print(df_sa)

     sessionId                 activityTime dataSource deviceCategory platform sessionDate  DB_id eventCategory eventCount eventLabel             eventAction
1  1578491x03d  2020-01-08T15:48:37.915105Z        web        desktop  Windows  2020-01-08      0          Auth          1  (not set)  Successfully Logged In

架构

与您上面指定的相同，但有 2 处更改:

1. 表 session 不再有列事件。

2. 表 Activity 有一个附加列 sessionId。

SQL

如何在 pandas Dataframe 中执行 SQL，您可以在网上查找，这里可能有很多内容需要介绍。例如，请参见此处:Executing an SQL query over a pandas dataset

如何获取数据

一些例子:(但如果你想要具体的东西，最后你必须自己弄清楚，我在这里不开设 SQL 类(class))

• 如果您只需要 session 数据:查询df_session
• 如果你想要全部事件:查询df_event和df_pageview
• 如果你想要全部事件并与 session 相结合:加入 df_session withdf_activities 然后加入 df_event 和 df_pageview

我不需要 Dataframe...我需要 MYSQL 数据库(或其他东西)

没有比这更容易的了。数据框采用“正确”的数据库格式。

session 示例:

for index, row in df_sessions.iterrows():
    # for event and pageview the index would be activityTime
    # the df activities don't have a specific index
    sessionId = index 
    dataSource = row['dataSource']
    deviceCategory = row['deviceCategory']
    platform = row['platform']
    sessionDate = row['sessionDate']
    DB_id = row['DB_id']
    # function to save a row in a SQL DB basically:
    # INSERT INTO session (sessionId,dataSource,deviceCategory,platform,sessionDate,DB_id) VALUES(x,x,x,x,x,x)
    save_to_sql(sessionId,dataSource,deviceCategory,platform,sessionDate,DB_id)

save_to_sql 是您自己的实现，具体取决于您使用的数据库。向您解释这一点不适合这个问题。

评论

1. DB_id 不知道该值的来源。我把它设置为0。