A Survey Export With Hundreds of Columns Made Usable

Survey platforms export one row per respondent and one column per question. With ten questions that is a fine table. With three hundred, it is a spreadsheet nobody can read, a database table that is awkward to query, and a file where adding a question changes the schema. Analysis wants the other shape: one row per answer, with the respondent, the question and the value, so that "average by question" is a GROUP BY and "answers by country" is a join. Turning wide into long is one of the most useful moves in data wrangling, and in DuckDB it takes a single statement that does not list the columns. This article does it on a 302-column file, shows the proof that no answer was lost and covers the question metadata that makes the result readable. It is one of the problems in worked CSV problems and the patterns behind them.

The wide file

The first columns identify the respondent, and the rest are the questions. A trimmed view of the header and a row:

respondent,country,Q1,Q2,Q3,Q4,Q5,...,Q299,Q300
R001,DE,3,,1,5,,...,2,4

(The ... stands for the columns elided here; the file has all 300.) Values are answers on a one-to-five scale, and an empty cell means the question was not asked, was skipped or was not shown because of a branch. DuckDB reports 302 columns for the file, which is what a spreadsheet would show after scrolling for a while.

Read it as text first, since an empty column of answers can be inferred as a number or a boolean depending on what the sniffer sees, and you will cast on purpose after the reshape:

CREATE TABLE wide AS SELECT * FROM read_csv('survey.csv', all_varchar=true);

Unpivot with a pattern

UNPIVOT turns columns into rows. The old way lists every column to unpivot, which for 300 columns is a long and fragile statement. DuckDB lets you select the columns with a regular expression through COLUMNS:

CREATE TABLE long_t AS
SELECT respondent, country, question, answer::INTEGER AS answer
FROM (UNPIVOT wide ON COLUMNS('^Q\d+$') INTO NAME question VALUE answer)
WHERE answer <> '';

The pattern ^Q\d+$ selects columns named Q followed by digits, which excludes respondent and country. INTO NAME question VALUE answer names the two new columns: the old column name goes into question and the cell goes into answer. The columns not mentioned, the respondent and the country, are kept on every output row. The WHERE clause drops blank cells, and the cast to integer happens after the unpivot, so a non-numeric answer would raise an error that points at a value instead of making the column text.

A new question in next month's export, Q301, is picked up by the pattern with no change to the statement, which is the main practical gain over listing the columns. The converse is a hazard: a column that matches the pattern but is not a question, such as a Q4_comment free-text field, would not match ^Q\d+$ and be left out, and one called Q99 that holds a comment would be pulled in and fail the integer cast, which is the behavior you want. Decide the pattern from the platform's naming scheme and check what it selects: SELECT column_name FROM (DESCRIBE wide) WHERE regexp_matches(column_name, '^Q\d+$') lists them.

The proof: count the answers two ways

The long table should hold exactly the non-blank question cells of the original. Compute that count in the wide table without unpivoting, and compare:

SELECT count(*) AS answers, count(DISTINCT respondent) AS people, count(DISTINCT question) AS questions
FROM long_t;

answers  people  questions
1057     5       299

1,057 answers from 5 people across 299 distinct questions. Five respondents by 300 questions is 1,500 cells, and about 70 percent were filled. To verify 1,057 independently, count in the original, for example with a script that reads the file with the csv module and counts non-empty cells in the question columns, or with a SQL expression that sums (Q1 <> '')::INT + (Q2 <> '')::INT ... generated for all 300 columns. The two numbers must agree. If they do not, the pattern missed a column, a cell held whitespace that <> '' did not catch (trim it first), or a header was duplicated.

The 299 is also a result: one of the 300 questions had no answers from any of the five respondents. In a real survey, that is worth knowing, as a question nobody answered may be a branch no one reached or a broken form.

Analysis in long form

The reshape turns wide questions into ordinary SQL. Answers per question and the mean for the most-answered ones:

SELECT question, count(*) AS n, round(avg(answer), 2) AS mean
FROM long_t GROUP BY question ORDER BY n DESC, question LIMIT 3;

question  n  mean
Q102      5  3.0
Q105      5  1.8
Q106      5  1.8

And by group, joining nothing: the respondent's country travels with every answer, so the breakdown is a group by:

SELECT country, round(avg(answer), 2) AS mean_answer, count(*) AS answers
FROM long_t GROUP BY country ORDER BY country;

country  mean_answer  answers
DE       3.10         213
FR       2.89         228
GB       3.00         616

Those figures are from random data and mean nothing. What matters is the form: one GROUP BY, no column names in the query. Note the counts, since GB has 616 answers because three respondents were in GB. A mean over 213 answers from one person is not 213 independent observations. If you want to compare countries, the unit is the respondent, so average each respondent first and then compare the respondents' averages, or the analysis overstates its certainty (comparing two time periods in a CSV makes the same point about bases).

Questions need names

Q102 means nothing to a reader. Keep the question text, the scale and the group in a small table, usually exported from the survey tool as a separate file or as the second header row of the export:

question,text,scale,topic
Q102,How satisfied are you with delivery?,1-5,delivery
Q105,Would you recommend us?,1-5,loyalty

Join it to the long table, and every query gains words. A second header row of question text in the export itself, common in survey tools, is the multi-line header problem, and skip plus a separate read of that row gives the text (parsing multi line headers in report style CSV files). Also separate scale types: a 1-to-5 rating, a yes/no, a multiple-choice code and a free-text answer cannot share one answer column without casting, so either keep answer as text and cast per question type using the metadata, or split the long table by type. Multiple-select questions often arrive as several columns (Q40_1, Q40_2, Q40_3, one per option), which the long form turns into question = 'Q40_2' rows; split the name into a question and an option with string_split if you need to group them.

When the wide shape is the right one

Do not unpivot everything by reflex. A wide table suits tools that want a feature matrix, such as a statistics package or a model, where each question is a variable and each respondent a case: reading the long form back into a matrix is another pivot. It also suits questions you will always analyze together, a battery of ten items that make a scale, where the mean across the items per respondent is a one-line expression. And a 302-column table is fine to store in Parquet or a database, where columns are cheap, even if it is miserable to read in a spreadsheet.

Use the long form for exploring, counting and joining, and the wide form for modeling and for the export you give back. Keep both reproducible from the raw file with a script. For very large survey files, convert once to Parquet to avoid reparsing 300 columns each time (why Parquet beats CSV for repeated analysis). The statement is documented in DuckDB's UNPIVOT reference, and its CSV overview covers reading wide files.