Data Cleaning Cheat Sheet

  



  1. R Data Transformation Cheat Sheet
  2. Sql Data Cleaning Cheat Sheet
  3. Python Data Cleaning Cheat Sheet Pdf

A regular expression is a sequence of characters that describes a search pattern. In Pandas, regular expressions are integrated with vectorized string methods, making finding and extracting patterns of characters easier. Learning how to use Regex make data cleaning less time-consuming for Data Scientists. This is a huge Data Science cheat sheet. Thanks for taking the time to help us. I consider this post one of the best for learning and have near!!đź‘Ť. My Data Cleaning in R Cheat Sheet - Yanqi Xu.

Last Updated 2018-10-09

The purpose of this review is to simply list common data analysis procedures that we do in quantitative methods research and outline the SPSS point-and-click procedures to accomplish these goals. This document will be updated thoughout time. The commands here are based on SPSS Version 24. I would not recommend starting with this document if you are just beginning with SPSS. Yes, often there are multiple ways to conduct the same analysis. I only present one here for each item.

Data Cleaning

Counting missing data

  1. Analyze > Descriptive Statistics > Frequencies
  2. Select the variable(s)
  3. Click “Continue” and the “OK”
Data

Missing data counts will be at the top of the resulting output.

Edit variable values

  1. Transform > Recode into Same Variables…
  2. Select the variable to transform and move it into the right column.
  3. Click “Old and New Values…”
  4. Under “Old Value”, enter either a specific value you would like to replace or a set of values you would like to replace.
  5. Under “New Value”, enter what the replacement value should be.
  6. Click “Add” under “New Value”.
  7. Click “Continue” and then “OK”.

Create a variable

  1. Transform > Compute Variable…
  2. Click “Type and Label…” to set the variable type, then click “Continue”.
  3. Enter the value for the variable. If it is a string, include the value in quotes.
  4. OR enter a formula for the variable based on the existing variables.
  5. Click “OK”.

Create dummy variables from categorical variables

  1. Transform > Create Dummy Variables
  2. Move the categorical variable into “Create Dummy Variables for.”
  3. Under “Root names (one per selected variable)”, type whatever you want to be the prefix for the dummy variables. (Suggestion: Use the name of the original variable, followed by an underscore.)
  4. Click “OK”.

Delete a variable

  1. Right-click on the column header
  2. Click “Clear”.

This does not produce a syntax in the Output window. The syntax for deleting a variable is here, in case you are saving your syntax:

Drop observations based on some condition (KEEP observations meeting the opposite)

  1. Data > Select Cases… > Select “If condition is satisfied” > If…
  2. Enter the condition based on which observations you would like to keep, then click “Continue”. (Remember that a condition checking if a string variable – one that uses letters instead of numbers – is equal to some value, put that value in quotes when writing the condition.)
  3. Select “Delete unselected cases”.
  4. Click “OK”.

You can specify multiple conditions at the same time by separating them with AND or OR.

Merging datasets

  1. Data > Merge Files > Add Variables…
  2. Note that the datasets you are merging must already be saved as SPSS (.sav) format files. In addition, the variables you are matching on must have the same name across datasets.
  3. Select “An external SPSS statistics data file”, browse for your file, and select it.
  4. Select “Match cases on key variables”, click on the matching variable, and add it to “Key Variables”.
  5. Click “OK”.

R Data Transformation Cheat Sheet

Appending datasets

  1. Data > Merge Files > Add Cases…
  2. Note that the datasets you are merging must already be saved as SPSS (.sav) format files. In addition, the variables you are matching on must have the same name across datasets.
  3. Select “An external SPSS statistics data file”, browse for your file, and select it.
  4. All variables already in both datasets will appear in “Variables in New Active Dataset”, and variables not in both datasets will be in “Unpaired Variables”. Move all unpaired variables you want into the right column.
  5. Click “OK”.

Reshaping datasets

From long to wide format:

  1. Data > Restructure…
  2. Select “Restructure selected cases into variables”.
  3. Move the all variables that are not to be reshaped (are consistent across rows for a unit) into “Identifier variable(s)”, then click “Next”.
  4. Select “Yes – data will be sorted by the Identifier and Index variables”, then click “Next”.
  5. Select “Group by original variable”, then click “Next”.
  6. Select “Restructure the data now”, then click “Next”.

