使用python处理每个用户一行中的数据

Question

我正在从谷歌分析自定义报告生成的csv文件中收集用户数据，一些用户属性每个用户都有多个值。我在这里的目标是获取用户数据，并将与一个用户相关的所有数据放入一行。

示例：

users = [
  { id:1, name: 'user1'},
  { id:2, name: 'user2'},
  { id:3, name: 'user3'}
 ]

 products_purchased = [
  {id:1 , product_purchased: 'sardines'},
  {id:1, product_purchased: 'shoes'},
  {id:2 , product_purchased: 'fish'},
  {id:2, product_purchased: 'chicken'},
  {id:3 , product_purchased: 'eggs'},
  {id:3, product_purchased: 'chicken'},
]

我正在尝试重新排列机器学习的数据，如下所示：

users = [
  { id:1, name: 'user1', product_purchased-1: 'sardines',
    product_purchased-2: 'shoes'},
  { id:2, name: 'user2', product_purchased-1:'fish',
    product_purchased-2: 'chicken' },
  { id:3, name: 'user3', product_purchased-1: 'eggs', 
    product_purchased-2: 'chicken'}
 ]

以下是我的Python代码：

import csv


processed = []
columns = ['id', 'username', 'country','city','region','event-1','event-2','event-3',
'event-4', 'event-5','event-6','event-7','event-8','event-9','event-10','product-1',
'product-2','product-3','product-4','product-5','product-6','product-7','product-8',
'product-9','product-10','page-1','page-2','page-3','page-4','page-5','Sessions with Event',
'Total Events','Adding a product on to the cart (Goal 4 Conversion Rate)',
'Adding a product on to the cart (Goal 4 Completions)']
i = 0
#columns =[] 'ID', 'WP Username' , country, city, region, event action 1 (10 actions), products (multiple), 'Sessions with Event',Total Events, Adding a product on to the cart (Goal 4 Conversion Rate),
# Adding a product on to the cart (Goal 4 Completions)

# Completed the main dimentions of the GA data
# getting details per unique user
with open('users.csv') as users_data:
    user_dict = csv.DictReader(users_data)
    users = list(user_dict)

    for user in users:
        processed.append({
            'id': user['ID'],
            'username': user['WordPress_Username'],
            'country':user['Country'],
            'city':user['City'],
            'region':user['Region']
        })

with open('events.csv') as events_data:
    events_dict = csv.DictReader(events_data)
    events = list(events_dict)

    for p in processed:
        for event in events:
            i += 1
            if p['id'] == event['ID']:
                p['event-' + str(i)] = event['Event Action']
            else:
                i = 0

with open('products.csv') as products_data:
    products_dict = csv.DictReader(products_data)
    products = list(products_dict)

    for p in processed:
        for product in products:
            i += 1
            if p['id'] == product['ID']:
                p['product-' + str(i)] = product['Product ID']
            else:
                i = 0

with open('pages.csv') as page_visited:
    pages_dict = csv.DictReader(page_visited)
    pages = list(pages_dict)

    for p in processed:
        for page in pages:
            i +=1
            if p['id'] == page['ID']:
                 p['page-' + str(i)] = page['Page']
            else:
                i = 0


for p in processed:
    for user in users:
        p['Sessions with Event'] = user['Sessions with Event']
        p['Total Events'] = user['Total Events']
        p['Adding a product on to the cart (Goal 4 Conversion Rate)'] = user['Adding a product on to the cart (Goal 4 Conversion Rate)']
        p['Adding a product on to the cart (Goal 4 Completions)'] = user[ 'Adding a product on to the cart (Goal 4 Completions)']
    for event in events:
        if p['id'] == event['ID']:
            p['Sessions'] = event['Sessions']
    for page in pages:
        if p['id'] == page['ID']:
            p['id'] = page['Pages / Session']



try:
    with open('data.csv', 'w') as data:
        writer = csv.DictWriter(data, fieldnames=columns)
        writer.writeheader()
    for p in processed:
        writer.writerow(p)
except IOError:
    print("I/O error") 

我想知道我的代码出了什么问题，或者是一个也可以实现我正在寻找的东西的替代方案。我以前尝试过Google Data Studio，在GA中似乎有一些选项可以让我这样做。

我打算稍后使用scikit学习对数据进行聚类，这就是为什么我要格式化数据以创建pandas数据框架。

出于好奇:数据透视表可以用来创建数据帧吗？这种格式是否适用于scikit learn？

更新:我修复了事件“会话”行的括号问题。BUt现在我得到以下错误：

  File "data_processing.py", line 87, in <module>
    writer.writerow(p)
  File "/usr/lib/python2.7/csv.py", line 152, in writerow
    return self.writer.writerow(self._dict_to_list(rowdict))
ValueError: I/O operation on closed file

Answer 1

我要感谢所有回答我问题的人！非常感谢@xbello和@G.Anderson。正如他们都指出的那样，问题是我在第75行有一个括号(“[event‘’Sessions‘”)，并且在最后的with open语句中没有适当缩进。