Data-Cleaning Tool

file       01_survey_export_de.csv · 107,237 bytes · sha256 6ca4696f292c4a49
encoding   utf-8-sig (bom)
delimiter  ';' (consistency 1.00)
header     row 1
records    1,026 · empty 8 · short 0 · malformed 0 · duplicate 8
locale     decimal ',', thousands '.'

  #  header                     normalised                 non-empty distinct  guess
  1  Respondent ID              respondent_id                   1017      999  string
  2  Welle                      welle                           1018        4  integer(decimal=',', thousands='.') 100.0 %
  3  Startdatum                 startdatum                      1018      755  date(%Y-%m-%d, %d.%m.%Y, %d/%m/%Y) 99.0 %
     note: 95 values parse day-first and month-first (%d/%m/%Y and %m/%d/%Y); the guess reads them day-first
  4  Abgeschlossen am           abgeschlossen_am                1018     1006  datetime(%d.%m.%Y %H:%M) 100.0 %
  5  Alter                      alter                           1018       73  integer(decimal=',', thousands='.') 100.0 %
     null-like 'k.A.' x3
  6  Geschlecht                 geschlecht                      1018        8  string
  7  Land                       land                            1018        6  string
  8  Haushaltseinkommen (€)     haushaltseinkommen_eur           998      374  integer(decimal=',', thousands='.') 100.0 %
     note: 189 values read differently in English and German style (e.g. "38.500" is 38.5 or 38500)
  9  Ausgaben letzter Monat (€) ausgaben_letzter_monat_eur      1018     1000  number(decimal=',', thousands='.') 100.0 %
 10  Zufriedenheit              zufriedenheit                   1003       10  integer(decimal=',', thousands='.') 100.0 %
 11  NPS                        nps                             1018       11  integer(decimal=',', thousands='.') 100.0 %
 12  Kommentar                  kommentar                        588       15  string
 13  Kommentar                  kommentar_2                      253        6  string
 14                             column_14                          0        0  empty

repeated headers: Kommentar
empty columns: column_14

# Profile skeleton from clean-data init 0.1.0 for 01_survey_export_de.csv.
# Review every column. Validation rules (required, min, max, regex, allowed) and missing-value
# strategies are not guessed: add them where the data needs them. Reference: docs/profile-reference.md
version: 1
name: "01_survey_export_de"
description: "Skeleton generated from 01_survey_export_de.csv"
input:
  encoding: "utf-8-sig"  # bom
  delimiter: ";"
  header_row: 1
  null_tokens: ["", "k.A."]
  locale:
    decimal: ","
    thousands: "."
    date_formats: ["%Y-%m-%d", "%d.%m.%Y", "%d/%m/%Y"]
    datetime_formats: ["%d.%m.%Y %H:%M"]
headers:
  normalise: snake_case
  # Normalised input names: respondent_id, welle, startdatum, abgeschlossen_am, alter, geschlecht, land, haushaltseinkommen_eur, ausgaben_letzter_monat_eur, zufriedenheit, nps, kommentar, kommentar_2, column_14
  # rename maps a normalised name to the output name, e.g. {old_name: new_name}
  rename: {}
  drop: ["column_14"]  # empty in the analysed records
columns:
  respondent_id:
    type: string
    # 1017 non-empty, 999 distinct
    # e.g. "NG-279327", "NG-959966", "NG-926477"
  welle:
    type: integer
    # 1018 non-empty, 4 distinct

read 1026 · empty 8 · rejected 18 · duplicates 18 · out 982 · exit 0

INFO: [plan]     in-memory run · profile survey-de
INFO: [read]     encoding utf-8-sig (given) · delimiter ';' · header row 1 · footer rows 0
…
INFO: [dedupe]   dedupe.exact · dedupe.key.1 [respondent_id] keep last by completed_at
INFO: [read]     1026 records · 0 malformed · 0 short · 0 footer rows skipped
…
INFO: [types]    household_income number(decimal=",", thousands=".")  parsed 998 · null 20 · invalid 0
INFO:            r2 "45.000,00" -> 45000.0 · r3 "38.500" -> 38500.0 · r4 "47.500" -> 47500.0 · r5 "40.500,00" -> 40500.0 · r6 "28.500,00" -> 28500.0
…
INFO: [validate] age.type (integer)  failed 0
INFO: [validate] age.min (>= 16)  failed 2 · r48 "15" · r76 "12"
INFO: [validate] age.max (<= 99)  failed 3 · r118 "130" · r544 "150" · r871 "101"
…
INFO: [dedupe]   dedupe.exact  removed 12 · r85 duplicate of r80 · r304 duplicate of r124 · r324 duplicate of r181 · r389 duplicate of r41 · r407 duplicate of r189
INFO: [dedupe]   dedupe.key.1 [respondent_id] keep last by completed_at  removed 6 · r40 superseded by r902 · r190 superseded by r288 · r220 superseded by r687 · r422 superseded by r610 · r528 superseded by r783
INFO: [missing]  household_income median 68000.0  filled 20
INFO: [missing]  satisfaction median_low 6  filled 15
…

read 1026 · empty 8 · rejected 18 · duplicates 18 · out 982 · exit 0

INFO: read: 1026 records from 01_survey_export_de.csv
INFO: structure: 8 empty records dropped, 13 output columns
INFO: normalise: 2723 cells changed
INFO: types: 10 invalid cells, 0 set to null
INFO: validate: 18 rows rejected
INFO: dedupe: 18 rows removed (12 exact, 6 by key, 0 fuzzy)
INFO: missing: 35 cells filled, 0 rows dropped
INFO: write: 982 rows written to cleaned.csv