pivot: перевод длинного формата в широкий

Визуализации в Vega и Vega-Lite чаще всего строятся на данных в длинном формате (long format). В таком представлении каждая строка содержит:

  • измерение;
  • категорию;
  • одно значение метрики.

Пример длинного формата:

[
  {"month": "Jan", "type": "A", "value": 10},
  {"month": "Jan", "type": "B", "value": 20},
  {"month": "Feb", "type": "A", "value": 15},
  {"month": "Feb", "type": "B", "value": 25}
]

Структура таблицы:

month type value
Jan A 10
Jan B 20
Feb A 15
Feb B 25

Широкий формат (wide format) представляет значения категорий в виде отдельных столбцов:

month A B
Jan 10 20
Feb 15 25

В Vega-Lite переход между этими форматами выполняется с помощью трансформации pivot.

Назначение transform.pivot

Трансформация pivot преобразует значения одного поля в набор новых колонок.

Синтаксис:

{
  "pivot": "field_with_categories",
  "value": "field_with_values",
  "groupby": ["field1", "field2"]
}

Основные параметры:

Параметр Назначение
pivot поле, значения которого станут именами новых колонок
value поле со значениями
groupby поля группировки
op агрегирующая операция
limit ограничение количества создаваемых колонок

Базовый пример pivot

Исходные данные:

[
  {"year": 2023, "metric": "sales", "amount": 100},
  {"year": 2023, "metric": "profit", "amount": 30},
  {"year": 2024, "metric": "sales", "amount": 120},
  {"year": 2024, "metric": "profit", "amount": 40}
]

Трансформация:

{
  "transform": [
    {
      "pivot": "metric",
      "value": "amount",
      "groupby": ["year"]
    }
  ]
}

Результат:

year sales profit
2023 100 30
2024 120 40

Полный пример Vega-Lite

<!DOCTYPE html>
<html>
<head>
  <script src="https://cdn.jsdelivr.net/npm/vega@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-lite@5"></script>
  <script src="https://cdn.jsdelivr.net/npm/vega-embed@6"></script>
</head>
<body>
  <div id="view"></div>

  <script>
    const spec = {
      "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

      "data": {
        "values": [
          {"year": 2023, "metric": "sales", "amount": 100},
          {"year": 2023, "metric": "profit", "amount": 30},
          {"year": 2024, "metric": "sales", "amount": 120},
          {"year": 2024, "metric": "profit", "amount": 40}
        ]
      },

      "transform": [
        {
          "pivot": "metric",
          "value": "amount",
          "groupby": ["year"]
        }
      ],

      "mark": "bar",

      "encoding": {
        "x": {"field": "year", "type": "ordinal"},
        "y": {"field": "sales", "type": "quantitative"}
      }
    };

    vegaEmbed("#view", spec);
  </script>
</body>
</html>

Как работает pivot

Алгоритм трансформации:

  1. Выполняется группировка по groupby.
  2. Для каждой группы собираются уникальные значения поля pivot.
  3. Для каждого уникального значения создаётся отдельная колонка.
  4. Значения из value помещаются в соответствующие ячейки.

Схема:

groupby -> pivot categories -> generated columns

Использование нескольких groupby-полей

groupby может содержать несколько полей.

Исходные данные:

[
  {"year": 2024, "region": "EU", "metric": "sales", "value": 50},
  {"year": 2024, "region": "EU", "metric": "profit", "value": 15},
  {"year": 2024, "region": "US", "metric": "sales", "value": 80},
  {"year": 2024, "region": "US", "metric": "profit", "value": 25}
]

Трансформация:

{
  "pivot": "metric",
  "value": "value",
  "groupby": ["year", "region"]
}

Результат:

year region sales profit
2024 EU 50 15
2024 US 80 25

Агрегация через op

Иногда после pivot возникает несколько значений для одной ячейки.

Пример:

[
  {"month": "Jan", "type": "A", "value": 10},
  {"month": "Jan", "type": "A", "value": 20}
]

Без агрегирования Vega-Lite не сможет определить, какое значение использовать.

