Reshape a wide survey

When you need it

Your survey tool exports one row per respondent and one column per question. Open-ended answers sit next to scores, and many tools add a second header row with the question wording. For most files the in-browser converter does this, and it can take the question wording from the row under the column names. Use this recipe when you need more control: incomplete responses to drop, Likert labels to turn into numbers, odd date formats.

Input

Example input, survey-export.csv. Row 2 holds the question wording. R_102’s first answer spans two lines and its second is blank; R_103 didn’t finish. R_101’s second answer contains a non-breaking space, as text pasted from a phone often does.

survey-export.csv

ResponseId,StartDate,Finished,Q1_nps,Q2_easy,Q3_why,Q4_improve
Response ID,Start Date,Finished,"How likely are you to recommend Lumen Coffee to a friend? (0-10)",The app makes ordering easy,Why did you give that score?,What one thing should we improve?
R_101,3/1/26 8:15,True,9,5 - Strongly agree,The baristas know my order.,Faster pickup at rush hour
R_102,3/1/26 9:40,True,3,2 - Disagree,"Two cold drinks in a row.
Not great.",
R_103,3/2/26 18:05,False,7,,,

Output

survey-rows.csv

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,question,Q1_nps,Q2_easy
R_101,R_101,false,Why did you give that score?,1772370900000,R_101,Q3_why,9,5
R_101,R_101,true,The baristas know my order.,1772370901000,R_101,Q3_why,9,5
R_101,R_101,false,What one thing should we improve?,1772370902000,R_101,Q4_improve,9,5
R_101,R_101,true,Faster pickup at rush hour,1772370903000,R_101,Q4_improve,9,5
R_102,R_102,false,Why did you give that score?,1772376000000,R_102,Q3_why,3,2
R_102,R_102,true,Two cold drinks in a row. Not great.,1772376001000,R_102,Q3_why,3,2

Rules

• Each respondent is one session. The response id is the sessionId, userId and conversationId.
• Questions go in as outgoing rows. Each open-ended question is incoming: false, then its answer is incoming: true one second later. The model reads only text, so a short answer like “Faster pickup” needs its question next to it. See Reviews, surveys and tickets.
• Take the question wording from the export. The second header row is the text respondents saw, so the script uses it rather than a hand-typed copy. If your export has no wording row, type each question exactly as respondents saw it.
• Scores stay as extra columns. NPS and ratings are numbers, not text to enrich. 5 - Strongly agree becomes 5, so the agent can average it.
• Skip blank answers; don’t fill them. No [No Response] rows: placeholder text reads like a real answer to the model (conversation format).
• Drop incomplete responses if your survey tool marks them (Finished is False here), unless partial answers matter to you.
• Clean the text. Replace non-breaking spaces and line breaks with plain spaces.
• State the time zone. The export’s 3/1/26 8:15 is Eastern time. The Node script uses a fixed offset (-05:00), which is right only while every response falls in standard time; for dates across a daylight saving change, use a named zone as in Fix timestamps, or the Python version below.

Script

Node 18 or later, with npm install papaparse.

survey-to-rows.mjs

// Reshape a wide survey export (one row per respondent) into one row per question and answer.
// Usage: node survey-to-rows.mjs survey-export.csv survey-rows.csv
import fs from 'node:fs';
import Papa from 'papaparse';

const [input, output] = process.argv.slice(2);
const OPEN_ENDED = ['Q3_why', 'Q4_improve']; // these become messages
const SCORES = ['Q1_nps', 'Q2_easy'];       // these stay as extra columns
const TIME_ZONE_OFFSET = '-05:00';          // the survey tool exported Eastern Standard Time

const [header, questionText, ...answers] = Papa.parse(fs.readFileSync(input, 'utf8'), { skipEmptyLines: 'greedy' }).data;
const col = (name) => header.indexOf(name);
const clean = (s) => (s ?? '').replace(/\u00a0/g, ' ').replace(/\s+/g, ' ').trim();
const score = (s) => { const m = clean(s).match(/^(\d+)/); return m ? m[1] : ''; }; // "5 - Strongly agree" -> 5

// "3/1/26 8:15" -> Unix ms
function toMillis(s) {
  const [, mo, d, y, h, mi] = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{2,4}) (\d{1,2}):(\d{2})$/);
  const year = y.length === 2 ? `20${y}` : y;
  return Date.parse(`${year}-${mo.padStart(2, '0')}-${d.padStart(2, '0')}T${h.padStart(2, '0')}:${mi}:00${TIME_ZONE_OFFSET}`);
}

const rows = [];
let blank = 0;
for (const r of answers) {
  const id = r[col('ResponseId')];
  if (r[col('Finished')] !== 'True') continue; // drop incomplete responses
  const extra = Object.fromEntries(SCORES.map((c) => [c, score(r[col(c)])]));
  let t = toMillis(r[col('StartDate')]);
  for (const c of OPEN_ENDED) {
    const answer = clean(r[col(c)]);
    if (!answer) { blank++; continue; } // no placeholder rows
    const base = { sessionId: id, userId: id, conversationId: id, question: c, ...extra };
    rows.push({ ...base, incoming: 'false', text: clean(questionText[col(c)]), dimensionlabs_timestamp: t });
    rows.push({ ...base, incoming: 'true', text: answer, dimensionlabs_timestamp: t + 1000 });
    t += 2000;
  }
}

