# pivot

> For the complete documentation index, see [llms.txt](https://kotlin.github.io/dataframe/llms.txt).

Splits the rows of a [DataFrame](dataframe.html) and groups them horizontally into new columns based on values from one or several columns of the original [DataFrame](dataframe.html).

API reference: [pivot](https://kotlin.github.io/dataframe/api-docs/core/org.jetbrains.kotlinx.dataframe.api/pivot.html), [Pivot](https://kotlin.github.io/dataframe/api-docs/core/org.jetbrains.kotlinx.dataframe.api/-pivot/index.html)

```TEXT
pivot (inward = true) { pivotColumns }
  [ .groupBy { indexColumns } | .groupByOther() ]
  [ .default(defaultValue) ]
     reducer | aggregator
    
reducer = .minBy { column } | .maxBy { column } | .first [ { rowCondition } ] | .last [ { rowCondition } ] 
          .medianBy { column } | .percentileBy(percentile) { column } |.with { rowExpression } | .values { valueColumns }

aggregator = .count() | .matches() | .frames() | .with { rowExpression } | .values { valueColumns } | .aggregate { aggregations } | .<stat> [ { columns } ]
```

See [column selectors](columnselectors.html) for how to select the columns for this operation, [pivot + groupBy](#pivot-groupby), [pivot reducing](#reducing), [pivot aggregation](#aggregation).

Parameters:

* `inward` — if `true` generated columns are nested inside the original column, otherwise they will be top-level

* `pivotColumns` — columns with values for horizontal data grouping and generation of new columns

* `indexColumns` — columns with values for vertical data grouping

* `defaultValue` — value to fill mismatched pivot-index column pairs

* `valueColumns` — columns with output values

```KOTLIN
df
```

[Embedded content](./resources/df.html)

Properties:

```KOTLIN
df.pivot { isHappy }
```

Strings:

```KOTLIN
df.pivot("isHappy")
```

[Embedded content](./resources/pivot.html)

Properties:

```KOTLIN
df.pivot(inward = true) { isHappy }
```

Strings:

```KOTLIN
df.pivot("isHappy", inward = true)
```

[Embedded content](./resources/pivotInward.html)

To pivot several columns at once, you can combine them using `and` or `then` infix function:

* `and` will pivot columns independently

* `then` will create column hierarchy from combinations of values from pivoted columns

Properties:

```KOTLIN
df.pivot { isHappy and name.firstName }
```

Strings:

```KOTLIN
df.pivot { "isHappy" and "name"["firstName"] }
```

[Embedded content](./resources/pivotAnd.html)

Properties:

```KOTLIN
df.pivot { isHappy then name.firstName }
```

Strings:

```KOTLIN
df.pivot { "isHappy" then "name"["firstName"] }
```

[Embedded content](./resources/pivotThen.html)

## pivot + groupBy

To create a matrix table that is expanded both horizontally and vertically, apply [groupBy](groupby.html) transformation passing the columns for vertical grouping. Reversed order of `pivot` and [groupBy](groupby.html) will produce the same result.

Properties:

```KOTLIN
df.pivot { isHappy }.groupBy { name.firstName }
// same as
df.groupBy { name.firstName }.pivot { isHappy }
```

Strings:

```KOTLIN
df.pivot("isHappy").groupBy { "name"["firstName"] }
// same as
df.groupBy { "name"["firstName"] }.pivot("isHappy")
```

[Embedded content](./resources/pivotGroupBy.html)

To group by all columns except pivoted, use `groupByOther`:

Properties:

```KOTLIN
df.pivot { isHappy }.groupByOther()
```

Strings:

```KOTLIN
df.pivot("isHappy").groupByOther()
```

[Embedded content](./resources/pivotGroupByOther.html)

`PivotGroupBy` supports the same reducing and aggregation operations as `Pivot`. In addition, `PivotGroupBy` supports the [default](#default-values-for-aggregation) and [matches](#pivotmatches) functions.

Properties:

```KOTLIN
df.groupBy { name.firstName }.pivot { isHappy }.max { age }
```

Strings:

```KOTLIN
df.groupBy { "name"["firstName"] }.pivot("isHappy").max("age")
```

[Embedded content](./resources/pivotGroupByAggregation.html)

Properties:

```KOTLIN
df.pivot { isHappy }.groupBy { name.firstName }.frames()
```

Strings:

```KOTLIN
df.pivot("isHappy").groupBy { "name"["firstName"] }.frames()
```

[Embedded content](./resources/pivotGroupByFrames.html)

## Reducing

A [Pivot]() can be reduced into a [DataRow](datarow.html). It means that each `Pivot` group is collapsed into a single row, and then these rows are concatenated into a single [DataRow](datarow.html) with `Pivot` keys as top-level columns or as [column groups](datacolumn.html#columngroup).

Reducing is a specific case of [aggregation](#aggregation).

### Step 1: use a reducing method

Use the following functions to collapse each group in a [Pivot]() into a single row:

* [first](first.html) / [last](last.html) — take the first or last row (optionally, the first or last one that satisfies a predicate) of each group;

* [minBy](minby.html) / [maxBy](maxby.html) — take the row with the minimum or maximum value of the given [row expression](datarow.html#row-expressions) evaluated on rows within each group;

* [medianBy](median.html) / [percentileBy](percentile.html) — take the row at the position closest to the estimated median/percentile index of the [row expression](datarow.html#row-expressions)'s results calculated on rows within each group.

These functions return an instance of `ReducedPivot`, which is a class serving as a transitional step between performing a reduction on [Pivot]() groups and specifying how the resulting reduced rows should be represented in a resulting [DataRow](datarow.html).

#### Examples of reducing

##### df.pivot

Properties:

```KOTLIN
df.pivot { isHappy }
```

Strings:

```KOTLIN
df.pivot("isHappy")
```

[Embedded content](./resources/pivotReducing.html)

##### first

Properties:

```KOTLIN
df.pivot { isHappy }.first()
```

Strings:

```KOTLIN
df.pivot("isHappy").first()
```

[Embedded content](./resources/pivotFirst.html)

The [first](first.html) function can be used with a predicate:

Properties:

```KOTLIN
df.pivot { isHappy }.first { age == 30 }
```

Strings:

```KOTLIN
df.pivot("isHappy").first { "age"<Int>() == 30 }
```

[Embedded content](./resources/pivotFirstWithPredicate.html)

##### last

Properties:

```KOTLIN
df.pivot { isHappy }.last()
```

Strings:

```KOTLIN
df.pivot("isHappy").last()
```

[Embedded content](./resources/pivotLast.html)

The [last](last.html) function can be used with a predicate:

Properties:

```KOTLIN
df.pivot { isHappy }.last { age > 20 }
```

Strings:

```KOTLIN
df.pivot("isHappy").last { "age"<Int>() > 20 }
```

[Embedded content](./resources/pivotLastWithPredicate.html)

##### minBy

Properties:

```KOTLIN
df.pivot { isHappy }.minBy { weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").minBy { "weight"<Int>() }
```

[Embedded content](./resources/pivotMinBy.html)

##### maxBy

Properties:

```KOTLIN
df.pivot { isHappy }.maxBy { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").maxBy { "age"<Int>() }
```

[Embedded content](./resources/pivotMaxBy.html)

##### medianBy

Properties:

```KOTLIN
df.pivot { isHappy }.medianBy { weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").medianBy { "weight"<Int>() }
```

[Embedded content](./resources/pivotMedianBy.html)

##### percentileBy

Properties:

```KOTLIN
df.pivot { isHappy }.percentileBy(25.0) { weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").percentileBy(25.0) { "weight"<Int>() }
```

[Embedded content](./resources/pivotPercentileBy.html)

###  Step 2: transform the resulting reduced rows into a new [DataRow](datarow.html) 

`ReducedPivot` can be transformed into a new [DataRow](datarow.html) (either using the original reduced rows or their transformed versions).

To perform this transformation, use one of the following functions:

* [values](values.html) — creates a new [DataRow](datarow.html) containing the values from the reduced rows in the selected columns;

* `with` — computes a new value for each reduced row using a [row expression](datarow.html#row-expressions) and produces a [DataRow](datarow.html) containing these computed values.

Each of these functions returns a new [DataRow](datarow.html) with [Pivot]() keys as top-level columns (or as [column groups](datacolumn.html#columngroup)) and values composed of the reduced results from each group.

#### Examples of transforming

##### values

Properties:

```KOTLIN
df.pivot { isHappy }.first().values()
```

Strings:

```KOTLIN
df.pivot("isHappy").first().values()
```

[Embedded content](./resources/pivotValues.html)

##### with

Properties:

```KOTLIN
df.pivot { isHappy }
    .maxBy { age }
    .with { name.firstName + " " + name.lastName }
```

Strings:

```KOTLIN
df.pivot("isHappy")
    .maxBy("age")
    .with { "name"["firstName"]<String>() + " " + "name"["lastName"]<String>() }
```

[Embedded content](./resources/pivotWith.html)

## Aggregation

Each Pivot group can be aggregated — that is, transformed into a new value, [DataRow](datarow.html), or [DataFrame](dataframe.html) — and then concatenated into a single [DataRow](datarow.html) composed of these aggregated results, with pivot keys as top-level columns or as [column groups](datacolumn.html#columngroup).

The following aggregation methods are available:

* `frames` — returns this [Pivot]() as a [DataRow](datarow.html) with pivot keys as [columns](datacolumn.html) (or [column groups](datacolumn.html#columngroup)) and corresponding groups stored as [FrameColumns](datacolumn.html#framecolumn);

* [values](values.html) — collects values from all rows of each group for the selected columns into a single `List` (values from [column groups](datacolumn.html#columngroup) are collected into a [DataFrame](dataframe.html));

* [count](count.html) — creates a [DataRow](datarow.html) with the `Pivot` key columns containing the number of rows in each corresponding group;

* `with` — creates a [DataRow](datarow.html) containing values computed using a [row expression](datarow.html#row-expressions) across all rows of each group: * values of `DataRow<T>` are collected into `DataFrame<T>`, the resulting column is `FrameColumn<T>`; * values of other types `R` are collected into `List<R>`, the resulting column is `DataColumn<List<R>>`;

* `aggregate` — performs a set of custom aggregations using `AggregateDsl`, allowing computation of one or more derived values per group;

* various [aggregation statistics](#aggregation-statistics).

Each of these methods returns a new [DataRow](datarow.html) with `Pivot` keys as top-level columns (or as [column groups](datacolumn.html#columngroup)) and values representing the aggregated results of each group.

### Examples of aggregation

#### frames

Properties:

```KOTLIN
df.pivot { isHappy }.frames()
```

Strings:

```KOTLIN
df.pivot("isHappy").frames()
```

[Embedded content](./resources/pivotFrames.html)

#### values on Pivot

Properties:

```KOTLIN
df.pivot { isHappy }.values { name and age }
```

Strings:

```KOTLIN
df.pivot("isHappy").values("name", "age")
```

[Embedded content](./resources/pivotValuesAggregate.html)

Properties:

```KOTLIN
df.pivot { isHappy }.values()
```

Strings:

```KOTLIN
df.pivot("isHappy").values()
```

[Embedded content](./resources/pivotValuesAggregateAll.html)

#### count

Properties:

```KOTLIN
df.pivot { isHappy }.count()
```

Strings:

```KOTLIN
df.pivot("isHappy").count()
```

[Embedded content](./resources/pivotCount.html)

#### with on Pivot

Properties:

```KOTLIN
df.pivot { isHappy }.with { name.lastName }
```

Strings:

```KOTLIN
df.pivot("isHappy").with { "name"["lastName"]<String>() }
```

[Embedded content](./resources/withOnPivot.html)

#### aggregate

Properties:

```KOTLIN
df.pivot { isHappy }.aggregate {
    count() into "total"
    count { age >= 18 } into "adults"
    median { age } into "median age"
    min { age } into "min age"
    maxBy { age }.name into "oldest"
}
```

Strings:

```KOTLIN
df.pivot("isHappy").aggregate {
    count() into "total"
    count { "age"<Int>() > 18 } into "adults"
    median("age") into "median age"
    min("age") into "min age"
    maxBy("age")["name"] into "oldest"
}
```

[Embedded content](./resources/pivotAggregateMultiple.html)

If only one aggregation function is used, the column name can be omitted:

Properties:

```KOTLIN
df.pivot { isHappy }.aggregate { minBy { age }.name }
```

Strings:

```KOTLIN
df.pivot("isHappy").aggregate { minBy("age")["name"] }
```

[Embedded content](./resources/pivotAggregate.html)

### Aggregation statistics

[Aggregation statistics](summarystatistics.html) are predefined shortcuts for common statistical aggregations such as sum, mean, median, and others. These aggregation operations are applied to each group within a `Pivot`.

Each function computes a statistic across the [rows](datarow.html) of a pivot group and returns the result as a new value in the resulting [DataRow](datarow.html) (for [Pivot]()) or as a new [column](datacolumn.html) in the resulting [DataFrame](dataframe.html) (for `PivotGroupBy`).

The following aggregation statistics are available:

* [count](count.html);

* [max / maxOf / maxFor](minmax.html);

* [min / minOf / minFor](minmax.html);

* [sum / sumOf / sumFor](sum.html);

* [mean / meanOf / meanFor](mean.html);

* [std / stdOf / stdFor](std.html);

* [median / medianOf / medianFor](median.html);

* [percentile / percentileOf / percentileFor](percentile.html).

To compute one or several [statistics](summarystatistics.html) per every pivot group, use the [aggregate](#aggregate) function.

The functions `max`, `maxOf`, and `maxFor` differ as follows. They all calculate the maximum of values, but:

* `max` computes it on the selected columns. If more than one column is selected, for each group it computes one maximum value among all selected columns.

* `maxOf` computes it by a [row expression](datarow.html#row-expressions): the expression is calculated for each row of the group and the maximum value is returned.

* `maxFor` computes it for each of the selected columns within each group. If more than one column is selected, for each group it computes the maximum value for each selected column separately.

Similar logic applies to other statistics.

#### Direct aggregations

Most common aggregation functions can be computed directly on a [Pivot]() or `PivotGroupBy`.

When applied to a [Pivot]() (without [groupBy](groupby.html)), the result is a [DataRow](datarow.html) with pivot keys as columns and the computed statistic as values.

When applied to a `PivotGroupBy`, the result is a [DataFrame](dataframe.html) where rows correspond to the grouping keys and columns correspond to the pivot keys, with computed statistics as cell values.

##### Examples of direct aggregations

###### max

Properties:

```KOTLIN
df.pivot { isHappy }.max { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").max("age")
```

[Embedded content](./resources/pivotMax.html)

Properties:

```KOTLIN
df.pivot { isHappy }.max { age and weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").max("age", "weight")
```

[Embedded content](./resources/pivotMaxMultiple.html)

Properties:

```KOTLIN
df.pivot { isHappy }.maxFor { age and weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").maxFor("age", "weight")
```

[Embedded content](./resources/pivotMaxForMultiple.html)

Properties:

```KOTLIN
df.pivot { isHappy }.maxOf { if (age < 30) weight else null }
```

Strings:

```KOTLIN
df.pivot("isHappy").maxOf { if ("age"<Int>() < 30) "weight"<Int>() else null }
```

[Embedded content](./resources/pivotMaxOf.html)

###### min

Properties:

```KOTLIN
df.pivot { isHappy }.min { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").min("age")
```

[Embedded content](./resources/pivotMin.html)

###### sum

Properties:

```KOTLIN
df.pivot { isHappy }.sum { weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").sum("weight")
```

[Embedded content](./resources/pivotSum.html)

###### mean

Properties:

```KOTLIN
df.pivot { isHappy }.mean()
```

Strings:

```KOTLIN
df.pivot("isHappy").mean()
```

[Embedded content](./resources/pivotMeanAll.html)

Properties:

```KOTLIN
df.pivot { isHappy }.mean { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").mean { "age"<Int>() }
```

[Embedded content](./resources/pivotMean.html)

###### std

Properties:

```KOTLIN
df.pivot { isHappy }.std { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").std { "age"<Int>() }
```

[Embedded content](./resources/pivotStd.html)

###### median

Properties:

```KOTLIN
df.pivot { isHappy }.median { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").median("age")
```

[Embedded content](./resources/pivotMedian.html)

###### percentile

Properties:

```KOTLIN
df.pivot { isHappy }.percentile(25.0) { age }
```

Strings:

```KOTLIN
df.pivot("isHappy").percentile(25.0) { "age"<Int>() }
```

[Embedded content](./resources/pivotPercentile.html)

####  The `separate` flag 

By default, when an aggregation function produces several values for a single data group, the column hierarchy in the resulting [DataFrame](dataframe.html) is indexed first by pivot keys and then by the names of aggregated values. To reverse this order so that resulting columns are indexed first by names of aggregated values and then by pivot keys, use the `separate=true` flag that is available in multi-result aggregation operations, such as `aggregate` or `<stat>For`:

Properties:

```KOTLIN
df.pivot { isHappy }.maxFor(separate = true) { age and weight }
```

Strings:

```KOTLIN
df.pivot("isHappy").maxFor("age", "weight", separate = true)
```

[Embedded content](./resources/pivotSeparate.html)

Properties:

```KOTLIN
df.pivot { isHappy }.aggregate(separate = true) {
    min { age } into "min age"
    maxOrNull { weight } into "max weight"
}
```

Strings:

```KOTLIN
df.pivot("isHappy").aggregate(separate = true) {
    min("age") into "min age"
    maxOrNull("weight") into "max weight"
}
```

[Embedded content](./resources/pivotAggregateSeparate.html)

#### Default values for aggregation

By default, any aggregation function will result in a `null` value for those matrix cells where the intersection of column and row keys produced an empty data group. You can specify a default value for any aggregation by using the `default` infix function. This value will replace all `null` results of the aggregation function over non-empty data groups as well. To use one default value for all aggregation functions, use `default()` before aggregation.

Properties:

```KOTLIN
df.pivot { isHappy }.groupBy { city }.aggregate { min { age } default 0 }
```

Strings:

```KOTLIN
df.pivot("isHappy").groupBy("city").aggregate { min("age") default 0 }
```

[Embedded content](./resources/pivotDefault.html)

Properties:

```KOTLIN
df.pivot { isHappy }.groupBy { city }.default(0).min()
```

Strings:

```KOTLIN
df.pivot("isHappy").groupBy("city").default(0).min()
```

[Embedded content](./resources/pivotDefaultDirect.html)

Properties:

```KOTLIN
df.pivot { isHappy }.groupBy { city }.aggregate {
    count() into "people" default 0
    any { age < 18 } into "hasMinors" default false
}
```

Strings:

```KOTLIN
df.pivot("isHappy").groupBy("city").aggregate {
    count() into "people" default 0
    any { "age"<Int>() < 18 } into "hasMinors" default false
}
```

[Embedded content](./resources/pivotDefaultMultiple.html)

### Pivot inside aggregate

[pivot]() can be used inside the [aggregate](groupby.html#aggregation) function of [groupBy](groupby.html). This allows combining column pivoting with other [groupBy](groupby.html) aggregations:

Properties:

```KOTLIN
df.groupBy { name.firstName }.aggregate {
    pivot { isHappy }.aggregate(separate = true) {
        mean { age } into "mean age"
        count() into "count"
    }
    count() into "total"
}
```

Strings:

```KOTLIN
df.groupBy { "name"["firstName"] }.aggregate {
    pivot("isHappy").aggregate(separate = true) {
        mean("age") into "mean age"
        count() into "count"
    }
    count() into "total"
}
```

[Embedded content](./resources/pivotInAggregate.html)

### pivotCounts

Pivots one or several columns with [count](count.html) aggregation, preserving all other columns of a [DataFrame](dataframe.html) or a `GroupBy`.

Properties:

```KOTLIN
df.pivotCounts { isHappy }
// same as
df.pivot { isHappy }.groupByOther().count()
```

Strings:

```KOTLIN
df.pivotCounts("isHappy")
// same as
df.pivot("isHappy").groupByOther().count()
```

[Embedded content](./resources/pivotCountsOnDf.html)

Properties:

```KOTLIN
df.groupBy { name }.pivotCounts { city }
// same as
df.groupBy { name }.pivot { city }.count()
// same as
df.groupBy { name }.aggregate {
    pivotCounts { city }
}
```

Strings:

```KOTLIN
df.groupBy("name").pivotCounts("city")
// same as
df.groupBy("name").pivot("city").count()
// same as
df.groupBy("name").aggregate {
    pivotCounts("city")
}
```

[Embedded content](./resources/pivotCountsOnGroupBy.html)

### pivotMatches

Pivots one or several columns with `Boolean` aggregation, preserving all other columns of [DataFrame](dataframe.html). Each cell in the resulting pivoted columns contains `true` if that value was present in the original data for the corresponding row, and `false` otherwise.

Properties:

```KOTLIN
df.pivotMatches { city }
// same as
df.pivot { city }.groupByOther().matches()
```

Strings:

```KOTLIN
df.pivotMatches("city")
// same as
df.pivot("city").groupByOther().matches()
```

[Embedded content](./resources/pivotMatchesGroupByOther.html)

Properties:

```KOTLIN
df.groupBy { name }.pivotMatches { city }
// same as
df.groupBy { name }.pivot { city }.matches()
// same as
df.groupBy { name }.aggregate {
    pivotMatches { city }
}
```

Strings:

```KOTLIN
df.groupBy("name").pivotMatches("city")
// same as
df.groupBy("name").pivot("city").matches()
// same as
df.groupBy("name").aggregate {
    pivotMatches("city")
}
```

[Embedded content](./resources/pivotMatches.html)