How to tidy a dataset

Keren Xu

2018/04/28

Column headers are values, not variable names

load libraries

library(foreign)
library(stringr)
library(plyr)
library(reshape2)

Load ———————————————————————–

pew <- read.spss("https://github.com/XUKEREN/KERENXU/raw/master/data/pew.sav",reencode='utf-8')
pew <- as.data.frame(pew)

Take a look at the dataset pew in R. This dataset has three variables: religion, income and frequency. We need to melt it in order to tidy it. Turn columns into rows.

head(pew)
##     weight   psraid int_date    lang type   cregion       state      usr
## 1 4.512821 10000001    50807 English  RDD Northeast Connecticut Suburban
## 2 2.102564 10000002    50807 English  RDD Northeast       Maine    Rural
## 3 1.282051 10000003    50807 English  RDD Northeast       Maine    Rural
## 4 1.355323 10000004    50807 English  RDD Northeast       Maine    Rural
## 5 1.589744 10000005    50807 English  RDD Northeast    New York    Urban
## 6 1.410256 10000007    61507 English  RDD Northeast    New York    Urban
##       usr1   form density3           q1      q1a           q2      q2a
## 1 Suburban Form A        4    Satisfied     Very    Satisfied     Very
## 2    Rural Form B        1    Satisfied     Very    Satisfied     Very
## 3    Rural Form A        2    Satisfied Somewhat    Satisfied     Very
## 4    Rural Form B        2 Dissatisfied     Very    Satisfied     Very
## 5    Urban Form A        5 Dissatisfied     Very    Satisfied Somewhat
## 6    Urban Form A        5 Dissatisfied     Very Dissatisfied     Very
##                  q3a                q3b                   q3c
## 1     Very satisfied     Very satisfied        Very satisfied
## 2     Very satisfied     Very satisfied Somewhat dissatisfied
## 3     Very satisfied     Very satisfied        Very satisfied
## 4 Somewhat satisfied     Very satisfied     Very dissatisfied
## 5 Somewhat satisfied Somewhat satisfied Somewhat dissatisfied
## 6 Somewhat satisfied               <NA>     Very dissatisfied
##                 q3d
## 1    Very satisfied
## 2    Very satisfied
## 3    Very satisfied
## 4 Very dissatisfied
## 5    Very satisfied
## 6    Very satisfied
##                                                            q5a
## 1 I worry the government is getting too involved in the issue 
## 2 The government should do more to protect morality in society
## 3 I worry the government is getting too involved in the issue 
## 4 I worry the government is getting too involved in the issue 
## 5 I worry the government is getting too involved in the issue 
## 6 I worry the government is getting too involved in the issue 
##                                                            q5b
## 1 Homosexuality is a way of life that should be accepted by so
## 2 Homosexuality is a way of life that should be discouraged by
## 3 Homosexuality is a way of life that should be accepted by so
## 4 Homosexuality is a way of life that should be accepted by so
## 5 Homosexuality is a way of life that should be discouraged by
## 6 Homosexuality is a way of life that should be accepted by so
##                                                            q5c
## 1 Most people who want to get ahead can make it if they're wil
## 2 Most people who want to get ahead can make it if they're wil
## 3 Most people who want to get ahead can make it if they're wil
## 4 Hard work and determination are no guarantee of success for 
## 5 Most people who want to get ahead can make it if they're wil
## 6 Hard work and determination are no guarantee of success for 
##                                                            q5d
## 1 The government today can't afford to do much more to help th
## 2 The government today can't afford to do much more to help th
## 3 The government should do more to help needy Americans, even 
## 4 The government should do more to help needy Americans, even 
## 5 The government today can't afford to do much more to help th
## 6 The government should do more to help needy Americans, even 
##                                                            q5e
## 1 Stricter environmental laws and regulations cost too many jo
## 2 Stricter environmental laws and regulations are worth the co
## 3 Stricter environmental laws and regulations cost too many jo
## 4 Stricter environmental laws and regulations are worth the co
## 5 Stricter environmental laws and regulations are worth the co
## 6 Stricter environmental laws and regulations are worth the co
##                                                            q5f
## 1 The best way to ensure peace is through military strength - 
## 2 The best way to ensure peace is through military strength - 
## 3 The best way to ensure peace is through military strength - 
## 4 Good diplomacy is the best way to ensure peace - Second stat
## 5 Good diplomacy is the best way to ensure peace - Second stat
## 6 Good diplomacy is the best way to ensure peace - Second stat
##                                                            q5g
## 1 It's best for the future of our country to be active in worl
## 2 It's best for the future of our country to be active in worl
## 3 It's best for the future of our country to be active in worl
## 4 We should pay less attention to problems overseas and concen
## 5 It's best for the future of our country to be active in worl
## 6 It's best for the future of our country to be active in worl
##                                   q6                    q7
## 1 Smaller government, fewer services Illegal in most cases
## 2 Smaller government, fewer services Illegal in most cases
## 3   Bigger government, more services   Legal in most cases
## 4   Bigger government, more services   Legal in most cases
## 5 Smaller government, fewer services   Legal in most cases
## 6   Bigger government, more services    Legal in all cases
##                  q8                                     q8a
## 1  Most of the time                your personal experience
## 2  Most of the time                your personal experience
## 3 Only now and then                          your education
## 4  Most of the time                      or something else?
## 5  Some of the time what you have seen or read in the media
## 6  Most of the time                your personal experience
##                     q9                q10a             q10b
## 1 Should express views Completely disagree     Mostly agree
## 2      Should keep out    Completely agree Completely agree
## 3      Should keep out     Mostly disagree     Mostly agree
## 4      Should keep out     Mostly disagree  Mostly disagree
## 5      Should keep out     Mostly disagree     Mostly agree
## 6      Should keep out        Mostly agree  Mostly disagree
##                  q10c                                  q10d
## 1 Completely disagree Practical experience and common sense
## 2        Mostly agree Practical experience and common sense
## 3        Mostly agree Practical experience and common sense
## 4     Mostly disagree Practical experience and common sense
## 5    Completely agree                Scientific information
## 6    Completely agree       Religious teachings and beliefs
##              marital hisp  race                     q16  chr
## 1            Married   No White             Protestant  <NA>
## 2           Divorced   No White             Protestant  <NA>
## 3           Divorced   No White             Protestant  <NA>
## 4 Never been married  Yes White  Nothing in particular  <NA>
## 5 Never been married   No White       Jewish (Judaism)  <NA>
## 6 Never been married   No White       Jewish (Judaism)  <NA>
##                                                             q17
## 1  Church of Christ, or Disciples of Christ (Christian Church) 
## 2                                                      Baptist 
## 3                    Congregational or United Church of Christ 
## 4                                                          <NA>
## 5                                                          <NA>
## 6                                                          <NA>
##                                 q17a q17b q17c q17d q17e q17f q17g q17h
## 1                               <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 2  American Baptist Churches in USA  <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 3                               <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 4                               <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 5                               <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 6                               <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
##   q17i q17j               q17k                      q17l q17m q17n q17o
## 1 <NA> <NA>  Church of Christ                       <NA> <NA> <NA> <NA>
## 2 <NA> <NA>               <NA>                      <NA> <NA> <NA> <NA>
## 3 <NA> <NA>               <NA>  United Church of Christ  <NA> <NA> <NA>
## 4 <NA> <NA>               <NA>                      <NA> <NA> <NA> <NA>
## 5 <NA> <NA>               <NA>                      <NA> <NA> <NA> <NA>
## 6 <NA> <NA>               <NA>                      <NA> <NA> <NA> <NA>
##   q17p q17q q17r                                               q17s q17t
## 1 <NA> <NA> <NA>                                               <NA> <NA>
## 2 <NA> <NA> <NA>                                               <NA> <NA>
## 3 <NA> <NA> <NA>                                               <NA> <NA>
## 4 <NA> <NA> <NA>                                               <NA> <NA>
## 5 <NA> <NA> <NA>                                            Reform  <NA>
## 6 <NA> <NA> <NA>  Jewish not further specified (just Jewish) (VOL)  <NA>
##   q17u q17v  q18                                        denom
## 1 <NA> <NA>  Yes                            Church of Christ 
## 2 <NA> <NA>  Yes        American Baptist Churches in the USA 
## 3 <NA> <NA>   No                     United Church of Christ 
## 4 <NA> <NA> <NA>                       Nothing in particular 
## 5 <NA> <NA> <NA>                               Reform Jewish 
## 6 <NA> <NA> <NA>  Jewish not further specified (just jewish) 
##                                          family
## 1  Restorationist in the Evangelical Tradition 
## 2            Baptist in the Mainline Tradition 
## 3  Congregationalist in the Mainline Tradition 
## 4                        Nothing in particular 
## 5                                      Jewish  
## 6                                      Jewish  
##                             reltrad             protfam q19a q19b
## 1  Evangelical Protestant Churches      Restorationist    No   No
## 2     Mainline Protestant Churches             Baptist    No   No
## 3     Mainline Protestant Churches   Congregationalist    No   No
## 4                     Unaffiliated      Not Protestant  <NA> <NA>
## 5                           Jewish      Not Protestant  <NA> <NA>
## 6                           Jewish      Not Protestant  <NA> <NA>
##                  q20                  q21                 q27  q28a
## 1        Once a week       Very important Between 100 and 500 Never
## 2             Seldom       Very important                <NA>  <NA>
## 3             Seldom    Not too important                <NA>  <NA>
## 4 A few times a year       Very important       Less than 100 Never
## 5             Seldom    Not too important                <NA>  <NA>
## 6              Never Not at all important                <NA>  <NA>
##                   q28b   q28c                  q28d q29 q30
## 1 Several times a year Seldom Once or twice a month Yes Yes
## 2                 <NA>   <NA>                  <NA>  No Yes
## 3                 <NA>   <NA>                  <NA>  No Yes
## 4                Never  Never                Seldom  No Yes
## 5                 <NA>   <NA>                  <NA>  No Yes
## 6                 <NA>   <NA>                  <NA>  No Yes
##                  q31                        q32  q33                q34
## 1 Absolutely certain            God is a person  Yes Absolutely certain
## 2     Fairly certain            God is a person  Yes     Fairly certain
## 3 Absolutely certain God is an impersonal force  Yes     Fairly certain
## 4 Absolutely certain God is an impersonal force  Yes Absolutely certain
## 5    Not too certain God is an impersonal force   No               <NA>
## 6    Not too certain                       <NA> <NA>               <NA>
##   q34a q34b q34c q34d q35 q36
## 1 <NA> <NA> <NA> <NA> Yes Yes
## 2 <NA> <NA> <NA> <NA> Yes Yes
## 3 <NA> <NA> <NA> <NA> Yes Yes
## 4 <NA> <NA> <NA> <NA> Yes  No
## 5 <NA> <NA> <NA> <NA>  No  No
## 6 <NA> <NA> <NA> <NA>  No  No
##                                                            q37
## 1                               [Holy book] is the word of God
## 2                               [Holy book] is the word of God
## 3                               [Holy book] is the word of God
## 4 [Holy book] is a book written by men and is not the word of 
## 5 [Holy book] is a book written by men and is not the word of 
## 6 [Holy book] is a book written by men and is not the word of 
##                                                            q38
## 1          [Holy book] is to be taken literally, word for word
## 2 Not everything in [Holy book] should be taken literally, wor
## 3 Not everything in [Holy book] should be taken literally, wor
## 4                                                         <NA>
## 5                                                         <NA>
## 6                                                         <NA>
##                  q39a                q39b                q39c
## 1        Mostly agree        Mostly agree Completely disagree
## 2    Completely agree        Mostly agree     Mostly disagree
## 3        Mostly agree        Mostly agree        Mostly agree
## 4    Completely agree     Mostly disagree        Mostly agree
## 5     Mostly disagree Completely disagree        Mostly agree
## 6 Completely disagree Completely disagree    Completely agree
##                                                         q40a
## 1 Many religions can lead to eternal life - Second statement
## 2 Many religions can lead to eternal life - Second statement
## 3 Many religions can lead to eternal life - Second statement
## 4                                                       <NA>
## 5 Many religions can lead to eternal life - Second statement
## 6                                                       <NA>
##                                                           q40b
## 1 There is MORE than one true way to interpret the teachings o
## 2 There is only ONE true way to interpret the teachings of my 
## 3 There is MORE than one true way to interpret the teachings o
## 4                                                         <NA>
## 5 There is MORE than one true way to interpret the teachings o
## 6                                   Neither/Both equally (VOL)
##                                                           q40c
## 1               preserve its traditional beliefs and practices
## 2               preserve its traditional beliefs and practices
## 3 adjust traditional beliefs and practices in light of new cir
## 4                                                         <NA>
## 5 adjust traditional beliefs and practices in light of new cir
## 6                                                         <NA>
##                  q41                  q42a                 q42b
## 1 A few times a week Once or twice a month               Seldom
## 2             Seldom                Seldom               Seldom
## 3             Seldom                 Never                Never
## 4 A few times a week                Seldom At least once a week
## 5             Seldom                 Never                Never
## 6              Never                 Never                Never
##                   q42c                 q42d  q42e q42f   q42g
## 1                Never Several times a year Never <NA>   <NA>
## 2               Seldom               Seldom Never <NA>   <NA>
## 3               Seldom                Never Never <NA>   <NA>
## 4 At least once a week                 <NA>  <NA> <NA> Seldom
## 5               Seldom                Never  <NA> <NA>   <NA>
## 6                Never                Never  <NA> <NA>   <NA>
##                    q43a                 q43b                 q43c q43d
## 1  At least once a week               Seldom Several times a year   No
## 2  At least once a week At least once a week                 <NA>   No
## 3                 Never               Seldom                 <NA>   No
## 4  At least once a week At least once a week At least once a week  Yes
## 5                 Never                Never                 <NA>   No
## 6 Once or twice a month Several times a year                 <NA>   No
##    q46                    q47                     q50 q50a      q50b
## 1 <NA>                   <NA>         Roman Catholic  <NA>      <NA>
## 2 <NA>                   <NA>             Protestant  <NA>  Baptist 
## 3 <NA>                   <NA>  Nothing in particular  <NA>      <NA>
## 4 <NA> Yes, there is conflict                    <NA> <NA>      <NA>
## 5 <NA>                   <NA>       Jewish (Judaism)  <NA>      <NA>
## 6 <NA>                   <NA>  Nothing in particular  <NA>      <NA>
##            q51  q52                                       q53 children
## 1  Protestant  <NA>  Nondenominational or Independent Church         0
## 2         <NA> <NA>                                      <NA>        0
## 3         <NA> <NA>                                      <NA>        0
## 4         <NA> <NA>                                      <NA>        0
## 5         <NA> <NA>                                      <NA>        0
## 6         <NA> <NA>                                      <NA>        0
##   q55a q55b q55c    sex age                     q60  q61
## 1 <NA> <NA> <NA> Female  43            Born in U.S. <NA>
## 2 <NA> <NA> <NA>   Male  61            Born in U.S. <NA>
## 3 <NA> <NA> <NA>   Male  58            Born in U.S. <NA>
## 4 <NA> <NA> <NA> Female  47            Born in U.S. <NA>
## 5 <NA> <NA> <NA>   Male  79            Born in U.S. <NA>
## 6 <NA> <NA> <NA> Female  74 Born in another country  Yes
##                             q63
## 1 Yes, father born outside U.S.
## 2 No, both parents born in U.S.
## 3 No, both parents born in U.S.
## 4 No, both parents born in U.S.
## 5 Yes, father born outside U.S.
## 6                          <NA>
##                                                           educ
## 1     Technical, trade, or vocational school AFTER high school
## 2           High school graduate (Grade 12 or GED certificate)
## 3        College graduate (B.S., B.A., or other 4-year degree)
## 4  Some college, no 4-year degree (including associate degree)
## 5 Post-graduate training or professional schooling after colle
## 6 Post-graduate training or professional schooling after colle
##                 income             regist           regicert       party
## 1 75 to under $100,000    Yes, registered Absolutely certain  Republican
## 2  20 to under $30,000 No, not registered               <NA>  Republican
## 3  30 to under $40,000 No, not registered               <NA> Independent
## 4    Less than $10,000 No, not registered               <NA> Independent
## 5  50 to under $75,000    Yes, registered Absolutely certain Independent
## 6  20 to under $30,000    Yes, registered Absolutely certain    Democrat
##    partyln         ideo                                  pvote04a
## 1     <NA>     Moderate                                     Voted
## 2     <NA> Conservative Did not vote (includes too young to vote)
## 3 Democrat Conservative Did not vote (includes too young to vote)
## 4 Democrat     Moderate Did not vote (includes too young to vote)
## 5 Democrat     Moderate                                     Voted
## 6     <NA> Very liberal                                     Voted
##          pvote04b
## 1            Bush
## 2            <NA>
## 3            <NA>
## 4            <NA>
## 5 Other candidate
## 6           Kerry
names(pew)
##   [1] "weight"   "psraid"   "int_date" "lang"     "type"     "cregion" 
##   [7] "state"    "usr"      "usr1"     "form"     "density3" "q1"      
##  [13] "q1a"      "q2"       "q2a"      "q3a"      "q3b"      "q3c"     
##  [19] "q3d"      "q5a"      "q5b"      "q5c"      "q5d"      "q5e"     
##  [25] "q5f"      "q5g"      "q6"       "q7"       "q8"       "q8a"     
##  [31] "q9"       "q10a"     "q10b"     "q10c"     "q10d"     "marital" 
##  [37] "hisp"     "race"     "q16"      "chr"      "q17"      "q17a"    
##  [43] "q17b"     "q17c"     "q17d"     "q17e"     "q17f"     "q17g"    
##  [49] "q17h"     "q17i"     "q17j"     "q17k"     "q17l"     "q17m"    
##  [55] "q17n"     "q17o"     "q17p"     "q17q"     "q17r"     "q17s"    
##  [61] "q17t"     "q17u"     "q17v"     "q18"      "denom"    "family"  
##  [67] "reltrad"  "protfam"  "q19a"     "q19b"     "q20"      "q21"     
##  [73] "q27"      "q28a"     "q28b"     "q28c"     "q28d"     "q29"     
##  [79] "q30"      "q31"      "q32"      "q33"      "q34"      "q34a"    
##  [85] "q34b"     "q34c"     "q34d"     "q35"      "q36"      "q37"     
##  [91] "q38"      "q39a"     "q39b"     "q39c"     "q40a"     "q40b"    
##  [97] "q40c"     "q41"      "q42a"     "q42b"     "q42c"     "q42d"    
## [103] "q42e"     "q42f"     "q42g"     "q43a"     "q43b"     "q43c"    
## [109] "q43d"     "q46"      "q47"      "q50"      "q50a"     "q50b"    
## [115] "q51"      "q52"      "q53"      "children" "q55a"     "q55b"    
## [121] "q55c"     "sex"      "age"      "q60"      "q61"      "q63"     
## [127] "educ"     "income"   "regist"   "regicert" "party"    "partyln" 
## [133] "ideo"     "pvote04a" "pvote04b"