Для решения применяется op.

Пример с sum

{
  "pivot": "type",
  "value": "value",
  "groupby": ["month"],
  "op": "sum"
}

Результат:

month A
Jan 30

Поддерживаемые агрегирующие операции

op поддерживает те же операции, что и aggregate.

Основные варианты:

Операция Описание
sum сумма
average среднее
median медиана
min минимум
max максимум
count количество
distinct число уникальных
valid число валидных
missing число пропусков

Pivot для временных рядов

Часто применяется для преобразования метрик по времени.

Исходные данные:

[
  {"date": "2024-01", "metric": "temperature", "value": 5},
  {"date": "2024-01", "metric": "humidity", "value": 70},
  {"date": "2024-02", "metric": "temperature", "value": 8},
  {"date": "2024-02", "metric": "humidity", "value": 65}
]

Pivot:

{
  "pivot": "metric",
  "value": "value",
  "groupby": ["date"]
}

Результат:

date temperature humidity
2024-01 5 70
2024-02 8 65

Pivot и heatmap

Pivot особенно полезен для матриц и тепловых карт.

Исходные данные:

[
  {"x": 1, "y": "A", "v": 10},
  {"x": 1, "y": "B", "v": 20},
  {"x": 2, "y": "A", "v": 15},
  {"x": 2, "y": "B", "v": 30}
]

После pivot:

x A B
1 10 20
2 15 30

Ограничение количества колонок через limit

Если поле pivot содержит много уникальных значений, можно ограничить число создаваемых колонок.

{
  "pivot": "category",
  "value": "amount",
  "groupby": ["year"],
  "limit": 5
}

Это особенно важно при работе:

  • с большими CSV;
  • динамическими API;
  • логами;
  • телеметрией;
  • event-stream данными.

Проблема пропущенных значений

Если для некоторой комбинации данных отсутствует значение, создаётся null.

Исходные данные:

[
  {"month": "Jan", "type": "A", "value": 10},
  {"month": "Jan", "type": "B", "value": 20},
  {"month": "Feb", "type": "A", "value": 15}
]

Результат:

month A B
Jan 10 20
Feb 15 null

Заполнение пропусков

Часто используется совместно с calculate.

{
  "calculate": "datum.B || 0",
  "as": "B_filled"
}

Полный пример:

"transform": [
  {
    "pivot": "type",
    "value": "value",
    "groupby": ["month"]
  },
  {
    "calculate": "datum.B || 0",
    "as": "B_filled"
  }
]

Pivot и calculate

После преобразования появляются полноценные колонки, доступные в выражениях.

Пример:

{
  "calculate": "datum.sales - datum.costs",
  "as": "profit"
}

Полный pipeline:

"transform": [
  {
    "pivot": "metric",
    "value": "amount",
    "groupby": ["year"]
  },
  {
    "calculate": "datum.sales - datum.expenses",
    "as": "profit"
  }
]

Pivot и window

После преобразования можно применять оконные функции.

{
  "window": [
    {
      "op": "rank",
      "as": "rank"
    }
  ],
  "sort": [
    {"field": "sales", "order": "descending"}
  ]
}

Pivot в чистом Vega

В Vega используется аналогичная трансформация.

Пример:

{
  "type": "pivot",
  "field": "metric",
  "value": "amount",
  "groupby": ["year"]
}

Отличие Vega:

  • используется "type": "pivot";
  • transform описывается как объект Vega-пайплайна;
  • возможно более гибкое управление потоками данных.

Комбинирование pivot и fold

fold выполняет обратную операцию.

fold: широкий → длинный

{
  "fold": ["sales", "profit"],
  "as": ["metric", "value"]
}

pivot: длинный → широкий

{
  "pivot": "metric",
  "value": "value"
}

Цикл преобразований

Очень распространённая схема:

CSV/API
    ↓
fold
    ↓
filter
    ↓
aggregate
    ↓
pivot
    ↓
visualization

Практический пример: KPI dashboard

Исходные данные:

