Open a Kobo export without losing data

The problem

You have an export from Kobo. You open it, the shape looks about right, and you start working. Three hours later a total is wrong and you trace it back to the first line of code you wrote.

Reading the file is not a formality. It is where most cleaning errors are made, because pandas makes a series of reasonable guesses and never mentions them.

What you will have at the end

The survey loaded, an honest list of what every column actually contains, and the habit of looking before you touch anything.

The dataset

Download household-survey-messy.csv from the link above: 600 households from a fictional survey across six districts, plus a few duplicates. It is generated, not real, and it is deliberately broken in the exact ways real exports are broken.

The steps

Load it and look

import pandas as pd

df = pd.read_csv("household-survey-messy.csv")
print(df.shape)
print(df.columns.tolist())

(618, 16)

618 rows for 600 households. Hold that thought — we come back to it in a later lesson.

Look at what pandas decided

This is the step people skip.

print(df.dtypes)

Two of these are worth stopping on:

Neither is a bug. Both are pandas doing something sensible with what it was given, and both will surprise you later if you did not look now.

Read it as it actually is

For a first pass, stop pandas guessing at all:

raw = pd.read_csv(
    "household-survey-messy.csv",
    dtype=str,              # every column as text
    keep_default_na=False,  # do not turn "NA" into a blank
)

keep_default_na=False is the important one. By default pandas treats NA, N/A, null and a dozen other strings as missing — which quietly erases the difference between a blank cell and someone having typed NA. Those are not the same thing, and the next lesson but one is entirely about why.

Now count what is really in a column:

print(raw["consent"].value_counts())

Eleven different spellings of yes and no. That is a real export.

Where it breaks

The file with two header rows

If someone ticked "include labels" when exporting, the file has the field names on row one and the question text on row two:

lab = pd.read_csv("household-survey-with-labels.csv")
print(lab.shape)              # (619, 16) - one row too many
print(lab.iloc[0]["district"])  # 'District'  <- a question label, not data

Every column is now text, because the first "value" in each is a sentence. Skip the label row:

lab = pd.read_csv("household-survey-with-labels.csv", skiprows=[1])

Note skiprows=[1] — a list, meaning "skip row index 1". Passing skiprows=1 without the brackets skips the header instead and loses your column names.

The invisible character at the start of the file

Exports often carry a byte order mark: three invisible bytes before the first column name. The symptom is a KeyError on a column you can plainly see.

pandas 3 strips it for you, so you may never meet it there. Other tools do not:

import csv

with open("household-survey-messy.csv", encoding="utf-8") as f:
    print(next(csv.reader(f))[0])      # '_uuid'  <- not '_uuid'

with open("household-survey-messy.csv", encoding="utf-8-sig") as f:
    print(next(csv.reader(f))[0])      # '_uuid'

utf-8-sig means "UTF-8, and discard the mark if it is there". It is safe on files that do not have one, so when you are reading a file you did not create, use it by default.

The ID that became a number

Not in this dataset, but it will happen to you: an identifier like 0012345 read as a number loses its leading zero, and 3.1E5 becomes 310000.0. If a column is an identifier, say so:

df = pd.read_csv("export.csv", dtype={"household_id": str})

Your turn

Load the messy file twice — once with defaults, once with dtype=str and keep_default_na=False — and compare monthly_income_ugx between them.

Count how many cells differ. Those are the values pandas decided were missing on your behalf. One of them is a household that answered.

Next

Select, filter, rename — the four operations that do most of the work.