<commit_before>---
layout: page
title: "Language"
category: doc
date: 2002-01-01 12:00:00
order: 3
---

### Example recipe

The recipes are written in a special language. They look like this:

```python
from databaker.constants import *

def per_file(tableset):
    return tableset

def per_tab(tab):
    obs = tab.filter("MGSL").assert_one().shift(DOWN).fill(RIGHT).fill(DOWN).is_number().is_not_italic()

    tab.col('A').one_of(['Male', 'Female', 'All Persons']).dimension('gender', CLOSEST, ABOVE)
    tab.col('A').is_date().dimension(TIME, DIRECTLY, LEFT)
    tab.regex("All aged .*").dimension('ages', CLOSEST, ABOVE)
    tab.filter("Total economically active").fill(LEFT).fill(RIGHT).is_not_blank().dimension('indicator_', DIRECTLY, ABOVE)

    return obs
```

You can just edit or copy recipes to make basic changes. If you need to do anything
more complicated, it is worth knowing they are written in the Python programming
language. There's lots of online help and training for Python.

### Header line
```python
from databaker.constants import *
```

This is just boilerplate: it needs to be at the start of each file.

### Per File
```python
def per_file(tableset):
    ...
    return wanted_sheets
```

Here we say which tabs in the Excel spreadsheet we're interested in.

Options include:

* Explicit names of tables as strings
 
```python
def per_file(tabs):
    return "My Table"                    # one table called 'My Table'
    # or
    return ["This Table", "That Table"]  # two tables with specific names
    # or
    return "*"                           # all tables
```

* Using a list of table names to inform decisions:

```python
def per_file(tabs):
    tablist = tabs.names         # get a list of names
    tablist.discard('Notes')     # remove names from list
    tablist.discard('CV notes')
    return tablist
```

* Returning the tables themselves

```python
def per_file(tabs):
    return tabs  # all tables
```

This last one is potentially useful for selecting tables based on the contents of their cells:

```python
def per_file(tabs):
    for tab in tabs:
        if tab.excel_ref("A1").value == '2015':
            yield tab
```

### Per Tab
```python
def per_tab(tab):
    ....
    return observations
```

As sugggested by the name, this code is run for every tab in the spreadsheet. It needs to return the observations and tell the tab which cells are which headers.

These are done with:

```python
def per_tab(tab):
    tab.dimension('table_header', selector().value)  # get text describing sheet - dark blue
    tab.selector().dimension(GEOG, DIRECTLY, LEFT)  # places - red
    tab.selector().dimension('top_header', DIRECTLY, ABOVE)  # what the number is - green 
    obs = tab.selector()  # observations - yellow
    return obs
```