[
  {"department": "IT", "metric": "income", "value": 200},
  {"department": "IT", "metric": "costs", "value": 120},
  {"department": "HR", "metric": "income", "value": 90},
  {"department": "HR", "metric": "costs", "value": 70}
]

Pivot:

{
  "pivot": "metric",
  "value": "value",
  "groupby": ["department"]
}

Calculate:

{
  "calculate": "datum.income - datum.costs",
  "as": "profit"
}

Итог:

department income costs profit
IT 200 120 80
HR 90 70 20

Производительность pivot

pivot может быть дорогой операцией.

Причины:

  • создание большого количества колонок;
  • динамическая перестройка схемы;
  • агрегация;
  • обработка пропусков;
  • увеличение объёма объектов JavaScript.

Оптимизация

Ограничение cardinality

Количество уникальных значений поля pivot желательно держать небольшим.

Плохо:

pivot: user_id

Хорошо:

pivot: category

Предварительная агрегация

Лучше агрегировать данные заранее:

{
  "aggregate": [
    {
      "op": "sum",
      "field": "amount",
      "as": "total"
    }
  ],
  "groupby": ["year", "metric"]
}

Затем выполнять pivot.

Использование limit

{
  "pivot": "metric",
  "value": "amount",
  "limit": 10
}

Частые ошибки

Отсутствует groupby

Неправильно:

{
  "pivot": "metric",
  "value": "amount"
}

Результат может быть неожиданным, так как все строки будут объединены в одну группу.

Дублирование значений

Если существует несколько строк для одной комбинации:

groupby + pivot

необходимо указывать op.

Слишком много колонок

Если pivot создаёт сотни или тысячи полей:

  • растёт потребление памяти;
  • замедляется rendering;
  • ухудшается работа scales;
  • увеличивается время layout.

Отличия pivot от aggregate

aggregate:

много строк -> меньше строк

pivot:

строки -> колонки

Обе операции могут использоваться совместно.

Архитектура обработки данных

Во внутреннем pipeline Vega-Lite pivot:

  1. получает поток tuples;
  2. создаёт hash-group по groupby;
  3. строит словарь колонок;
  4. выполняет aggregation;
  5. формирует новый dataset.

Это означает, что после pivot структура объектов полностью меняется.

Когда использовать pivot

pivot особенно полезен в следующих сценариях:

  • матрицы;
  • heatmap;
  • dashboards;
  • KPI-таблицы;
  • cross-tab отчёты;
  • multi-metric visualizations;
  • сравнение категорий;
  • экспорт табличных данных;
  • подготовка данных для calculate/window.

Когда pivot нежелателен

В некоторых случаях длинный формат предпочтительнее:

  • stacked charts;
  • faceting;
  • layered visualizations;
  • streamgraph;
  • animated transitions;
  • grammar-of-graphics pipelines.

Большинство визуализаций Vega-Lite естественным образом работают именно с длинным форматом данных.

Сравнение long и wide форматов

Особенность Long Wide
Простота агрегации высокая средняя
Faceting удобно неудобно
Calculate между метриками сложно удобно
Heatmap средне удобно
Grammar of graphics идеально ограниченно
KPI-таблицы неудобно удобно

Полный пример pipeline

const spec = {
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

  "data": {
    "values": [
      {"year": 2023, "metric": "sales", "amount": 100},
      {"year": 2023, "metric": "costs", "amount": 60},
      {"year": 2024, "metric": "sales", "amount": 140},
      {"year": 2024, "metric": "costs", "amount": 90}
    ]
  },

  "transform": [
    {
      "pivot": "metric",
      "value": "amount",
      "groupby": ["year"]
    },
    {
      "calculate": "datum.sales - datum.costs",
      "as": "profit"
    }
  ],

  "mark": "bar",

  "encoding": {
    "x": {
      "field": "year",
      "type": "ordinal"
    },
    "y": {
      "field": "profit",
      "type": "quantitative"
    }
  }
};

Такой подход особенно распространён в аналитических системах, BI-dashboard интерфейсах и многомерной обработке данных внутри Vega/Vega-Lite pipeline.

Оглавление