python - 如何有效地列化(=旋转)pandas DataFrame(使用groupby)？

Question

为您提供问题的背景:

我有不错的 SQL 表(72M 行，6GB)，其中的数据可以理解为“基于列”，例如:

------------------------------
| fk_id | date       | field |
------------------------------
|     1 | 2001-01-02 |    24 |
|     1 | 2001-01-03 |    25 |
|     1 | 2001-01-04 |    21 |
|     1 | 2001-01-05 |    20 |
|     1 | 2001-01-06 |    30 |
|     1 | 2001-01-07 |    33 |
|            ....            |
|     2 | 2001-01-02 |    10 |
|     2 | 2001-01-03 |    15 |
|     2 | 2001-01-04 |    12 |
|     2 | 2001-01-05 |    11 |
|     2 | 2001-01-06 |    10 |
|     2 | 2001-01-07 |    12 |
|            ....            |
|            ....            |
| 12455 | 2015-01-01 |    99 |
| 12456 | 2005-10-10 |    10 |
| 12456 | 2005-10-11 |    10 |
|            ....            |
------------------------------

Python 中作为 pandas.DataFrame 所需的最终结果应该如下所示，其中 date 成为索引列、外键、列名称和值列字段矩阵的内容:

------------------------------------------------------
| date       |     1 |     2 |  .... | 12455 | 12456 | 
------------------------------------------------------
| 2001-01-02 |    24 |    10 |  .... |   NaN |   NaN |
| 2001-01-03 |    25 |    15 |  .... |   NaN |   NaN |
| 2001-01-04 |    21 |    12 |  .... |   NaN |   NaN |
| 2001-01-05 |    20 |    11 |  .... |   NaN |   NaN |
| 2001-01-06 |    30 |    10 |  .... |   NaN |   NaN |
| 2001-01-07 |    33 |    12 |  .... |   NaN |   NaN |
|       .... |    .. |    .. |  .... |  .... |  .... |
| 2005-10-10 |    50 |     4 |  .... |   NaN |    10 |
| 2005-10-11 |    51 |     3 |  .... |   NaN |    10 |
|       .... |    .. |    .. |  .... |  .... |  .... |
| 2015-01-01 |    40 |   NaN |  .... |    50 |    99 |
------------------------------------------------------

到目前为止，我使用以下代码完成此操作:

def _split_by_fk(self, df):
    """
    :param df: pandas.DataFrame
    :param fields: Iterable
    :return: pandas.Panel
    """
    data = dict()
    res = df.groupby('fk_id')
    for r in res:
        fk_id = r[0]
        data[fk_id] = r[1]['field']
    return pd.DataFrame(data)

def get_data(self, start, end):
    s = select([daily_data.c.date, daily_data.c.fk_id, daily_data.c.field])\
        .where(and_(end >= daily_data.c.date, daily_data.c.date >= start))\
        .order_by(daily_data.c.fk_id, daily_data.c.date)
    data = pd.read_sql(s, con=db_engine, index_col='date')
    return self._split_by_fk(data)


>>> get_data('1960-01-01', '1989-12-31')

这基本上是:

1. 直接通过 pandas.read_sql 函数通过 sqlalchemy 查询 SQL DB。
2. groupby 接收到的 DataFrame
3. 迭代分组结果对象并将它们放入字典中
4. 将dict转换为DataFrame。

使用上述方法查询 29 年的每日数据(13'813 列)需要 4 分 38 秒(整个 DataFrame 占用 796.5MB 内存)，其中 %lprun 表明大多数情况下花费在 read_sql 函数中，其余部分花费在 _split_by_fk 中(输出摘录):

% Time   Line Contents
===============================================================
83.8     data = pd.read_sql(s, con=db_engine, index_col='date')
16.2     return self._split_by_fk(data)

<小时/>

我的代码感觉不太优雅，因为我正在收集字典中的所有组以将它们再次转换为 DataFrame。

现在我的实际问题:是否有一种(更)有效/Pythonic的方法以上面所示的方式“列化”pandas.DataFrame？

<小时/>

PS:我不乐意提供关于处理此类数据结构和数据量的更一般方向的指针和提示，强硬的，我认为应该可以解决所有“小数据”风格的问题。

Answer 1

如果我理解正确，您可以执行df.pivot(index='date', columns='fk_id', values='field')。

I think that it should be possible to solve everything "small data"-style.

祝你好运。具有 12000 列的 DataFrame 不太可能表现良好。