# Move a Spreadsheet Labeling Job Into Potato

Source: https://www.potatoannotator.com/blog/move-spreadsheet-labeling-job-into-potato

Teams that label rows in Excel or Google Sheets can follow this tutorial to move the job into Potato 2.13.1, with two annotators relabeling 40 sentences without seeing each other's labels or the labels already in the sheet. At the end you have an agreement number for the two annotators, a comparison with the sheet's labels, and a CSV with one row per sentence and one column per annotator that opens in the same spreadsheet program. A spreadsheet works well for one person labeling a few hundred rows, and [Build or Buy an Annotation Tool](/docs/guides/build-or-buy-annotation-tool) describes where a spreadsheet stops working once a second person joins. No earlier experience with annotation tools is assumed.

## The sheet

The sentences come from the [Financial PhraseBank](https://huggingface.co/datasets/takala/financial_phrasebank) (reference 1), a set of 4,840 sentences from financial news. Sixteen people with a finance background labeled each sentence as positive, negative, or neutral for an investor, with 5 to 8 annotators per sentence. The release is licensed under [CC BY-NC-SA 3.0](https://creativecommons.org/licenses/by-nc-sa/3.0/). A sheet built from the sentences may be shared for noncommercial use only, with attribution and under the same license.

The tutorial's sheet has 40 sentences drawn at random. Twenty come from the release's file of sentences on which every original annotator agreed, and twenty from sentences on which only 50% to 66% agreed. The `label` column has the release's majority label and stands in for the labels a colleague already typed into the sheet. The `agreement` column records which group each sentence came from. Three rows of the sheet are shown below:

```csv
id,sentence,label,agreement
s08,"Finnish financial group Aktia reports operating profit of EUR 44.4 mn in January-September 2009 , up from EUR 37.3 mn in the corresponding period in 2008 .",positive,100%
s16,"It generated an operating loss of EUR 96.3 mn , down from a profit of EUR 43.9 mn .",negative,100%
s35,It is the most awkward thing to use if you 're holding the phone with one hand .,negative,50-65%
```

Your own sheet needs an `id` column with a different value in every row. If the sheet has no ID column, add a column of row numbers or codes such as `s01`.

## Saving the sheet as CSV

Potato reads CSV, TSV, JSON, JSON Lines, and Parquet files ([Data Formats](/docs/core-concepts/data-formats)), and Potato does not read Excel workbooks. In Google Sheets, choose File, then Download, then Comma Separated Values (.csv). In Excel, use Save As with the type "CSV UTF-8". A workbook named in the config stops the server with `Unsupported input file format xlsx`. To convert a workbook with Python instead, use pandas, which Potato installs, and openpyxl (`pip install openpyxl`):

```python
import pandas as pd

pd.read_excel("sheet.xlsx", dtype=str).to_csv("sheet.csv", index=False)
```

`dtype=str` reads every cell as text. Without the option, pandas reads the ID `007` as the number 7.0 in a column with one empty cell. Potato reports a problem in the file when the server starts, and the test run hit each of the problems below:

- **Accented letters stop the server with a `UnicodeDecodeError`.** Excel's plain "CSV" type writes an older Windows encoding. Save as "CSV UTF-8", or name the encoding in the config by writing the `data_files` entry as `- {path: data/items.csv, encoding: cp1252}`.
- **Blank rows at the bottom give `Empty ID in column 'id'`.** Delete the empty rows in the sheet before saving.
- **Semicolons between cells give a cell-count error.** Excel in some European locales separates cells with semicolons. Change the separator, or export from Google Sheets.
- **An upper-case `.CSV` extension was refused in 2.12.1.** Potato 2.13.1 accepts the file, and on 2.12.1 rename the file to end in `.csv`.

## Keeping the old labels away from annotators

Potato sends every column of a row to the annotator's browser, and the whole row sits in the page source of the annotation page. The test run found the sheet's `label` value in the page source of every item, although the page showed only the sentence. To keep the old labels away from annotators, give Potato a file with only the `id` and `sentence` columns, and keep `sheet.csv` for the comparison later:

```python
import pandas as pd

pd.read_csv("sheet.csv", dtype=str)[["id", "sentence"]].to_csv("data/items.csv", index=False)
```

To make the file in the spreadsheet instead, copy the tab, delete the label columns from the copy, and download the copy as CSV.

From Potato 2.13.1, `item_properties.private_keys: [label]` is another way to keep the column away from annotators. Potato then leaves the `label` column out of everything Potato sends to the browser, and the column stays in the exports.

## The config

Install Potato and make a project folder with `sheet.csv`, `data/items.csv`, and the config below:

```bash
pip install potato-annotation==2.13.1
```

The config is complete and passes `python -m potato.validate_cli config.yaml --strict` on 2.12.1 and on 2.13.1:

```yaml
annotation_task_name: Financial news sentiment
task_dir: .
output_annotation_dir: annotation_output/
data_files:
  - data/items.csv
item_properties:
  id_key: id
  text_key: sentence

user_config:
  allow_all_users: true
  users: []

num_annotators_per_item: 2

annotation_schemes:
  - annotation_type: radio
    name: sentiment
    description: Would this news move the company's share price up, down, or not at all?
    labels: [positive, neutral, negative]
```

`id_key` and `text_key` name the two columns of `data/items.csv`. `num_annotators_per_item: 2` sends every sentence to two annotators. The `radio` scheme gives each sentence one answer from three labels. The label names match the spelling in the sheet's `label` column, and the two sets of labels compare without recoding. [Annotation Schemes](/docs/core-concepts/annotation-schemes) lists the other question types, and [Configuration Basics](/docs/getting-started/configuration-basics) explains the rest of the keys.

## Labeling

Start the server from the project folder:

```bash
potato start config.yaml -p 8000
```

Each annotator opens `http://localhost:8000`, registers with a username and password, and sees one sentence at a time with the three labels below the sentence. Keys 1, 2, and 3 pick a label, and Next saves the answer. A colleague on another computer needs the server at an address that computer can reach, as [Deploying Potato on a VPS](/docs/deployment/deploy-vps) describes.

The numbers on this page come from a run on Potato 2.12.1 with two scripted annotators, ana and ben, who labeled all 40 sentences through the annotation page in a headless browser. The first annotator's labels came from reading each sentence. The second annotator's labels were written separately and differ from the first on uncertain sentences. The numbers therefore describe two simulated annotators and say nothing about the scheme's reliability with real annotators.

## Agreement between the two annotators

The admin page reports agreement while labeling is still going on. Open `http://localhost:8000/admin/iaa` and type the admin key that Potato writes to `admin_api_key.txt` in the project folder the first time someone asks for an admin page. With the two scripted annotators, the page reported 40 items labeled by both annotators, Krippendorff's alpha of 0.580, Cohen's kappa of 0.575, and agreement on 75% of the sentences. [Cohen's kappa](https://en.wikipedia.org/wiki/Cohen%27s_kappa) and [Krippendorff's alpha](https://en.wikipedia.org/wiki/Krippendorff%27s_alpha) both discount the agreement two annotators would reach by chance. [Inter-Annotator Agreement](/docs/guides/inter-annotator-agreement) explains how to read the two coefficients.

For a report, run the reliability export once labeling is done:

```bash
python -m potato.export --config config.yaml --format reliability --output export/
```

The command writes `export/reliability.csv` and `export/reliability.md`. The table for the two scripted annotators:

```text
| Variable | Level | Krippendorff's α | 95% CI | % agreement | Units | Coders |
|---|---|---|---|---|---|---|
| sentiment | nominal | 0.58 | [0.303, 0.8] | 75.0 | 40 | 2 |
```

The interval runs from 0.303 to 0.8 because 40 sentences is a small sample. A larger sample narrows the interval.

## Labels back in the sheet's layout

Potato's CSV export has one row per annotator per sentence, and one column per label:

```bash
python -m potato.export --config config.yaml --format csv --output export/
```

The command writes `export/annotations.csv`, with 80 rows for the 40 sentences:

```csv
instance_id,user_id,sentiment.positive,sentiment.negative,sentiment.neutral
s27,ana,positive,,
```

A spreadsheet user usually wants the layout of the original sheet instead. The script below folds the three label columns into a single `sentiment` column, turns each annotator into a column, and puts the columns next to the original sheet:

```python
"""One row per sentence, one column per annotator, next to the original sheet."""
import pandas as pd

sheet = pd.read_csv("sheet.csv", dtype=str)
potato = pd.read_csv("export/annotations.csv", dtype=str)

# Potato writes one column per option (sentiment.positive, ...); keep whichever is filled
potato["sentiment"] = potato.filter(like="sentiment.").bfill(axis=1).iloc[:, 0]
wide = potato.pivot(index="instance_id", columns="user_id", values="sentiment")

out = sheet.merge(wide, left_on="id", right_index=True, how="left")
out.to_csv("labels_by_annotator.csv", index=False)
```

Run the script with `python back_to_sheet.py` from the project folder. `labels_by_annotator.csv` has the sheet's columns followed by `ana` and `ben`:

```text
 id   label agreement      ana      ben
s01 neutral    50-65%  neutral positive
s02 neutral    50-65% negative  neutral
s03 neutral      100%  neutral  neutral
s04 neutral    50-65% positive positive
```

The `--format tsv` export writes the same rows as `export/annotations.tsv`, for a script that expects tabs.

## Agreement with the sheet

The site's [agreement calculator](/tools/inter-annotator-agreement) reads a table with the item in the first column and one annotator per column after the first. Paste the `id`, `label`, and `ana` columns of `labels_by_annotator.csv` into the calculator, then the `id`, `label`, and `ben` columns. The test run gave these values:

| Compared with the sheet | Krippendorff's α | Cohen's κ | Same label |
|---|---|---|---|
| ana | 0.745 | 0.743 | 34 of 40 |
| ben | 0.563 | 0.558 | 30 of 40 |

All 13 rows with any disagreement among ana, ben, and the sheet came from the 20 sentences on which the original annotators had split. On the 20 sentences every original annotator agreed on, ana, ben, and the sheet gave the same label every time. On three rows, ana and ben agreed with each other and not with the sheet. The sheet's labels on the three rows are the first ones to check. To find such rows in a larger sheet, open `labels_by_annotator.csv` in the spreadsheet and filter for rows where the `ana` or `ben` column differs from `label`.

## Bringing the sheet's labels into Potato

With the steps above, the sheet's labels stay out of Potato and the two annotators label independently of the sheet. A team that wants to keep working from the sheet's labels has two other routes.

**The sheet's label as a starting answer.** When the goal is to review and correct the sheet rather than measure agreement with the sheet, Potato can preselect the sheet's label on every sentence. The script below writes the labels to `data/prelabels.json`, keyed by row ID:

```python
import csv
import json

with open("sheet.csv", encoding="utf-8") as f:
    prelabels = {row["id"]: {"sentiment": row["label"]} for row in csv.DictReader(f) if row["label"]}

with open("data/prelabels.json", "w", encoding="utf-8") as f:
    json.dump(prelabels, f, indent=1)
```

Add this block to the config above:

```yaml
pre_annotation:
  enabled: true
  predictions_file: data/prelabels.json
```

In the test run, the sheet's label was already selected on all 40 sentences when each sentence opened. An annotator who clicks Next without changing the answer saves the sheet's label as the annotator's own. Agreement with the sheet on pre-labeled sentences is therefore not agreement between independent judgments. Potato 2.12.1 printed a note that `pre_annotation` was planned for removal in Potato 3.0. Potato 2.13.1 dropped the note, because `pre_annotation` is how Potato's importers bring in existing answers. The [labeling engine](/docs/labeling-engine/migration) has a `static` source for labels that a contract has to certify.

**The sheet's columns as annotators.** A sheet with one column per person can come into Potato as those people's saved work. The admin page and the reliability export then include the people who labeled in the sheet. The route goes through Potato's Label Studio importer ([Importing Projects](/docs/tools/importing-projects)). The script below writes each named column as one person's answers in Label Studio's export format:

```python
"""Write each annotator column of sheet.csv as that person's Label Studio annotations."""
import csv
import json
import sys

columns = sys.argv[1:]  # the sheet's annotator columns
tasks = []
with open("sheet.csv", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        tasks.append({
            "id": row["id"],
            "data": {"sentence": row["sentence"]},
            "annotations": [
                {"completed_by": {"email": col},
                 "result": [{"from_name": "sentiment", "to_name": "sentence",
                             "type": "choices", "value": {"choices": [row[col]]}}]}
                for col in columns if row[col]
            ],
        })
with open("sheet_tasks.json", "w", encoding="utf-8") as f:
    json.dump(tasks, f, ensure_ascii=False)
```

Without a labeling config, the importer builds the question from the labels in use and leaves out a label nobody chose. Save the question's three labels as `sheet.xml`:

```xml
<View>
  <Text name="sentence" value="$sentence"/>
  <Choices name="sentiment" toName="sentence">
    <Choice value="positive"/><Choice value="neutral"/><Choice value="negative"/>
  </Choices>
</View>
```

Run the script with the names of the annotator columns, then the import:

```bash
python sheet_to_tasks.py label
potato import -i sheet_tasks.json -f label_studio --ls-config sheet.xml --keep-annotators -o imported
```

The import writes a new project in `imported/` with a config, the sentences as `data/sheet_tasks.jsonl`, and the column's 40 labels saved as the work of a user named `label`. The new config lets only the imported names log in, with no password. To add ana and ben, add the two names to the `user_config` block of `imported/config.yaml`, and add a `num_annotators_per_item` line after the block:

```yaml
user_config:
  allow_all_users: false
  users:
  - label
  - ana
  - ben
num_annotators_per_item: 3
```

With `num_annotators_per_item: 3`, each sentence goes to the sheet's user and the two annotators, and the admin page scores a sentence once all three have answered. In the test run, the admin page and the reliability export reported Krippendorff's alpha of 0.628 across the three, with a 95% interval from 0.414 to 0.8. Before the server goes on a network, set `require_password: true` and give each user in `imported/annotation_output/user_config.json` a password, as the import's README says.

## Reading straight from Google Sheets

Potato reads rows directly from a Google Sheet through the `google_sheets` [data source](/docs/features/remote-data-sources), without a download step. The test run did not reach a live sheet, because the source needs Google credentials. The config below passes the validator, and the setup steps come from Potato's source code and Google's documentation.

Potato signs in to Google with a service account and asks only for read access to sheets. Create a project in the Google Cloud console, turn on the Google Sheets API, create a service account, and download the account's JSON key into `credentials/` in the project folder. Then share the sheet with the address in the key's `client_email` field, as you would with a colleague. Install the client libraries with `pip install google-api-python-client google-auth`, and replace the `data_files` block in the config above with:

```yaml
data_sources:
  - type: google_sheets
    spreadsheet_id: "YOUR_SPREADSHEET_ID"
    sheet_name: Sheet1
    range: "A:B"
    credentials_file: credentials/service_account.json
```

The spreadsheet ID is the long string between `/d/` and `/edit` in the sheet's URL. `range: "A:B"` reads only the first two columns and leaves the label columns out of Potato, provided `id` and `sentence` are columns A and B. Potato reads the `credentials_file` path relative to the project folder.

## Where to go next

- [Build or Buy an Annotation Tool](/docs/guides/build-or-buy-annotation-tool), for when a spreadsheet is enough and when a tool pays off
- [Data Formats](/docs/core-concepts/data-formats), for the file formats Potato reads and the fields each row needs
- [Remote Data Sources](/docs/features/remote-data-sources), for reading from URLs, cloud storage, databases, and Google Sheets
- [Export Formats](/docs/features/export-formats), for every export format and its options
- [Inter-Annotator Agreement](/docs/guides/inter-annotator-agreement), for choosing and reading an agreement coefficient
- [Quality Control](/docs/features/quality-control), for pre-annotation settings, attention checks, and gold items
- [Text annotation in Potato](/use-cases/text-annotation), for the other text tasks Potato runs

## References

1. Malo, P., Sinha, A., Korhonen, P., Wallenius, J., & Takala, P. (2014). Good debt or bad debt: Detecting semantic orientations in economic texts. *Journal of the Association for Information Science and Technology*, 65(4), 782-796. https://doi.org/10.1002/asi.23062