Data Transformation and Dataset Preparation

How do I know if a survey scale has been reverse-coded?

Check the questionnaire, item by item, and record for each scale which end is positive and what numeric code it carries. Do not read direction from the variable name or from the value labels in the data. Within a battery, the detection signal is an item that correlates negatively with the others: that usually means it was negatively worded and not corrected. A silently reversed scale is one of the most damaging undetected errors in research, because the output is coherent and plausible and simply means the opposite of what it says.

What is the correct way to reverse-code a variable?

Create a new variable rather than overwriting the original, use an explicit formula that includes the scale length (for a 1 to 5 scale, new equals 6 minus old), and prove it by cross-tabulating original against reversed and by confirming the item now correlates positively with the rest of its battery. Including the scale length in the formula is what stops a five-point rule being applied to a seven-point item, which is a common error in trackers where the scale changed.

When is it acceptable to collapse survey categories?

When the analysis plan says the finer distinction is not analysed, when a subgroup is too small to report separately, when the client's other data uses those groupings, or when a previous wave defined them that way. Never because a chart has too many bars or because a boundary puts a figure on the convenient side of a round number. Record the mapping and the reason, retain the source variable, and check the result: a collapse that puts 80% of the sample into one category has destroyed the variable regardless of the justification.

How do I build a net variable correctly?

Count the respondents who fall into any of the member categories, and verify that count directly. Never sum the individual percentages, because respondents who fall into more than one category are counted twice and the net is overstated, sometimes by a great deal. Define the net by its member codes, state its base, and be careful with the word itself: "net" also means the difference between two ends of a scale, and a file where both senses appear unlabelled will produce a wrong chart.

How should I choose band boundaries for a continuous variable?

There are three approaches and each has a cost. Substantively meaningful boundaries (a retirement age, a price point, a contract term) are the most defensible and rarely the tidiest. External standard boundaries buy comparability and may fit the data badly. Distribution-driven boundaries such as quartiles fit the data and do not travel: quartile boundaries move as the sample moves, so a top quartile in one wave is not the same group as a top quartile in the next. State the approach, keep the continuous variable, and test whether the conclusion survives moving a boundary that sits near a concentration of cases.

What should be in a codebook?

For every variable: name, label, type, valid values and their labels, missing codes and their meanings, whether it was collected or derived, the construction rule if derived, the base description, scale direction and length where applicable, wave availability, and any comparability break marker. The test is that somebody who has never seen the study can open the codebook and interpret any variable in the file without asking a question. Without one, a dataset stays interpretable for about as long as the person who built it remembers the details.

What can go wrong when merging two datasets?

Three things, all quiet. A key that is not unique in one file multiplies rows in a merge the analyst believes is one-to-one. A format mismatch (leading zeros, trailing spaces, case, text versus numeric keys) causes matches to fail invisibly. And the records that fail to match usually differ systematically from those that do, so an unprofiled merge attrition introduces a bias that looks like a finding. Validate key uniqueness in both files first, reconcile row counts after, and compare the profile of matched and unmatched records.

Can I combine tracker waves into one file?

Only after checking, variable by variable, whether question wording, response list and order, scale length and direction, routing and base definition, mode and missing conventions changed between waves. Classify each variable as comparable, comparable with a caveat, or not comparable. Put break markers into the data itself rather than only into a document, because once the waves are stacked a break is invisible and a movement caused by a wording change is indistinguishable from a real one.

How do I verify a derived variable is correct?

By hand, on real cases, not in aggregate. Take at least ten cases spanning the range, including one at each extreme, one with a missing component and one that crosses a band boundary, recompute the value from the source variables, and compare. Distribution checks pass while individual values are wrong: an off-by-one in a reversal, a band boundary applied exclusively where it should be inclusive, or a missing rule that silently produces a zero are all invisible in a histogram and obvious in one case.

What is the difference between wide and long data, and when do I reshape?

Wide means one row per respondent, and it is right for cross-sectional analysis and reporting. Long means one row per respondent per item, wave or occasion, and it is right for repeated measures, multilevel models and some visualisation tools. Reshape only when the analysis needs it, and check the arithmetic: rows in the long file should equal respondents multiplied by occasions minus any legitimately absent combinations. Any discrepancy means a duplicate key or a lost record.