Exercise project: Clean data (challenging)

This exercise is intended to help you practice working with several datasets with different naming conventions, merging and cleaning them, and then processing them further—both in R and SPSS.

Task

You receive two CSV files:

  1. “uebung_dirty1.csv” with the following variables:
    • ID: Unique identifier of the person
    • geschl: Gender (coding: 1, 2, 1.0, and 9; 9 indicates unclear information)
    • alter: Age in years (contains erroneous values such as 0 or 999)
    • einkommen: Monthly net income in euros (error values: -99, 99999)
    • Q1, Q2, Q3, Q4: Questionnaire items (scale 1–5; Q3 should be reverse-coded)
  2. “uebung_dirty2.csv” with the following variables:
    • PersonID: Unique identifier of the person (corresponds to ID in Dataset 1)
    • sex: Gender (coding: “M”, “F”, “M.0”, and “unknown”)
    • age: Age in years (similar error values to those in Dataset 1)
    • income: Monthly net income in euros (with the same error values as in Dataset 1)
    • Item1, Item2, Item3, Item4: Questionnaire items (scale 1–5; Item3 should be reverse-coded)

Here are the specific data tables. As a first step, put them into a separate file—depending on how you want to structure the exercise, this can be a .csv/.txt file or, for example, directly into a .sav file.

Dataset 1 – uebung_dirty1.csv

IDgenderageincomeQ1Q2Q3Q4
1122200045NA3
291703214
321915004422
4299925005NA3NA
5219-992254
615540004444

Dataset 2 – uebung_dirty2.csv

PersonIDsexageincomeItem1Item2Item3Item4
1M22200045NA3
2unknown1703214
3F1915004422
6M.05540004444
7M5540004444
8F3030003332

Important notes:

  • The two datasets refer to the same people but use different variable names (e.g., ID vs. PersonID, geschl vs. sex, alter vs. age, einkommen vs. income, Q1–Q4 vs. Item1–Item4).
  • First, both datasets must be adjusted so that the variable names match (e.g., by renaming variables), and then merged using the person identifier.
  • The following additional tasks also apply:
    • Remove duplicates (e.g., when multiple cases with the same ID occur in a dataset).
    • Set incorrect values as missing (NA in R or SYSMIS in SPSS).
    • Reverse coding: Q3 (or Item3) should be recoded (for a scale from 1 to 5: 6 – original value).
    • Remove cases in which all four items (Q1, Q2, Q3, Q4 or Item1, Item2, Item3, Item4) have the maximum value (5).
    • Convert the gender variable into a factor (in R: factor; in SPSS: Value Labels). Different codings (1, 1.0, “M”, “M.0”, etc.) should be interpreted uniformly as “Male” or “Female”.
    • Create a new variable „age_group“ (e.g., <20 = "Youth", 20–64 = "Adult", ≥65 = "Senior").
    • Calculate the mean of the items (Q1–Q4 or Item1–Item4; after reverse coding Q3/Item3) in a new variable Q_gesamt – but only if at least two of the four items are available; otherwise, the value should be NA.
  1. Export the cleaned and merged dataset as a new CSV file (e.g., „uebung_clean.csv“) and additionally in a format-specific format (RDS in R, SAV in SPSS).
  2. Create a brief report documenting which steps you carried out (e.g., renaming variables, defining missing values, reverse coding, merging, etc.).

Sample solution in R

Solution in two Quick & Dirty videos: Part 1, Part 2

library(dplyr)

### 1. Load datasets
# Dataset 1
df1 <- read.csv2("uebung_dirty1.csv", header = TRUE, na.strings = c("NA", ""))
# Dataset 2
df2  ID, sex -> geschl, age -> alter, income -> einkommen,
# Item1 -> Q1, Item2 -> Q2, Item3 -> Q3, Item4 -> Q4.
df2 %
  rename(ID = PersonID,
         geschl = sex,
         alter = age,
         einkommen = income,
         Q1 = Item1,
         Q2 = Item2,
         Q3 = Item3,
         Q4 = Item4)

### 3. Merge data
# Assuming both datasets contain the same people. We merge them using a full join based on ID.
df <- full_join(df1, df2, by = "ID", suffix = c("_1", "_2"))

