Generate Python Code from Excel Survey Results for Statistical Analysis

Survey export → list of dicts

Three respondents, two Likert questions and a group. Note ratings come out as strings.

Excel (Sheet1):
  group | q1_quality | q2_value | comment
  A     | 4          | 5        | Loved it
  B     | 2          | 3        | Too slow
  A     | 5          | 4        |            <- skipped

format: list_of_dicts → data.py

data = [
    {"group": "A", "q1_quality": "4", "q2_value": "5", "comment": "Loved it"},
    {"group": "B", "q1_quality": "2", "q2_value": "3", "comment": "Too slow"},
    {"group": "A", "q1_quality": "5", "q2_value": "4", "comment": None}
]

Into pandas, cast Likert, compute means

Load the list, coerce ratings to numbers, then compare group means.

import pandas as pd
from data import data   # rename if you like

df = pd.DataFrame(data)
for col in ["q1_quality", "q2_value"]:
    df[col] = pd.to_numeric(df[col], errors="coerce")

print(df.groupby("group")["q1_quality"].mean())
# A -> 4.5, B -> 2.0

Chi-square on a contingency table

Crosstab two categorical columns and run scipy's chi-square test.

import pandas as pd
from scipy.stats import chi2_contingency
from data import data

df = pd.DataFrame(data)
table = pd.crosstab(df["group"], df["q2_value"])
chi2, p, dof, expected = chi2_contingency(table)
print(f"chi2={chi2:.2f}, p={p:.3f}")

Skipped questions as missing data

Blank cells become None → NaN, so missing-response handling is built in.

import pandas as pd
from data import data

df = pd.DataFrame(data)
print(df["comment"].isna().sum())   # 1 skipped comment

# drop respondents who skipped q1, or impute
df["q1_quality"] = pd.to_numeric(df["q1_quality"], errors="coerce")
complete = df.dropna(subset=["q1_quality"])

Open-ended text for a word frequency pass

Text answers stay as strings, ready for a quick token count or any NLP step.

from collections import Counter
from data import data

words = Counter()
for r in data:
    c = r["comment"]
    if c:                      # skip None (skipped)
        words.update(c.lower().split())
print(words.most_common(5))

A 4 rating is emitted as "4". df["q1"].mean() on the raw column errors or gives nonsense because it's an object column. Cast first: pd.to_numeric(df["q1"], errors="coerce"). This is the headline gotcha for survey analysis with this tool.

Contrary to a common assumption, headers are NOT cut to 40 characters and there is NO original→sanitised mapping comment. A full-sentence question becomes one long key with underscores. Keep the original sheet handy if you need the readable question text for charts or reports.

Blank cells (unanswered questions) map to None, which pandas reads as NaN. Use dropna(subset=[...]) to drop incomplete respondents or fillna(...) to impute. A cell containing only a space is treated as a real (non-empty) answer — trim those first if respondents entered spaces.

Only the first 500 responses are inlined; list_of_dicts appends a # ... N more rows truncated comment. For a survey with thousands of responses, analyse via pandas.read_excel() on the file instead of inlining — the generator is best for small or sampled surveys.

Multi-paragraph open-ended answers contain newlines, which the generator does NOT escape — producing a broken multi-line string literal that won't import. Collapse line breaks in the text column before generating with a whitespace pass, or fix the offending literal afterwards.

Qualtrics inserts description/ImportId rows beneath the question text. The tool treats row 1 as the header and everything after as responses, so those metadata rows become bogus respondent dicts. Delete them in Excel before uploading.

SurveyMonkey grid questions export with a question row and a sub-answer row. Only the first row becomes keys, so the second header row lands as a phantom respondent. Flatten to a single header row before generating.

If two questions sanitise to the same identifier (e.g. punctuation-only differences), the later one overwrites the earlier inside each respondent dict — you silently lose a question. Rename the headers first with csv-header-rename.

Many exports keep responses on a 'Sheet1' and a codebook/legend on another tab. The generator reads the first sheet only — move the response grid to the front if it isn't already.

The generator is Pro-gated (Python Generator requires Pro tier.). Free users can't convert survey data with it. For large surveys you'd want pandas.read_excel() regardless of tier.