同一张表中有许多时间序列：如何以正确的格式生成 JSON 字符串

Question

我在 Postgres 数据库中存储了简单的时间序列，可以将其加载到 Pandas 数据框中。date    number      system_id1       33.1        12       24.2        13       14.1        14       15.5        1[...]   1113        11       4513        22       53.4        23       24.8        24       13.12       2[...]   3333        2想要的结果是一个 JSON 字符串，我可以将它提供给我的 JavaScript 图表，如下所示：[[1, number in date 1(of system_id 1), number in date 1(of system_id 2), number in date 1(of system_id 3), ...],[2, number 2(of system_id 1), number 2(of system_id 2), number 2(of system_id 3), ...],[3, number 3(of system_id 1), number 3(of system_id 2), number 3(of system_id 3), ...],[4, number 4(of system_id 1), number 4(of system_id 2), number 4(of system_id 3), ...],[...]]我可以直接在SQL中拉出上表SELECT * FROM MyTable或者我可以使用 Django-ORM 将它传递给pandas.DataFrame()Python。无论我用 ORM、Pandas 还是直接在 SQL 中获得想要的结果都是一样的，它只需要尽可能快，这就是让我开始在 SQL 中寻找解决方案的原因。但是，我不知道如何。我需要的是GROUP BY date然后为每个不同的列创建一个不同的列system_id：这是否可能或可取？GROUP BY date要求我为该列提供一个聚合函数 ( sum, ...)。是否有一个聚合函数可以执行我正在尝试做的事情？avgnumber

Answer 1

您可以使用Postgres上的string_agg或MySQL上的GROUP_CONCAT来实现。

输出将类似于

| date | numbers       |

|------|---------------|

| 1    | 33.1,4513,... |

| 2    | 24.2,53.4,... |

| 3    | 14.1,24.8,... |

和查询

SELECT date, STRING_AGG(number) numbers 

FROM MyTable 

GROUP BY date

Answer 2

我认为我们可以通过 pandas 交叉表功能的一些帮助来做到这一点，

我的假设是您需要将数字列作为整数而不是字符串（如我的评论解决方案中所示）

如果日期列中有任何字符串，它将被注册为对象，因此读取为字符串。

import pandas as pd

df = pd.read_clipbard(sep='\s+')

print(df)

    date    number  system_id

0   1   33.10   1

1   2   24.20   1

2   3   14.10   1

3   4   15.50   1

4   [...]   1113.00     1

5   1   4513.00     2

6   2   53.40   2

7   3   24.80   2

8   4   13.12   2

9   [...]   3333.00     2

然后我们可以将它传递给交叉表，同时传递system_id列参数

js_object = (pd.crosstab(df.date, df.system_id, 

values=df.number,aggfunc='first').reset_index().values.tolist())

print(js_object)

 [['1', 33.1, 4513.0],

 ['2', 24.2, 53.4],

 ['3', 14.1, 24.8],

 ['4', 15.5, 13.12],

 ['[...]', 1113.0, 3333.0]]

希望有帮助！