python-2.7 - 从大型数据库查询填充 Pandas 数据框的值 (Python)

Question

我正在运行一段代码来查询数据库，然后如果查询中存在该元组，则用值 1 填充 pandas 数据帧。它通过运行查询然后迭代元组并填充数据帧来实现这一点。但是，该查询返回近 800 万行数据。

我的问题是是否有人知道如何加快这样的过程。下面是代码:

user_age = pd.read_sql_query(sql_age, datastore, index_col=['userid']).age.astype(np.int, copy=False)
x = pd.DataFrame(0, index=user_age.index, columns=range(366), dtype=np.int8)

for r in pd.read_sql_query(sql_active, datastore, chunksize=50000):
        for userid, day in r.itertuples(index=False):
            x.at[userid, day] = 1

提前谢谢您!

Answer 1

您可以通过替换 Python 循环来节省一些时间

for userid, day in r.itertuples(index=False):
    x.at[userid, day] = 1

使用“高级整数索引”进行 NumPy 数组赋值:

x[npidx[r['userid']], r['day']] = 1

<小时/>

在 80000 行 DataFrame 上，using_numpy(如下)大约快 6 倍:

In [7]: %timeit orig()
1 loop, best of 3: 984 ms per loop

In [8]: %timeit using_numpy()
10 loops, best of 3: 162 ms per loop

<小时/>

import numpy as np
import pandas as pd

def mock_read_sql_query():
    np.random.seed(2016)
    for arr in np.array_split(index, N//M):
        size = len(arr)
        df = pd.DataFrame({'userid':arr , 'day':np.random.randint(366, size=size)})
        df = df[['userid', 'day']]
        yield df

N, M = 8*10**4, 5*10**2
index = np.arange(N)
np.random.shuffle(index)
columns = range(366)

def using_numpy():
    npidx = np.empty_like(index)
    npidx[index] = np.arange(len(index))
    x = np.zeros((len(index), len(columns)), dtype=np.int8)
    for r in mock_read_sql_query():
        x[npidx[r['userid']], r['day']] = 1
    x = pd.DataFrame(x, columns=columns, index=index)
    return x

def orig():
    x = pd.DataFrame(0, index=index, columns=columns, dtype=np.int8)
    for r in mock_read_sql_query():
        for userid, day in r.itertuples(index=False):
            x.at[userid, day] = 1
    return x

expected = orig()
result = using_numpy()

expected_index, expected_col = np.where(expected)
result_index, result_col = np.where(result)
assert np.equal(expected_index, result_index).all()
assert np.equal(expected_col, result_col).all()