const fields = ['sessionId', 'userId', 'incoming', 'text', 'dimensionlabs_timestamp', 'conversationId', 'question', ...SCORES];
fs.writeFileSync(output, Papa.unparse({ fields, data: rows.map((r) => fields.map((f) => r[f])) }, { newline: '\n' }) + '\n');
console.log(`${answers.length} responses -> ${rows.length} rows (${blank} blank ${blank === 1 ? 'answer' : 'answers'} skipped)`);

node survey-to-rows.mjs survey-export.csv survey-rows.csv
# 3 responses -> 6 rows (1 blank answer skipped)

In Python, with pandas

melt is the pandas name for wide-to-long. This version takes a named time zone, so daylight saving time is handled. It writes the same file.

survey_to_rows.py

# Reshape a wide survey export into one row per question and answer, with pandas.
# Usage: python3 survey_to_rows.py survey-export.csv survey-rows.csv
import sys
import pandas as pd

OPEN_ENDED = ["Q3_why", "Q4_improve"]
SCORES = ["Q1_nps", "Q2_easy"]
TIME_ZONE = "America/New_York"

src, dst = sys.argv[1], sys.argv[2]
raw = pd.read_csv(src, dtype=str, keep_default_na=False)
question_text = raw.iloc[0]                 # the second header row holds the question wording
df = raw.iloc[1:]
df = df[df["Finished"] == "True"].copy()     # drop incomplete responses

clean = lambda s: " ".join(str(s).replace("\u00a0", " ").split())
for c in SCORES:
    df[c] = df[c].str.extract(r"^(\d+)", expand=False).fillna("")   # "5 - Strongly agree" -> 5
start = pd.to_datetime(df["StartDate"], format="%m/%d/%y %H:%M").dt.tz_localize(TIME_ZONE)
df["start_ms"] = (start - pd.Timestamp("1970-01-01", tz="UTC")) // pd.Timedelta(milliseconds=1)

long = df.melt(id_vars=["ResponseId", "start_ms", *SCORES], value_vars=OPEN_ENDED, var_name="question", value_name="answer")
long["answer"] = long["answer"].map(clean)
long = long[long["answer"] != ""]            # no placeholder rows for blank answers
long["order"] = long["question"].map(OPEN_ENDED.index)
long = long.sort_values(["ResponseId", "order"])
long["n"] = long.groupby("ResponseId").cumcount()

rows = []
for r in long.itertuples():
    base = {"sessionId": r.ResponseId, "userId": r.ResponseId, "conversationId": r.ResponseId,
            "question": r.question, **{c: getattr(r, c) for c in SCORES}}
    t = r.start_ms + r.n * 2000
    rows.append({**base, "incoming": "false", "text": clean(question_text[r.question]), "dimensionlabs_timestamp": t})
    rows.append({**base, "incoming": "true", "text": r.answer, "dimensionlabs_timestamp": t + 1000})

cols = ["sessionId", "userId", "incoming", "text", "dimensionlabs_timestamp", "conversationId", "question", *SCORES]
pd.DataFrame(rows)[cols].to_csv(dst, index=False, lineterminator="\n")
print(f"{len(df)} complete responses -> {len(rows)} rows")

pip install pandas
python3 survey_to_rows.py survey-export.csv survey-rows.csv

With the toolkit

The toolkit’s survey shape does the reshaping from a mapping file. questionsFromRow reads the question wording from row 2, and skipRowsAfterHeader keeps that row from being read as a respondent. It keeps Likert labels as they are (5 - Strongly agree).

survey.config.json

{
  "input": { "skipRowsAfterHeader": 1 },
  "shape": "survey",
  "columns": { "sessionId": "ResponseId", "timestamp": "StartDate" },
  "timestamp": { "timeZone": "America/New_York" },
  "skip": [{ "column": "Finished", "values": ["False"] }],
  "survey": { "questionsFromRow": 2, "questions": ["Q3_why", "Q4_improve"] },
  "metadata": { "keep": ["Q1_nps", "Q2_easy"] }
}

node to-dimensionlabs.mjs --config survey.config.json --input survey-export.csv --out out/survey

Variations

• Qualtrics adds two rows under the column names: the question wording, then a row of {"ImportId":...} values. Skip both ("skipRowsAfterHeader": 2 in the toolkit, or drop the first two data rows in a script) and read the wording from row 2.
• A long export (one row per respondent per question): group rows by respondent first, then write the pairs in the order the questions were asked.
• Questions that depend on an earlier answer, such as “You gave us a 3. What could we do better?”: write the question as the respondent saw it, with the score filled in.
• Section headings (“How much do you agree with the following?”): leave them out unless the answers don’t make sense without them.