# We now need to consolidate the data for each variable.
# Example for "geschl": We may have two columns: geschl_1 and geschl_2.
# We first use geschl_1 if available; otherwise, we use geschl_2.
df %
  mutate(geschl = ifelse(!is.na(geschl_1), as.character(geschl_1), as.character(geschl_2)),
         alter = ifelse(!is.na(alter_1), alter_1, alter_2),
         einkommen = ifelse(!is.na(einkommen_1), einkommen_1, einkommen_2),
         Q1 = ifelse(!is.na(Q1_1), Q1_1, Q1_2),
         Q2 = ifelse(!is.na(Q2_1), Q2_1, Q2_2),
         Q3 = ifelse(!is.na(Q3_1), Q3_1, Q3_2),
         Q4 = ifelse(!is.na(Q4_1), Q4_1, Q4_2)
  )

# Remove the auxiliary columns:
df % select(ID, geschl, alter, einkommen, Q1, Q2, Q3, Q4)

### 4. Remove duplicates (based on ID)
df  "Male"; "2", "F", "F.0", "W", "female", etc. => "Female"
df$geschl <- toupper(df$geschl)  # convert to uppercase to facilitate comparisons
df$geschl[df$geschl %in% c("9", "UNKNOWN")] <- NA
df$geschl[df$geschl %in% c("1", "1.0", "M", "M.0")] <- "Male"
df$geschl[df$geschl %in% c("2", "F", "F.0", "W")] <- "Female"
df$geschl <- factor(df$geschl, levels = c("Male", "Female"))

# Age: set values 100 to NA
df$alter[df$alter  100] 50000 to NA
df$einkommen[df$einkommen == -99 | df$einkommen > 50000] <- NA

### 6. Reverse coding of Q3
# On a scale from 1 to 5: New value = 6 - original value
df$Q3 <- 6 - df$Q3

### 7. Remove cases where all items (Q1–Q4) have the maximum value of 5.
df % filter(!(Q1 == 5 & Q2 == 5 & Q3 == 5 & Q4 == 5))

### 8. Create new variables
# Age groups (e.g., <20 = "Youth", 20–64 = "Adult", ≥65 = "Senior")
df %
  mutate(alter_gruppe = case_when(
    !is.na(alter) & alter < 20 ~ "Youth",
    !is.na(alter) & alter = 65 ~ "Senior",
    TRUE ~ NA_character_
  ))

# Calculate the Q_gesamt scale mean (only if at least 2 items are available)
df %
  rowwise() %>%
  mutate(Q_gesamt = if_else(sum(!is.na(c_across(Q1:Q4))) >= 2,
                             mean(c_across(Q1:Q4), na.rm = TRUE),
                             NA_real_)) %>%
  ungroup()

### 9. Check the result
summary(df)
str(df)

### 10. Export the cleaned dataset
write.csv2(df, "uebung_clean.csv", row.names = FALSE)
saveRDS(df, file = "uebung_clean.rds")

Worked solution in SPSS

The SPSS syntax is carried out in several steps. You can use the SPSS dialog box (via the menus) for this and then adapt the generated syntax.

Step 1: Import both datasets

Dataset 1 (uebung_dirty1.csv):

GET DATA
  /TYPE=TXT
  /FILE="C:pfaduebung_dirty1.csv"
  /DELCASE=LINE
  /DELIMITERS=";"
  /QUALIFIER='"'
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=2
  /VARIABLES=
    ID F4.0
    geschl F3.0
    alter F4.0
    einkommen F8.0
    Q1 F2.0
    Q2 F2.0
    Q3 F2.0
    Q4 F2.0
  .
EXECUTE.
SAVE OUTFILE="C:pfadtemp1.sav" /COMPRESSED.
EXECUTE.

Dataset 2 (uebung_dirty2.csv):

GET DATA
  /TYPE=TXT
  /FILE="C:pfaduebung_dirty2.csv"
  /DELCASE=LINE
  /DELIMITERS=";"
  /QUALIFIER='"'
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=2
  /VARIABLES=
    PersonID F4.0
    sex A8
    age F4.0
    income F8.0
    Item1 F2.0
    Item2 F2.0
    Item3 F2.0
    Item4 F2.0
  .
EXECUTE.
SAVE OUTFILE="C:pfadtemp2.sav" /COMPRESSED.
EXECUTE.

