如何将含时间戳的表格转换为按时间分组的二维数组？

核心思路 #

按timestamp分组，每个时间戳下按A、B的顺序提取item_value，将每组值组成子数组，最终汇总成目标二维数组。

Python 实现 #

方法1：纯Python处理

# 整理后的原始数据（可从表格读取后转换为该格式）
data = [
    {"item_name": "A", "item_value": 0.25, "timestamp": "2023-03-01T17:20:00.000+0000"},
    {"item_name": "B", "item_value": 0.34, "timestamp": "2023-03-01T17:20:00.000+0000"},
    {"item_name": "A", "item_value": 0.30, "timestamp": "2023-03-01T17:30:00.000+0000"},
    {"item_name": "B", "item_value": 0.54, "timestamp": "2023-03-01T17:30:00.000+0000"},
    {"item_name": "A", "item_value": 0.32, "timestamp": "2023-03-01T17:35:00.000+0000"},
    {"item_name": "B", "item_value": 0.67, "timestamp": "2023-03-01T17:35:00.000+0000"},
]

# 按时间戳分组
groups = {}
for entry in data:
    ts = entry["timestamp"]
    if ts not in groups:
        groups[ts] = []
    # 保证A在前、B在后的顺序
    if entry["item_name"] == "A":
        groups[ts].insert(0, entry["item_value"])
    else:
        groups[ts].append(entry["item_value"])

# 按时间戳排序后生成结果数组
result = [group for _, group in sorted(groups.items())]
print(result)
# 输出: [[0.25, 0.34], [0.30, 0.54], [0.32, 0.67]]

方法2：使用Pandas（适合大量数据）

import pandas as pd

df = pd.DataFrame(data)

# 分组后按item_name排序，提取值列表
result = df.groupby("timestamp").apply(
    lambda x: x.sort_values("item_name")["item_value"].tolist()
).tolist()

print(result)
# 输出: [[0.25, 0.34], [0.30, 0.54], [0.32, 0.67]]

JavaScript 实现 #

// 原始数据
const data = [
    {item_name: "A", item_value: 0.25, timestamp: "2023-03-01T17:20:00.000+0000"},
    {item_name: "B", item_value: 0.34, timestamp: "2023-03-01T17:20:00.000+0000"},
    {item_name: "A", item_value: 0.30, timestamp: "2023-03-01T17:30:00.000+0000"},
    {item_name: "B", item_value: 0.54, timestamp: "2023-03-01T17:30:00.000+0000"},
    {item_name: "A", item_value: 0.32, timestamp: "2023-03-01T17:35:00.000+0000"},
    {item_name: "B", item_value: 0.67, timestamp: "2023-03-01T17:35:00.000+0000"},
];

// 按时间戳分组
const groups = {};
data.forEach(entry => {
    const ts = entry.timestamp;
    if (!groups[ts]) groups[ts] = [];
    entry.item_name === "A" ? groups[ts].unshift(entry.item_value) : groups[ts].push(entry.item_value);
});

// 按时间戳排序后生成结果
const result = Object.entries(groups)
    .sort((a, b) => new Date(a[0]) - new Date(b[0]))
    .map(item => item[1]);
console.log(result);
// 输出: [[0.25, 0.34], [0.30, 0.54], [0.32, 0.67]]

内容的提问来源于stack exchange，提问作者MMV