(in this example, `selector()` isn't an actual `databaker` function. It's
a placeholder for functions that pick relevant cells, e.g. `fill(RIGHT)`
or `.is_not_italic()`. For real selectors, see the
[function reference](function_reference.html).

![foo](../images/highlight.png)

#### .dimension(_name_, _string_)
#### .dimension(_name_, _method_, _direction_)

For dimensions that are the same for all observations on a tab, a literal string can be used. (This might be `tab.name`, the name of the tab, or a selector which selects a single cell followed by `.value`)

Otherwise, we need to say how to get from the observations to the headers: we need to specify whether we stay in the same column or row (`DIRECTLY`) or just need to get the closest in that direction, regardless (`CLOSEST`).

The region examined for headers typically includes the row/column the observation is in.

Note how `DIRECTLY` vs `CLOSEST` changes which cell is selected:

![foo](../images/strict_left.png)

[above: `DIRECTLY, LEFT`]

![foo](../images/loose_left.png)

[above: `CLOSEST, LEFT`]

This does lead to slightly counterintuitive behaviour when selecting `DIRECTLY` and `CLOSEST` cells which are off in one direction from the observations.<commit_msg>Update 2002-01-01-language.md<commit_after>---
layout: page
title: "Language"
category: doc
date: 2002-01-01 12:00:00
order: 3
---

### Example recipe

The recipes are written in a special language. They look like this:

```python
from databaker.constants import *

def per_file(tableset):
    return tableset

def per_tab(tab):
    obs = tab.filter("MGSL").assert_one().shift(DOWN).fill(RIGHT).fill(DOWN).is_number().is_not_italic()

    tab.col('A').one_of(['Male', 'Female', 'All Persons']).dimension('gender', CLOSEST, ABOVE)
    tab.col('A').is_date().dimension(TIME, DIRECTLY, LEFT)
    tab.regex("All aged .*").dimension('ages', CLOSEST, ABOVE)
    tab.filter("Total economically active").fill(LEFT).fill(RIGHT).is_not_blank().dimension('indicator_', DIRECTLY, ABOVE)

    return obs
```

You can just edit or copy recipes to make basic changes. If you need to do anything
more complicated, it is worth knowing they are written in the Python programming
language. There's lots of online help and training for Python.

### Header line
```python
from databaker.constants import *
```

This is just boilerplate: it needs to be at the start of each file.

### Per File
```python
def per_file(tableset):
    ...
    return wanted_sheets
```

Here we say which tabs in the Excel spreadsheet we're interested in.

Options include:

* Explicit names of tables as strings
 
```python
def per_file(tabs):
    return "My Table"                    # one table called 'My Table'
    # or
    return ["This Table", "That Table"]  # two tables with specific names
    # or
    return "*"                           # all tables
```

* Using a list of table names to inform decisions:

```python
def per_file(tabs):
    tablist = tabs.names         # get a list of names
    tablist.discard('Notes')     # remove names from list
    tablist.discard('CV notes')
    return tablist
```

* Returning the tables themselves

```python
def per_file(tabs):
    return tabs  # all tables
```

This last one is potentially useful for selecting tables based on the contents of their cells:

```python
def per_file(tabs):
    for tab in tabs:
        if tab.excel_ref("A1").value == '2015':
            yield tab
```

### Per Tab
```python
def per_tab(tab):
    ....
    return observations
```

As sugggested by the name, this code is run for every tab in the spreadsheet. It needs to return the observations and tell the tab which cells are which headers.

These are done with:

```python
def per_tab(tab):
    tab.dimension('table_header', selector().value)  # get text describing sheet - dark blue
    tab.selector().dimension(GEOG, DIRECTLY, LEFT)  # places - red
    tab.selector().dimension('top_header', DIRECTLY, ABOVE)  # what the number is - green 
    obs = tab.selector()  # observations - yellow
    return obs
```

(in this example, `selector()` isn't an actual `databaker` function. It's
a placeholder for functions that pick relevant cells, e.g. `fill(RIGHT)`
or `.is_not_italic()`. For real selectors, see the
[function reference](function_reference.html).

![foo](../images/highlight.png)

You can return multiple segments by using `yield`. This will lead to the previous headers being wiped; you can use variables to avoid having to recalculate the unchanged headers.

The output probably won't be in the correct order, however: each tab is processed in turn, rather than each segment. You can use `sortcsv --ons <csvfile>` to change the file in-place.

#### .dimension(_name_, _string_)
#### .dimension(_name_, _method_, _direction_)

For dimensions that are the same for all observations on a tab, a literal string can be used. (This might be `tab.name`, the name of the tab, or a selector which selects a single cell followed by `.value`)

Otherwise, we need to say how to get from the observations to the headers: we need to specify whether we stay in the same column or row (`DIRECTLY`) or just need to get the closest in that direction, regardless (`CLOSEST`).

The region examined for headers typically includes the row/column the observation is in.

Note how `DIRECTLY` vs `CLOSEST` changes which cell is selected:

![foo](../images/strict_left.png)

[above: `DIRECTLY, LEFT`]

![foo](../images/loose_left.png)

[above: `CLOSEST, LEFT`]

This does lead to slightly counterintuitive behaviour when selecting `DIRECTLY` and `CLOSEST` cells which are off in one direction from the observations.
{'fraction_non_alphanumeric': 0.1102792632204397, 'fraction_numerical': 0.00570409982174688, 'mean_word_length': 3.7845366685616826, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4527067', 'n_tokens_mistral': 2567, 'n_tokens_neox': 2395, 'n_words': 1012}