Step 2: Align variable names in SPSS

Open both files one after the other and rename the variables in Dataset 2. You can do this via the menu item “Transform -> Rename Variables…” or using syntax:

* Open temp2.sav.
GET FILE="C:pfadtemp2.sav".
RENAME VARIABLES (PersonID = ID) (sex = geschl) (age = alter) (income = einkommen)
                     (Item1 = Q1) (Item2 = Q2) (Item3 = Q3) (Item4 = Q4).
EXECUTE.
SAVE OUTFILE="C:pfadtemp2_renamed.sav" /COMPRESSED.
EXECUTE.

Step 3: Merging the datasets

Now open the file in SPSS temp1.sav and merge the datasets (via “Data -> Merge Files -> Add Cases…”):

plaintextCopyGET FILE="C:pfadtemp1.sav".
MATCH FILES
  /FILE=*
  /FILE="C:pfadtemp2_renamed.sav"
  /BY ID.
EXECUTE.

Note: If there are duplicates, they must be removed later.

Step 4: Data cleaning

a) Remove duplicates (based on the ID):

SORT CASES BY ID (A).
IF (LAG(ID)=ID) dup_flag = 1.
EXECUTE.
SELECT IF (dup_flag  1 OR MISSING(dup_flag)).
EXECUTE.

b) Define invalid values as missing:

* Gender: Set values 9 or "unknown" as missing.
DO IF (geschl = 9 OR UPPER(STRING(geschl,F8.0)) = "UNKNOWN").
  COMPUTE geschl = SYSMIS(geschl).
END IF.
EXECUTE.

* Age: Set values  100 as missing.
DO IF (alter  100).
  COMPUTE alter = SYSMIS(alter).
END IF.
EXECUTE.

* Income: Set -99 or >50000 as missing.
DO IF (einkommen = -99 OR einkommen > 50000).
  COMPUTE einkommen = SYSMIS(einkommen).
END IF.
EXECUTE.

c) Recode gender as a factor:

Add in the variable view (or using syntax):

plaintextCopyVALUE LABELS geschl
  1 "Male"
  2 "Female".
EXECUTE.

Note: If geschl is stored as a numeric value, make sure that values such as 1.0 are also interpreted correctly.

Step 5: Reverse coding of Q3

COMPUTE Q3_rev = 6 - Q3.
EXECUTE.
COMPUTE Q3 = Q3_rev.
EXECUTE.

Step 6: Remove invalid cases (all items = 5)

DO IF (Q1 = 5 AND Q2 = 5 AND Q3 = 5 AND Q4 = 5).
  COMPUTE flag_max = 1.
ELSE.
  COMPUTE flag_max = 0.
END IF.
EXECUTE.
SELECT IF (flag_max = 0).
EXECUTE.

Step 7: Create a new variable “age_group”

RECODE alter (LO THRU 19 = 1) (20 THRU 64 = 2) (65 THRU HI = 3) INTO alter_gruppe.
EXECUTE.
VALUE LABELS alter_gruppe
  1 "Youth"
  2 "Adult"
  3 "Senior".
EXECUTE.

Step 8: Calculate the scale mean Q_gesamt

* Sum of the items.
COMPUTE sumQ = Q1 + Q2 + Q3 + Q4.
EXECUTE.
* Number of non-missing items.
COMPUTE countQ = (NOT MISSING(Q1)) + (NOT MISSING(Q2)) + (NOT MISSING(Q3)) + (NOT MISSING(Q4)).
EXECUTE.
* Calculate the mean only if countQ >= 2.
IF (countQ >= 2) Q_gesamt = sumQ / countQ.
EXECUTE.

Step 9: Export the cleaned dataset

As CSV:

plaintextCopySAVE TRANSLATE
  /OUTFILE="C:pfaduebung_clean.csv"
  /TYPE=CSV
  /MAP
  /FIELDNAMES
  /REPLACE.
EXECUTE.

Or as an SPSS file (SAV):

SAVE OUTFILE="C:pfaduebung_clean.sav"
  /COMPRESSED.
EXECUTE.

Alles klar?

Ich hoffe, der Beitrag war für dich soweit verständlich. Wenn du weitere Fragen hast, nutze bitte hier die Möglichkeit, eine Frage an mich zu stellen!

Stelle Dominik eine Frage