Select three columns

pew[c("q16", "reltrad", "income")]

Clean ———————————————————————–

religion <- pew[c("q16", "reltrad", "income")]
class(religion$reltrad)
religion$reltrad <- as.character(religion$reltrad)
religion$reltrad <- str_replace(religion$reltrad, " Churches", "")
religion$reltrad <- str_replace(religion$reltrad, " Protestant", " Prot")
religion$reltrad[religion$q16 == " Atheist (do not believe in God) "] <- "Atheist"
religion$reltrad[religion$q16 == " Agnostic (not sure if there is a God) "] <- "Agnostic"
religion$reltrad <- str_trim(religion$reltrad)
religion$reltrad <- str_replace_all(religion$reltrad, " \\(.*?\\)", "")
religion$income <- c("Less than $10,000" = "<$10k", 
  "10 to under $20,000" = "$10-20k", 
  "20 to under $30,000" = "$20-30k", 
  "30 to under $40,000" = "$30-40k", 
  "40 to under $50,000" = "$40-50k", 
  "50 to under $75,000" = "$50-75k",
  "75 to under $100,000" = "$75-100k", 
  "100 to under $150,000" = "$100-150k", 
  "$150,000 or more" = ">150k", 
  "Don't know/Refused (VOL)" = "Don't know/refused")[religion$income]

