---
title: Operate on columns
description: Find and count unique values, recalculate the _value column, and use values to calculate a new column.
url: https://docs.influxdata.com/influxdb/cloud/query-data/common-queries/operate-on-columns/
estimated_tokens: 4644
product: InfluxDB Cloud (TSM)
version: cloud
---

# Operate on columns

Use the following common queries to operate on columns:

-   [Find and count unique values in a column](#find-and-count-unique-values-in-a-column)
-   [Recalculate the \_values column](#recalculate-the-_values-column)
-   [Calculate a new column](#calculate-a-new-column)

These examples use [NOAA water sample data](/influxdb/cloud/reference/sample-data/#noaa-water-sample-data).

## Find and count unique values in a column

Find and count the number of unique values in a specified column. The following examples find and count unique locations where data was collected.

### Find unique values

This query:

-   Uses [`group()`](/flux/v0/stdlib/universe/group/) to ungroup data and return results in a single table.
-   Uses [`keep()`](/flux/v0/stdlib/universe/keep/) and [`unique()`](/flux/v0/stdlib/universe/unique/) to return unique values in the specified column.

```js
from(bucket: "noaa")
    |> range(start: -30d)
    |> group()
    |> keep(columns: ["location"])
    |> unique(column: "location")
```

#### Example results

| location |
| --- |
| coyote_creek |
| santa_monica |

### Count unique values

This query:

-   Uses [`group()`](/flux/v0/stdlib/universe/group/) to ungroup data and return results in a single table.
-   Uses [`keep()`](/flux/v0/stdlib/universe/keep/), [`unique()`](/flux/v0/stdlib/universe/unique/), and then [`count()`](/flux/v0/stdlib/universe/count/) to count the number of unique values.

```js
from(bucket: "noaa")
    |> group()
    |> unique(column: "location")
    |> count(column: "location")
```

#### Example results

| location |
| --- |
| 2 |

## Recalculate the \_values column

To recalculate the `_value` column, use the `with` operator in [`map()`](/flux/v0/stdlib/universe/map/) to overwrite the existing `_value` column.

The following query:

-   Uses [`filter()`](/flux/v0/stdlib/universe/filter/) to filter the `average_temperature` measurement.
-   Uses [`map()`](/flux/v0/stdlib/universe/map/) to convert Fahrenheit temperature values into Celsius.

```js

from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with _value: (float(v: r._value) - 32.0) * 5.0 / 9.0} ))
```

| _field | _measurement | _start | _stop | _time | location | _value |
| --- | --- | --- | --- | --- | --- | --- |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:00:00Z | coyote_creek | 27.77777777777778 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:06:00Z | coyote_creek | 22.77777777777778 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:12:00Z | coyote_creek | 30 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:18:00Z | coyote_creek | 31.666666666666668 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:24:00Z | coyote_creek | 25 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:30:00Z | coyote_creek | 21.11111111111111 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:36:00Z | coyote_creek | 28.88888888888889 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:42:00Z | coyote_creek | 24.444444444444443 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:48:00Z | coyote_creek | 29.444444444444443 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T00:54:00Z | coyote_creek | 26.666666666666668 |
| degrees | average_temperature | 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | 2019-08-17T01:00:00Z | coyote_creek | 21.11111111111111 |
| ••• | ••• | ••• | ••• | ••• | ••• | ••• |

## Calculate a new column

To use values in a row to calculate and add a new column, use `map()`. This example below converts temperature from Fahrenheit to Celsius and maps the Celsius value to a new `celsius` column.

The following query:

-   Uses [`filter()`](/flux/v0/stdlib/universe/filter/) to filter the `average_temperature` measurement.
-   Uses [`map()`](/flux/v0/stdlib/universe/map/) to create a new column calculated from existing values in each row.

```js
from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with celsius: (r._value - 32.0) * 5.0 / 9.0}))
```

#### Example results

| _start | _stop | _field | _measurement | location | _time | _value | celsius |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:00:00Z | 82 | 27.78 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:06:00Z | 73 | 22.78 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:12:00Z | 86 | 30.00 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:18:00Z | 89 | 31.67 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:24:00Z | 77 | 25.00 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:30:00Z | 70 | 21.11 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:36:00Z | 84 | 28.89 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:42:00Z | 76 | 24.44 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:48:00Z | 85 | 29.44 |
| 1920-03-05T22:10:01Z | 2020-03-05T22:10:01Z | degrees | average_temperature | coyote_creek | 2019-08-17T00:54:00Z | 80 | 26.67 |
| ••• | ••• | ••• | ••• | ••• | ••• | ••• | ••• |

[queries](/influxdb/cloud/tags/queries/)