Cleaning US Census Data

You just got hired as a Data Analyst at the Census Bureau, which collects census data and finds interesting insights from it.

The person who previously had your job left you all the data they had for the most recent census. The data is spread across multiple csv files. They didn’t use R, and they would manually look through these csv files whenever they wanted to find something. Sometimes they would copy and paste certain numbers into Excel for analysis.

The thought of it makes you shiver. This is not scalable or repeatable.

Your boss wants you to dig into the data and find some insights by the end of the day. Can you get this data into R and into reasonable shape so that you can perform your analysis?

# load libraries
library(dplyr)
library(readr)
library(tidyr)

It will be easier to inspect the data stored in these files once you have it in a data frame. You can’t even call head() on these csvs! How are you supposed to read them?

Begin by creating a variable called files and set it equal to the list.files() of all of the csv files you want to import.

Read each file in files into a data frame using lapply() and save the result to df_list.

Concatenate all of the data frames in df_list into one data frame called us_census.

# load CSVs
files <- list.files(pattern = "states_.*csv")
df_list <- lapply(files, read_csv)
us_census <- bind_rows(df_list)

Inspect the us_census data frame by printing the column names, looking at the data types with str(), and viewing the head().

What columns have symbols that will prevent calculations? What are the data types of the columns? Do any columns contain multiple kinds of information?

# inspect data
str(us_census)

Classes ‘spec_tbl_df’, ‘tbl_df’, ‘tbl’ and 'data.frame':    61 obs. of  11 variables:
 $ X1       : num  0 1 2 3 4 5 0 1 2 3 ...
 $ State    : chr  "Alabama" "Alaska" "Arizona" "Arkansas" ...
 $ TotalPop : num  4830620 733375 6641928 2958208 38421464 ...
 $ Hispanic : chr  "3.7516156462584975%" "5.909580838323351%" "29.565921052631502%" "6.215474452554738%" ...
 $ White    : chr  "61.878656462585%" "60.910179640718574%" "57.120000000000026%" "71.13781021897813%" ...
 $ Black    : chr  "31.25297619047618%" "2.8485029940119775%" "3.8509868421052658%" "18.968759124087573%" ...
 $ Native   : chr  "0.4532312925170065%" "16.39101796407186%" "4.35506578947368%" "0.5229197080291965%" ...
 $ Asian    : chr  "1.0502551020408146%" "5.450299401197604%" "2.876578947368419%" "1.1423357664233578%" ...
 $ Pacific  : chr  "0.03435374149659865%" "1.0586826347305378%" "0.16763157894736833%" "0.14686131386861315%" ...
 $ Income   : chr  "$43296.35860306644" "$70354.74390243902" "$54207.82095490716" "$41935.63396778917" ...
 $ GenderPop: chr  "2341093M_2489527F" "384160M_349215F" "3299088M_3342840F" "1451913M_1506295F" ...

head(us_census)

colnames(us_census)

 [1] "X1"        "State"     "TotalPop"  "Hispanic"  "White"    
 [6] "Black"     "Native"    "Asian"     "Pacific"   "Income"   
[11] "GenderPop"

When inspecting us_census you notice a column X1 that stores meaningless information. Drop the X1 column from us_census, and save the resulting data frame to us_census. View the head of us_census.

# drop X1 column
us_census <- us_census%>%
  select(-X1)
head(us_census)

NA

You notice that there are 6 columns representing the population percentage for different races. The columns include the percent symbol %. Remove the percent symbol % from each of the race columns (Hispanic,White,Black,Native,Asian,Pacific). Save the resulting data frame to us_census, and view the head.

# remove % from race columns
us_census <- us_census%>%
  mutate(Hispanic=gsub('\\%','',Hispanic),
        White=gsub('\\%','',White),
        Black=gsub('\\%','',Black),
        Native=gsub('\\%','',Native),
        Asian=gsub('\\%','',Asian),
        Pacific=gsub('\\%','',Pacific))
head(us_census)

NA

The Income column also incudes a $ symbol along with the number representing median income for a state. Remove the $ from the Income column. Save the resulting data frame to us_census. View the head of us_census.

# remove $ from Income column
us_census <- us_census%>%
  mutate(Income=gsub('\\$','',Income))
head(us_census)

The GenderPop column appears to hold the male and female population counts. Separate this column at the _ character to create two new columns: male_pop and female_pop. Save the resulting data frame to us_census, and view the head.

# separate GenderPop column
us_census <- us_census%>%
  separate(GenderPop,c('male_pop', 'female_pop'), '_')
head(us_census)

You notice the new male_pop and female_pop columns contain extra characters M and F, respectively. Remove these extra characters from the columns. Save the resulting data frame to us_census, and view the head.

# clean male and female population columns
us_census <- us_census%>%
  mutate(male_pop=gsub('M','',male_pop),
        female_pop=gsub('F','',female_pop))
head(us_census)

Now that you have removed extra symbols from many of the columns that contain numerical data, you notice that the data type for these columns is still chr, or character. Convert all of these columns (Hispanic,White,Black,Native,Asian,Pacific,Income,male_pop,female_pop) to have a data type of numeric. Save the resulting data frame to us_census, and view the head.

# update column data types
us_census <- us_census%>%
  mutate(Hispanic = as.numeric(Hispanic),
        White = as.numeric(White),
        Black = as.numeric(Black),
        Native = as.numeric(Native),
        Asian = as.numeric(Asian),
        Pacific = as.numeric(Pacific),
        Income = as.numeric(Income),
        male_pop = as.numeric(male_pop),
        female_pop = as.numeric(female_pop))
head(us_census)

Take a second to look back at the Hispanic, White, Black, Native, Asian, and Pacific columns. The columns represent the population percentage for each race. Earlier you removed the % symbol, and then you just converted the column to numeric type. To make calculations easier, the column should now represent percentages in decimal form, where 50% is equivalent to 0.50. Update the values of these columns to be in decimal form, and save the resulting data frame to us_census. View the head of us_census.

# update values of race columns
us_census <- us_census%>%
  mutate(Hispanic = Hispanic/100,
         White = White/100,
         Black = Black/100,
         Native = Native/100,
         Asian = Asian/100,
         Pacific = Pacific/100)
head(us_census)

It’s always a good idea to check if there are duplicate rows of data in a data set. Pipe us_census into the duplicated() function to see which rows are duplicated. Then pipe the result into table() to get a count of the duplicated rows.

# check for duplicate rows
duplicates <- us_census%>%
  duplicated() %>%
  table()
duplicates

.
FALSE  TRUE 
   52     9 

Since there are duplicates, update the value of us_census to be the us_census data frame with only unique/distinct rows.

# remove duplicate rows
us_census <- us_census%>%
  distinct()

Confirm that there are no more duplicated rows in us_census. Pipe us_census into the duplicated() function to see which rows are duplicated. Then pipe the result into table() to get a count of the duplicated rows.

You should expect to see no TRUEs!

# cduplicates <- us_census%>%
duplicates <- us_census%>%
  duplicated() %>%
  table()
duplicates

.
FALSE 
   52 

View the head() of us_census. The data frame is all clean and ready for analysis! What do you want to find out?

# clean data frame
head(us_census)