select_multiple columns

The problem

"Which of these does the household own?" is one question with seven possible answers and any number of them chosen. Kobo stores that as one cell:

radio phone bicycle

You cannot group by it, you cannot count it, and the obvious fix produces percentages that add up to more than a hundred without telling you why.

What you will have at the end

One true/false column per asset, a correct count for each, and a clear head about which denominator a percentage belongs over.

The steps

See what is in there

import pandas as pd

df = pd.read_csv("household-survey-messy.csv", dtype=str, keep_default_na=False)

assets = df["assets"].str.strip()
print("blank:", (assets == "").sum())
print("max codes in one cell:", assets[assets != ""].str.split().str.len().max())

blank: 28
max codes in one cell: 4

The 28 blanks are the households that declined the interview — skip logic, not missing data, exactly as in the missing-data lesson.

Count the codes, not the cells

value_counts() on the raw column counts combinations, which is almost never what you want. Split first, then explode:

codes = assets[assets != ""].str.split().explode()
print(codes.value_counts())

tv            245
radio         235
phone         232
bicycle       231
fridge        220
motorcycle    205
none           42

.explode() turns a column of lists into one row per element. It is the single most useful function in this lesson.

Make one column per code

dummies = (
    assets[assets != ""]
    .str.split()
    .explode()
    .str.get_dummies()
    .groupby(level=0).max()
)

df = df.join(dummies.add_prefix("has_"), how="left")
print(df.filter(like="has_").sum().sort_values(ascending=False))

The groupby(level=0).max() collapses the exploded rows back to one row per household. Without it you get one row per asset and every later count doubles.

Get the denominator right

This is where percentages go wrong.

asked = df[assets != ""]          # households that were actually asked
print(f"own a radio: {asked['has_radio'].sum()} of {len(asked)} "
      f"({asked['has_radio'].mean():.1%})")

Over the 590 households that answered, not over all 618 rows and not over the 600 households. Three different denominators, three different numbers, and only one of them answers the question.

Percentages across assets will still sum to more than 100%, and that is correct — a household can own several things. Say so in the chart title: "percentage of households owning each item", not "distribution of assets".

Where it breaks

The trailing space

print((df["assets"] != df["assets"].str.strip()).sum())

27 cells have a trailing space. .str.split() with no argument handles that correctly — it splits on runs of whitespace and discards empties. But .str.split(" ") does not: it produces an empty string as a final element, and that empty string becomes a column called "" in your dummies.

Use .str.split(). Never .str.split(" ") on survey data.

"none" treated as an asset

none is a real code here and it means the opposite of the others. Check nobody has selected it alongside something else:

combos = assets[assets != ""].str.split()
print((combos.apply(lambda x: "none" in x and len(x) > 1)).sum())

Zero, so the form's validation held. When this is not zero you have found a form that let enumerators tick "none" and "radio" together, and you need a rule before you can count anything.

Counting cells instead of codes

assets.value_counts().head(3)     # counts COMBINATIONS - almost never useful

If you see a count of 1 against bicycle motorcycle radio fridge, you are counting combinations. Explode first.

Codes that are not in the form

Compare what is in the data against the choice list from the XLSForm. A code that appears in the data but not in the form means the form changed mid-round, and your series has a break in it.

Your turn

Produce a table of asset ownership by district: one row per district, one column per asset, holding the percentage of households that were asked.

Then answer: which district has the highest share owning a phone, and how many households is that share based on? A percentage from eleven households is not the same finding as one from ninety.

Join the roster to the household — one-to-many, and the 88 people who do not exist.