Vahtian

Clean and prepare your data

“My survey export has two header rows and won’t load.”

Turn a SurveyMonkey, Qualtrics, or Forms export into a tidy dataset R and Python can read.

You have the export, the deadline is close, and it will not load. You paste it into R or Python and the columns are named after questions, the answers have vanished, and the numbers sit one row low. Many people fix it by hand, copying answers into a new spreadsheet and hoping they introduce no new errors. You do not have to. Three short steps turn the export into a tidy dataset that R and Python can read.

By Heidi Andersén, MD, PhD · Updated 18 July 2026 · 6 min read

The problemTwo headers, and blanks that mean “same as left”

Open the raw file first and look. Row one holds the question, but only in the first column of each group; the cells after it are blank because the tool merged them. Row two holds the answer option for each column. The real data starts on row three. read_csv() assumes the first row contains column names. Survey exports often do not.

Step 1 and 2Fill the merged cells, then combine the two rows

Read with no header, carry the question rightwards across its blank cells, join it to the option row, and drop the two header rows from the data. The same shape in both languages:

R

library(tidyverse)
raw <- read_csv("export.csv", col_names = FALSE)
h1  <- raw[1, ] |> unlist() |> zoo::na.locf(na.rm = FALSE)  # carry question right
h2  <- raw[2, ] |> unlist() |> replace_na("")
nm  <- janitor::make_clean_names(paste(h1, h2, sep = "_"))
dat <- raw[-c(1, 2), ] |> set_names(nm)

Python

import pandas as pd
raw = pd.read_csv("export.csv", header=None)
h1  = raw.iloc[0].ffill()            # carry question right
h2  = raw.iloc[1].fillna("")
cols = (h1.astype(str) + "_" + h2.astype(str)).str.strip("_")
dat = raw.iloc[2:].copy()
dat.columns = cols

You now have one clean header row and typed-in data. Check the column count matches what you expect before going on.

If your export contains three header rows instead of two, the same approach applies: combine all the header rows into a single set of column names before importing the data.

Step 3Reshape matrix questions to long

A matrix question (one question, many sub-rows) arrives as many columns. For most analysis you want it long: one row per respondent per item. Pivot the matrix columns with tidyr::pivot_longer() in R or pandas.melt() in Python, keeping the respondent id as the anchor. Multi-select answers export as one column per option already coded 0/1, which is usually what you want, so leave those wide.

ChecklistBefore you analyse

Four checks

  • You looked at the raw file and counted the header rows (two, sometimes three).
  • Merged question cells were carried right before combining, so no column is named just after its option.
  • The final column count matches the survey.
  • Matrix questions are reshaped long; multi-select stays wide and coded.

Related guidesRead next

DictVahti creates a first draft of the data dictionary directly from the cleaned headers. You review it against the original survey before collection or analysis.