.. _csv:

# CSV

CSV (Comma Separated Values) is a common text file format for tabular data.
RiskScape can read CSV files directly and use them in your models.
CSV files are useful for storing relational data, such as asset inventories,
vulnerability tables, or lookup data.

.. _csv-type-detection:

## Type detection

When loading data from CSV files, by default RiskScape will automatically detect and apply more appropriate data types,
such as turning `lat`, `long` coordinates into geometry.
This makes it easier to work with numeric data and geometry in CSV format without having to manually create RiskScape bookmarks.

.. note::
    Type detection scans all of the data in the CSV file. This is convenient for smaller
    CSV files, but can take a long time for very large files (e.g. more than 100MB).

Type detection is applied *after* any other :ref:`attribute mapping <bookmarks-mapping-attributes>` operations
(i.e. `set-attribute` or `map-attribute`).

### Numeric columns

Type detection scans all text columns looking for numeric values:
- If a column contains only numeric values, the type will be changed to `floating`.
- If a column contains a mix of numeric and empty values, the type will be changed to `nullable('floating')`.
This allows RiskScape to handle missing data appropriately.

.. note::
    If *any* rows contain text that cannot be converted to a number (e.g. 'NaN' or 'NULL'), RiskScape \
    will assume it is not numeric, even if all the other rows are.

Type-detection currently only supports the `floating` numeric type.
In order to end up with the `integer` type, you would need to apply a type cast
in your bookmark, like this:

```ini
[bookmark buildings]
location = house_prices.csv
set-attribute.year = int(year)
set-attribute.cost = int(cost)
```

### Geometry

When type detection finds numeric latitude and longitude columns in your CSV file,
it can automatically create a `geometry` attribute. This makes it easy to use CSV files
containing point locations (such as building centroids) in your models.

RiskScape will add a geometry attribute when:

- numeric columns are found for latitude and longitude coordinates
- there is no other geometry attribute already present

The geometry value added will be a point in the EPSG:4326 CRS (i.e. WGS84).

The columns to use for the latitude and longitude coordinates is determined by:
- Looking for an exact match in column name. RiskScape looks for columns called either `longitude`, `long`, or `lon` for longitude, in that order, and `latitude` or `lat` for latitude.
- Searching for column names that contain `latitude` and `longitude` as sub-strings, e.g. `Building_latitude` or `Longitude_best_guess`.

In both cases the search:
- Is case insensitive, so `Lat` and `LAT` would both be acceptable latitude attribute names
- Uses the first match, so if `lat` and `custom_latitude` are both present then `lat` would be used.

.. note::
    RiskScape does not currently automatically detect columns containing Well-Known Text (WKT),
    e.g. ``POINT(174.7787 -41.2924)``.

### Disabling type detection

To disable type detection, simply add `type-detection = false` to your bookmark, e.g.

```ini
[bookmark my-csv-data]
location = PATH/TO/data.csv
type-detection = false
```

When RiskScape loads data from a CSV file with type-detection disabled, *every* column will be assigned the `text` type by default.

## Well-Known Text (WKT)

RiskScape does not automatically turn CSV data in Well-Known Text (WKT) format into geometry.
For example, using the following `cities.csv` file would simply result in a text-string `WKT` attribute by default.

```csv
name,WKT,population
Wellington,"POINT(-41.2924 174.7787)",1816000
Auckland,"POINT(-36.8509 174.7645)",520971
```

In order to load the data into a more usable format, you would need to define a bookmark like this:

```ini
[bookmark cities]
location = cities.csv
set-attribute.geometry = geom_from_wkt(WKT)
crs-name = EPSG:4326
```
.. tip::
    Whenever you create geometry manually in a bookmark, such as using ``geom_from_wkt()``,
    you should always specify the ``crs-name`` bookmark parameter as well.
    Otherwise RiskScape cannot tell what CRS the data is in.

### Coordinate Reference System quirks

Sometimes you may also have to specify the axis-order that the CRS is in,
i.e. whether the coordinates are in _lat, long_ or _long, lat_ order.
Sometimes the EPSG standard (particularly EPSG:4326) will define a _lat, long_ (or *northing, easting*) order,
however, GIS applications will typically work with the geometry in _long, lat_ (or *easting, northing*) order.

For example, say the `cities.csv` actually contained the following data, with the axis-order reversed
so that it is in _long, lat_ order:

```csv
name,WKT,population
Wellington,"POINT(174.7787 -41.2924)",1816000
Auckland,"POINT(174.7645 -36.8509)",520971
```

Simply loading the data using `geom_from_wkt()` would put it in a completely wrong location.
Because the data contains the longitude coordinate first, to load it correctly you would need
to add `crs-longitude-first` to the bookmark, like this:

```ini
[bookmark cities]
location = cities.csv
set-attribute.geometry = geom_from_wkt(WKT)
crs-name = EPSG:4326
crs-longitude-first = true
```

The Geometry Reference Guide has more details on :ref:`crs-lat-long`.

.. tip::
    The ``riskscape bookmark evaluate BOOKMARK_NAME`` command will produce
    a geospatial output file with all your bookmark's changes applied.
    This makes it easy to see what your data will actually look like when it is loaded by RiskScape.

## Other point coordinates

Sometimes your CSV data might contain point coordinates that RiskScape type-detection does not
load automatically, or the coordinates may be in another CRS that you need to specify manually.
For example, type-detection will not automatically create geometry for the following `cities.csv` file
because the column names do not match the 'lat', 'long' keywords at all.

```csv
name,POINT_Y,POINT_X,population
Wellington,-41.2924,174.7787,1816000
Auckland,-36.8509,174.7645,520971
```

Instead of `geom_from_wkt()`, in your bookmark you would use `create_point()`, like this:

```ini
[bookmark cities]
location = cities.csv
set-attribute.geometry = create_point(POINT_Y, POINT_X)
crs-name = EPSG:4326
```

.. note::
    The coordinate order for ``create_point()`` should always match the CRS that the geometry is in.
    In this case, EPSG:4326 specifies that coordinates are described in the ``y,x`` order.

.. tip::
    Using ``create_point()`` to create geometry can also be useful for other tabular data formats,
    like :ref:`NetCDF <netcdf>` or :ref:`HDF5 <openquake>`. Currently type-detection only creates
    geometry automatically for CSV input files.

.