From wide to long format (if you have only one variable that needs to be changed):

  1. Data > Restructure…
  2. Select “Restructure selected variables into cases”.
  3. Move the all variables that are not to be reshaped (are consistent across rows for a unit) into “Identifier variable(s)”, then click “Next”.
  4. Select “One”, then click “Next”.
  5. Move the identification variable (e.g., student ID) into the slot in “Case Group Identification”.
  6. Move the wide variables to be transposed into the slot in “Variables to be Transposed”.
  7. Move all other variables that should be the same for all rows of a case into the slot in “Fixed Variable(s)”.
  8. Select “One”, then click “Next”.
  9. Choose how you want the different rows for each case identified, either by sequential numbers or by the wide variable names, then click “Next”.
  10. Specify what to do with variables that you didn’t include and what to do with missing data in the wide variables, then click “Next”.
  11. Select “Restructure the data now”, then click “Next”.

Descriptive Statistics

Central tendency: mean, median, and mode (for continuous variable)

  1. Analyze > Descriptive Statistics > Frequencies
  2. Select the continuous variable(s)
  3. Uncheck “Display frequency tables”
  4. Click “Statistics…” and check the desired central tendency measures
  5. Click “Continue” and then “OK”

Central tendency: mode and frequency table (for categorical variable)

  1. Analyze > Descriptive Statistics > Frequencies
  2. Select the categorical variable(s)
  3. Check “Display frequency tables”
  4. Click “Format” and select “Descending counts”
  5. Click “Continue” and then “OK”

The top item in the frequency table is the mode. Note that if multiple categorical variables are selected, a separate frequency table will be created for each variable.

Variability: Standard deviation, variance, and range (for continuous variable)

  1. Analyze > Descriptive Statistics > Descriptives
  2. Select the continuous variable(s)
  3. Click “Options” and select the desired measures of spread
  4. Click “Continue” and then “OK”

Crosstabulation

Sql Data Cleaning Cheat Sheet

  1. Analyze > Descriptive Statistics > Crosstabs…
  2. Put one of the categorical variables in the Row(s) box
  3. Put the other categorical variable in the Column(s) box
  4. Use the “Cells” menu to indicate if you want row or column percentages
  5. Click “OK”

Conditional Means

  1. Analyze > Compare Means > Means…
  2. Put the continuous variable in the Dependent List box
  3. Put the categorical variable in the Layer 1 of 1, Independent List box
  4. Click “OK”

Correlation

  1. Analyze > Correlate > Bivariate
  2. Select all variables that you wish to correlate
  3. Click “OK”

Bivariate Hypothesis Testing

One-Sample T Test

  1. Analyze > Compare Means > One-Sample T Test…
  2. Select the variable
  3. Use the “Options” menu to set the confidence interval level
  4. Set the population mean in “Test Value”
  5. Click “OK”

Two-Sample Independent T Test

  1. Data must be organized such that the continuous variable is one variable and the categorical grouping variable is the other variable.
  2. Analyze > Compare Means > Independent-Samples T Test…
  3. Select the continuous variable and move it to “Test Variable(s)” selection
  4. Select the categorical outcome and move it to the “Grouping Variable” selection
  5. Click “Define Groups…”
  6. Enter the two values for the two groups that will be compared (e.g., 1 and 0, or “Male” and “Female”)
  7. Click “Continue” and then “OK”

Two-Sample Dependent T Test

  1. Data must be organized such that the continuous variable is in two separate variables, one for each time period/half of the paired sample.
  2. Analyze > Compare Means > Paired-Samples T Test…
  3. Select the two continuous variables and move them over to the right side – they should be under “Pair 1”
  4. Click “OK”

Correlation

  1. Analyze > Correlate > Bivariate
  2. Select all variables that you wish to correlate
  3. Click “OK”

Chi-squared test of independence

  1. Analyze > Descriptive Statistics > Crosstabs…
  2. Move one categorical to the “Row(s)” box
  3. Move the other categorical variable to the “Column(s)” box
  4. Click “Statistics…”
  5. Check “Chi-square” and click “Continue”
  6. Click “OK”

