# Transform¶

## Pivot by a single column¶

The `Table.pivot()` method is a general process for grouping data by row and, optionally, by column, and then calculating some aggregation for each group. Consider the following table:

name

race

gender

age

Joe

white

female

20

Jane

asian

male

20

Jill

black

female

20

Jim

latino

male

25

Julia

black

female

25

Joan

asian

female

25

In the very simplest case, this table can be pivoted to count the number occurences of values in a column:

```transformed = table.pivot('race')
```

Result:

race

pivot

white

1

asian

2

black

2

latino

1

## Pivot by multiple columns¶

You can pivot by multiple columns either as additional row-groups, or as intersecting columns. For example, given the table in the previous example:

```transformed = table.pivot(['race', 'gender'])
```

Result:

race

gender

pivot

white

female

1

asian

male

1

black

female

2

latino

male

1

asian

female

1

For the column, version you would do:

```transformed = table.pivot('race', 'gender')
```

Result:

race

male

female

white

0

1

asian

1

1

black

0

2

latino

1

0

## Pivot to sum¶

The default pivot aggregation is `Count` but you can also supply other operations. For example, to aggregate each group by `Sum` of their ages:

```transformed = table.pivot('race', 'gender', aggregation=agate.Sum('age'))
```

race

male

female

white

0

20

asian

20

25

black

0

45

latino

25

0

## Pivot to percent of total¶

Pivot allows you to apply a `Computation` to each row of aggregated results prior to returning the table. Use the stringified name of the aggregation as the column argument to your computation:

```transformed = table.pivot('race', 'gender', aggregation=agate.Sum('age'), computation=agate.Percent('sum'))
```

race

male

female

white

0

14.8

asian

14.8

18.4

black

0

33.3

latino

18.4

0

Note: actual computed percentages will be much more precise.

It’s helpful when constructing these cases to think of all the cells in the pivot table as a single sequence.

## Denormalize key/value columns into separate columns¶

It’s common for very large datasets to be distributed in a “normalized” format, such as:

name

property

value

Jane

gender

female

Jane

race

black

Jane

age

24

The `Table.denormalize()` method can be used to transform the table so that each unique property has its own column.

```transformed = table.denormalize('name', 'property', 'value')
```

Result:

name

gender

race

age

Jane

female

black

24

Jack

male

white

35

Joe

male

black

28

## Normalize separate columns into key/value columns¶

Sometimes you have a dataset where each property has its own column, but your analysis would be easier if all properties were stored together. Consider this table:

name

gender

race

age

Jane

female

black

24

Jack

male

white

35

Joe

male

black

28

The `Table.normalize()` method can be used to transform the table so that all the properties and their values share two columns.

```transformed = table.normalize('name', ['gender', 'race', 'age'])
```

Result:

name

property

value

Jane

gender

female

Jane

race

black

Jane

age

24