Long Excel Format

1. Overview

The Long format is designed for detailed, question-by-question analysis. Each row represents a single respondent's answer to a single survey question. This format is particularly useful when you want to:

• Explore how different questions were answered across respondents.
• Pivot, filter, and group responses in tools like Excel, R, or Python.
• Work with multi-choice and grid questions without needing to manage many columns.

2. File Structure & Layout

Each row corresponds to one answer to one option of one question from a respondent. Respondents therefore have multiple rows, one for each question they encountered.

Example rows

These selected rows and columns come from a raw export. The full file also includes line numbers, types, timestamps, weights and survey tags:

respondent_id	status	question	reporting_id	response	raw_response	responded
respondent_00000	Complete	Age	how-old-are-you	55+	62	1
respondent_00000	Complete	What is your gender?	what-is-your-gender	Male	Male	1
respondent_00000	Complete	What is your gender?	what-is-your-gender	Female	Female	0

3. Key Columns

• respondent_id - Unique identifier for each participant.
• status - Latest survey status (e.g., Complete, Terminated), which can still be In Progress.
• question - Question text used in the exported reporting data; it may contain a configured label, such as Age. Use the Data Map's separate question field for original wording and reporting_label for the readable label.
• reporting_id - The stable source-question identifier, such as how-old-are-you. It is not the readable reporting label Age; look up reporting_label in the Data Map.
• line_number - The line number of the question in the survey script.
• type - Type of question (NumericQuestion, MultiChoiceQuestion, OpenEnd, etc.).
• response - The recoded, human-readable response category (e.g., 35-44).
• raw_response - The raw value stored (e.g., 36).
• responded - Indicates whether and in what order the respondent selected the option. 0 = not selected, 1 = selected first, 2 = selected second, and so on.
• timestamp - Time when the answer was submitted.
• weight - Weighting factor applied to this respondent's answers for statistical adjustment.

4. Data Representation

Single-choice questions

Stored as one row with responded=1.

Multi-choice questions

Stored as multiple rows per respondent per option. The chosen options have responded>0, with the number indicating the order in which the options were chosen. Unchosen options have responded=0.

Example: Multi-choice question

Question: Which of the following fruits do you like? (Select all that apply)

respondent_id	question	response	responded
r1	Fruits	Apple	1
r1	Fruits	Banana	0
r1	Fruits	Orange	2

Here, the respondent chose Apple first, Orange second, and did not select Banana.

Numeric questions

Both response (bucketed/cleaned category, e.g. 35-44) and raw_response (e.g. 36) are provided.

Open-end questions

The full text appears in response and raw_response.

Respondent quality metadata

Available quality values are metadata rows, identified by reporting_id: quality_suspect_score, quality_duplicate_detected and quality_termination_reason. They are not additional questions or answer options. Raw CSV uses the same representation.

raw_response retains the recorded value or stable termination-reason code, while response provides the corresponding display representation. Missing evidence does not mean zero suspect risk or a negative duplicate finding. The data can retain more than one metadata update per respondent; use timestamps when selecting the latest value, and do not count these rows as additional respondents. Wide exports resolve each field to its latest recorded value.

Use Data Map to identify these rows and any distinct survey-question identifier created to avoid a name collision. See Respondent quality fields for their definitions and limitations.

5. Missing & Special Values

• Non-responses may appear with responded=0 and empty raw_response.
• "Prefer not to say" or similar options appear as normal response categories.
• Terminated respondents may have partial rows depending on where they dropped out.

Data Map worksheet

The workbook includes a Data Map worksheet alongside the respondent data. Use it to identify each question and match exported responses to their human-readable labels.

The Data Map keeps reporting_id, reporting_label and original question text separate. Open-ended questions are identified by reporting ID, question text, and question type, but their verbatim values are not duplicated in the map. Use the respondent data worksheet when you need the actual text.

The raw CSV download provides the same long-form data map as data_map.csv beside respondent_data.csv. See Downloading data and reports for the archive layout.

Compatibility: wide Excel and SPSS identifiers replace previous V###_descriptive_label headers. Update scripts, saved SPSS syntax, joins and BI imports that match the previous headers or readable labels. Use a fresh data map or codebook to build the old-to-new mapping, verify expanded options, and retain that mapping with the export. Do not assume an old positional variable number is a reporting ID.

6. Weighting

• Apply the weight column in analysis to ensure results reflect population targets.

7. Best Practices

• Use pivot tables (Excel) or groupby (Python/Pandas) to aggregate responses.
• For multi-choice questions, include all rows where responded>0 to capture all selected options. Use the order number if you need to analyze sequence of selection.
• When comparing formats, join reporting_id to the wide Data Map or SPSS codebook, then use the mapped exported column and option/topic. For example, how-old-are-you maps to how_old_are_you. Expanded IDs map to multiple columns.

8. When to Use Long Format

• For deep exploratory analysis.
• When handling multi-select or grid questions where wide format becomes cumbersome.
• When exporting data into R/Python for custom cleaning, text analysis, or advanced visualization.