Move a 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 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 (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. 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:

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), 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):

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:

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:

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:

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 lists the other question types, and Configuration Basics explains the rest of the keys.

Labeling

Start the server from the project folder:

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 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 and Krippendorff's alpha both discount the agreement two annotators would reach by chance. Inter-Annotator Agreement explains how to read the two coefficients.

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

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:

| 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:

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

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

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:

"""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:

 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 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:

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:

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:

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 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). The script below writes each named column as one person's answers in Label Studio's export format:

"""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:

<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:

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:

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, 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:

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, for when a spreadsheet is enough and when a tool pays off
• Data Formats, for the file formats Potato reads and the fields each row needs
• Remote Data Sources, for reading from URLs, cloud storage, databases, and Google Sheets
• Export Formats, for every export format and its options
• Inter-Annotator Agreement, for choosing and reading an agreement coefficient
• Quality Control, for pre-annotation settings, attention checks, and gold items
• Text annotation in Potato, 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