religion$income <- factor(religion$income, levels = c("<$10k", "$10-20k", "$20-30k", "$30-40k", "$40-50k", "$50-75k", 
  "$75-100k", "$100-150k", ">150k", "Don't know/refused"))

Melt ———————————————————————–

counts <- count(religion, c("reltrad", "income"))
names(counts)[1] <- "religion"
head(counts)
##   religion  income freq
## 1 Agnostic   <$10k   27
## 2 Agnostic $10-20k   34
## 3 Agnostic $20-30k   60
## 4 Agnostic $30-40k   81
## 5 Agnostic $40-50k   76
## 6 Agnostic $50-75k  137

Multiple variables stored in one column: TB dataset

Load ———————————————————————–

raw <- read.csv("https://raw.githubusercontent.com/XUKEREN/KERENXU/master/data/tb.csv", na.strings = "")
raw$new_sp <- NULL
raw <- subset(raw, year == 2000)
names(raw)[1] <- "country"

names(raw) <- str_replace(names(raw), "new_sp_", "")
raw$m04 <- NULL
raw$m514 <- NULL
raw$f04 <- NULL
raw$f514 <- NULL

Melt ———————————————————————–

clean <- melt(raw, id = c("country", "year"), na.rm = TRUE)
names(clean)[3] <- "column"
names(clean)[4] <- "cases"

clean <- arrange(clean, country, column, year)

Break up variable in to sex and age —————————————-

clean$sex <- str_sub(clean$column, 1, 1)

ages <- c(`04` = "0--4", `514` = "5--14", `014` = "0--14", `1524` = "15--24", `2534` = "25--34", 
  `3544` = "35--44", `4554` = "45--54", `5564` = "55--64", `65` = "65+", u = NA)

clean$age <- factor(ages[str_sub(clean$column, 2)], levels = ages)

clean <- clean[c("country", "year", "sex", "age", "cases")]