我有一个sql查询,它返回以下格式的数据集
user_id, type_id, avg
1, 3, 2.5
1, 2, 3.0
1, 5, 4.6
1, 11, 3.4
2, 2, 4.5
2, 3, 3.0
2, 11, 3.1
上面的数据来自下面的查询,执行时是一个非常大的表。
select u.user_id, t.type_id, sum(u.preference)/count(u.preference)
from user_preference u, item_type_pairs t
where t.item_id = u.item_id group by u.user_id, t.type_id;
查询耗时10分钟,返回2条以上的记录。我的最终目标是将其放入数据帧中,其中行为user_id
,列表示type_id
,每个单元格表示type_id
项的avg
值。
type_id_1, type_id_2, type_id_3
u1| 3.0 2.5
u2| 4.5 3.0
在这件事上最好的办法是什么。我还在想?我应该逐行读取并以某种方式填充数据帧吗?
我假设您能够创建一个MySql连接对象,使用类似于的东西
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
然后,将您的数据导入python非常简单:
with con:
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute(
"select u.user_id, t.type_id, sum(u.preference)/count(u.preference)"
"from user_preference u, item_type_pairs t"
"where t.item_id = u.item_id group by u.user_id, t.type_id;"
)
rows = cur.fetchall()
此时rows
将看起来像:
[{'user_id': 1, 'type_id': 2, 'avg': 2.5},
{'user_id': 1, 'type_id': 2, 'avg': 3.0},
...]
从这一步开始,从这些数据创建pandas数据帧非常简单:
import pandas as pd
import numpy as np
my_df = pd.DataFrame(rows)
然后,您可以使用pivot_table
函数将其转换为您想要的输出:
final_df = pd.pivot_table(
df,
index='user_id',
columns='type_id',
values='avg',
agg_func=np.average
)