One-way ANOVA

  1. Analyze > Compare Means > One-Way ANOVA…
  2. Move the continuous variable to the “Dependent List” box
  3. Move the categorical variable to the “Factor” box
  4. Click “Post Hoc…”
  5. Check “Tukey” and click “Continue”
  6. Click “OK”

Regression Methods

Ordinary least squares regression

  1. Analyze > Regression > Linear
  2. Move your dependent variable into the spot for “Dependent”
  3. Move your independent variable(s) into the spot for “Block 1 of 1”
  4. Click the “Statistics” button, then select “Collinearity diagnostics,” then click “Continue” if you want VIF statistics.
  5. Click “OK”

Binary logistic regression

  1. Analyze > Regression > Binary Logistic
  2. Move your dependent variable into the spot for “Dependent”
  3. Move your independent variable(s) into the spot for “Block 1 of 1”
  4. Click “Save”, select “Probabilities”, then click “Continue” (not important for the modeling itself, but the predicted probabilities are useful for other steps later)
  5. Click “OK”

Getting the ROC curve for a logistic model

  1. Run the logistic regression model as described above
  2. Analyze > ROC Curve…
  3. Move your predicted probabilities variable to “Test Variable”
  4. Move your binary outcome variable to “State Variable”
  5. Assuming your binary outcome is a 0/1 variable, type “1” in “Value of State Variable”
  6. Make sure “ROC Curve”, “With diagonal reference line”, and “Standard error and confidence interval” are checked
  7. Click “OK”

Ordinal logistic regression

  1. Analyze > Regression > Ordinal…
  2. Move your dependent variable into the spot for “Dependent”
  3. Move your independent variable(s) into the spot for “Covariate(s)” (It is suggested that you convert all of your categorical independent variables into dummy variables and include the dummy variables instead of the original categorical variables.)
  4. Click “Output”, select “Test of parallel lines”, then click “Continue”
  5. Click “OK”

Multinomial logistic regression

  1. Analyze > Regression > Multinomial Logistic
  2. Move your dependent variable into the spot for “Dependent”
  3. You can set the reference category using the “Reference Category…” menu.
  4. Move your independent variable(s) into the spot for “Covariate(s)” (It is suggested that you convert all of your categorical independent variables into dummy variables and include the dummy variables instead of the original categorical variables.)
  5. Click “OK”

(As of September 2016, SPSS does not support a test for independence of irrelevant alternatives.)

Miscellaneous Analysis Tools

Open a non-SPSS format file in SPSS

To open a non-SPSS format file in SPSS, you must open SPSS first. Once SPSS is open…

  1. In the “Recent Files” pane, click “Open another file…”
  2. Navigate to the location of the file on your computer.
  3. In the “Files of type” section, change the option to “All Files (*.*).”
  4. Select your file and click “Open.”

Note: SPSS has a weird quirk where sometimes, on some computers, when you go through the above steps, the opened file will appear to be empty if you concurrently have said file opened in Microsoft Excel. To be safe, when trying to open data in SPSS that is non-SPSS format, close the data in Microsoft Excel first.

Specify the “working” dataset

Logic: When using point-and-click, SPSS will only be able to refer to one dataset as a time, even though it is possible to have multiple datasets open at a time.

You can specify which dataset you are working from using the following syntax:

You can figure out the name of the dataset by finding the syntax line the opened the dataset in your output window (starts with GET FILE) and look for where it says DATASET NAME.

Conduct analysis for subset of observations

Logic: Rather than attaching the condition to the specific command as is the case in other languages (“Do X if Y”), SPSS workflow requires you to “filter” your data, which temporarily allows you to run commands on a subset of the data. When you are done, you can restore the full set of data.

  1. Data > Select Cases… > Select “If condition is satisfied” > If…
  2. Enter the condition based on which observations you would like to keep, then click “Continue”.
  3. Select “Filter out unselected cases”.
  4. Click “OK”.

Python Data Cleaning Cheat Sheet Pdf

When you are done with doing an analysis on your filtered subset, you can restore the full set of data using the following syntax.