# Reshape a wide survey > Recipe: turn a survey export with one row per respondent into question and answer rows, with scores kept as columns. ## 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](/data/convert-a-file) 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. ```csv title="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 ```csv title="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](/data/reviews-surveys-tickets#surveys). - **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](/data/conversation-format#small-rules-that-keep-results-clean)). - **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](/transform/recipe-fix-timestamps), or the Python version below. ## Script Node 18 or later, with `npm install papaparse`. ```js title="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 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)`); ``` ```bash 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. ```python title="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 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") ``` ```bash pip install pandas python3 survey_to_rows.py survey-export.csv survey-rows.csv ``` ## With the toolkit The [toolkit](/transform/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`). ```json title="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"] } } ``` ```bash 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.