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

Question

我在 Postgres 数据库中存储了简单的时间序列，可以将其加载到 Pandas 数据框中。

date    number      system_id

1       33.1        1

2       24.2        1

3       14.1        1

4       15.5        1

[...]   1113        1

1       4513        2

2       53.4        2

3       24.8        2

4       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 pddf = pd.read_clipbard(sep='\s+')print(df)    date    number  system_id0   1   33.10   11   2   24.20   12   3   14.10   13   4   15.50   14   [...]   1113.00     15   1   4513.00     26   2   53.40   27   3   24.80   28   4   13.12   29   [...]   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]]希望有帮助！