Cleaning-operations

Published

September 13, 2022

Author: Dr. Hickman

# knitr::opts_chunk$set(echo = TRUE)

Gapminder example

library('tidyverse')

print("----ORIGINAL----")
gap <- read_csv('data/gapminder.csv')
head(gap)
[1] "----ORIGINAL----"
Rows: 1704 Columns: 6
-- Column specification --------------------------------------------------------
Delimiter: ","
chr (2): country, continent
dbl (4): year, lifeExp, pop, gdpPercap

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
A tibble: 6 x 6
country continent year lifeExp pop gdpPercap
<chr> <chr> <dbl> <dbl> <dbl> <dbl>
Afghanistan Asia 1952 28.801 8425333 779.4453
Afghanistan Asia 1957 30.332 9240934 820.8530
Afghanistan Asia 1962 31.997 10267083 853.1007
Afghanistan Asia 1967 34.020 11537966 836.1971
Afghanistan Asia 1972 36.088 13079460 739.9811
Afghanistan Asia 1977 38.438 14880372 786.1134
print("----GROUP BY CONTINENT----")
gap %>% group_by(continent)

print("----AVERAGE LIFE EXPECTANCY BY CONTINENT----")
gap %>% group_by(continent) %>% summarize(avgLifeExp = mean(lifeExp))


print("----AVERAGE LIFE EXPECTANCY BY COUNTRY----")
gap %>% group_by(country) %>% summarize(avgLifeExp = mean(lifeExp))

print("----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----")
gap %>% group_by(continent, year) %>% summarize(mean(lifeExp))

print("----AVERAGE+MEDIAN EXPECTANCY BY YEAR----")
gap %>% group_by(year) %>% summarize(means = mean(lifeExp), medians = median(lifeExp))
[1] "----GROUP BY CONTINENT----"
A grouped_df: 1704 x 6
country continent year lifeExp pop gdpPercap
<chr> <chr> <dbl> <dbl> <dbl> <dbl>
Afghanistan Asia 1952 28.801 8425333 779.4453
Afghanistan Asia 1957 30.332 9240934 820.8530
Afghanistan Asia 1962 31.997 10267083 853.1007
Afghanistan Asia 1967 34.020 11537966 836.1971
Afghanistan Asia 1972 36.088 13079460 739.9811
Afghanistan Asia 1977 38.438 14880372 786.1134
Afghanistan Asia 1982 39.854 12881816 978.0114
Afghanistan Asia 1987 40.822 13867957 852.3959
Afghanistan Asia 1992 41.674 16317921 649.3414
Afghanistan Asia 1997 41.763 22227415 635.3414
Afghanistan Asia 2002 42.129 25268405 726.7341
Afghanistan Asia 2007 43.828 31889923 974.5803
Albania Europe 1952 55.230 1282697 1601.0561
Albania Europe 1957 59.280 1476505 1942.2842
Albania Europe 1962 64.820 1728137 2312.8890
Albania Europe 1967 66.220 1984060 2760.1969
Albania Europe 1972 67.690 2263554 3313.4222
Albania Europe 1977 68.930 2509048 3533.0039
Albania Europe 1982 70.420 2780097 3630.8807
Albania Europe 1987 72.000 3075321 3738.9327
Albania Europe 1992 71.581 3326498 2497.4379
Albania Europe 1997 72.950 3428038 3193.0546
Albania Europe 2002 75.651 3508512 4604.2117
Albania Europe 2007 76.423 3600523 5937.0295
Algeria Africa 1952 43.077 9279525 2449.0082
Algeria Africa 1957 45.685 10270856 3013.9760
Algeria Africa 1962 48.303 11000948 2550.8169
Algeria Africa 1967 51.407 12760499 3246.9918
Algeria Africa 1972 54.518 14760787 4182.6638
Algeria Africa 1977 58.014 17152804 4910.4168
... ... ... ... ... ...
Yemen, Rep. Asia 1982 49.113 9657618 1977.5570
Yemen, Rep. Asia 1987 52.922 11219340 1971.7415
Yemen, Rep. Asia 1992 55.599 13367997 1879.4967
Yemen, Rep. Asia 1997 58.020 15826497 2117.4845
Yemen, Rep. Asia 2002 60.308 18701257 2234.8208
Yemen, Rep. Asia 2007 62.698 22211743 2280.7699
Zambia Africa 1952 42.038 2672000 1147.3888
Zambia Africa 1957 44.077 3016000 1311.9568
Zambia Africa 1962 46.023 3421000 1452.7258
Zambia Africa 1967 47.768 3900000 1777.0773
Zambia Africa 1972 50.107 4506497 1773.4983
Zambia Africa 1977 51.386 5216550 1588.6883
Zambia Africa 1982 51.821 6100407 1408.6786
Zambia Africa 1987 50.821 7272406 1213.3151
Zambia Africa 1992 46.100 8381163 1210.8846
Zambia Africa 1997 40.238 9417789 1071.3538
Zambia Africa 2002 39.193 10595811 1071.6139
Zambia Africa 2007 42.384 11746035 1271.2116
Zimbabwe Africa 1952 48.451 3080907 406.8841
Zimbabwe Africa 1957 50.469 3646340 518.7643
Zimbabwe Africa 1962 52.358 4277736 527.2722
Zimbabwe Africa 1967 53.995 4995432 569.7951
Zimbabwe Africa 1972 55.635 5861135 799.3622
Zimbabwe Africa 1977 57.674 6642107 685.5877
Zimbabwe Africa 1982 60.363 7636524 788.8550
Zimbabwe Africa 1987 62.351 9216418 706.1573
Zimbabwe Africa 1992 60.377 10704340 693.4208
Zimbabwe Africa 1997 46.809 11404948 792.4500
Zimbabwe Africa 2002 39.989 11926563 672.0386
Zimbabwe Africa 2007 43.487 12311143 469.7093
[1] "----AVERAGE LIFE EXPECTANCY BY CONTINENT----"
A tibble: 5 x 2
continent avgLifeExp
<chr> <dbl>
Africa 48.86533
Americas 64.65874
Asia 60.06490
Europe 71.90369
Oceania 74.32621
[1] "----AVERAGE LIFE EXPECTANCY BY COUNTRY----"
A tibble: 142 x 2
country avgLifeExp
<chr> <dbl>
Afghanistan 37.47883
Albania 68.43292
Algeria 59.03017
Angola 37.88350
Argentina 69.06042
Australia 74.66292
Austria 73.10325
Bahrain 65.60567
Bangladesh 49.83408
Belgium 73.64175
Benin 48.77992
Bolivia 52.50458
Bosnia and Herzegovina 67.70783
Botswana 54.59750
Brazil 62.23950
Bulgaria 69.74375
Burkina Faso 44.69400
Burundi 44.81733
Cambodia 47.90275
Cameroon 48.12850
Canada 74.90275
Central African Republic 43.86692
Chad 46.77358
Chile 67.43092
China 61.78514
Colombia 63.89775
Comoros 52.38175
Congo, Dem. Rep. 44.54375
Congo, Rep. 52.50192
Costa Rica 70.18142
... ...
Sierra Leone 36.76917
Singapore 71.22025
Slovak Republic 70.69608
Slovenia 71.60075
Somalia 40.98867
South Africa 53.99317
Spain 74.20342
Sri Lanka 66.52608
Sudan 48.40050
Swaziland 49.00242
Sweden 76.17700
Switzerland 75.56508
Syria 61.34617
Taiwan 70.33667
Tanzania 47.91233
Thailand 62.20025
Togo 51.49875
Trinidad and Tobago 66.82800
Tunisia 60.72100
Turkey 59.69642
Uganda 47.61883
United Kingdom 73.92258
United States 73.47850
Uruguay 70.78158
Venezuela 66.58067
Vietnam 57.47950
West Bank and Gaza 60.32867
Yemen, Rep. 46.78042
Zambia 45.99633
Zimbabwe 52.66317
[1] "----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----"
`summarise()` has grouped output by 'continent'. You can override using the
`.groups` argument.
A grouped_df: 60 x 3
continent year mean(lifeExp)
<chr> <dbl> <dbl>
Africa 1952 39.13550
Africa 1957 41.26635
Africa 1962 43.31944
Africa 1967 45.33454
Africa 1972 47.45094
Africa 1977 49.58042
Africa 1982 51.59287
Africa 1987 53.34479
Africa 1992 53.62958
Africa 1997 53.59827
Africa 2002 53.32523
Africa 2007 54.80604
Americas 1952 53.27984
Americas 1957 55.96028
Americas 1962 58.39876
Americas 1967 60.41092
Americas 1972 62.39492
Americas 1977 64.39156
Americas 1982 66.22884
Americas 1987 68.09072
Americas 1992 69.56836
Americas 1997 71.15048
Americas 2002 72.42204
Americas 2007 73.60812
Asia 1952 46.31439
Asia 1957 49.31854
Asia 1962 51.56322
Asia 1967 54.66364
Asia 1972 57.31927
Asia 1977 59.61056
Asia 1982 62.61794
Asia 1987 64.85118
Asia 1992 66.53721
Asia 1997 68.02052
Asia 2002 69.23388
Asia 2007 70.72848
Europe 1952 64.40850
Europe 1957 66.70307
Europe 1962 68.53923
Europe 1967 69.73760
Europe 1972 70.77503
Europe 1977 71.93777
Europe 1982 72.80640
Europe 1987 73.64217
Europe 1992 74.44010
Europe 1997 75.50517
Europe 2002 76.70060
Europe 2007 77.64860
Oceania 1952 69.25500
Oceania 1957 70.29500
Oceania 1962 71.08500
Oceania 1967 71.31000
Oceania 1972 71.91000
Oceania 1977 72.85500
Oceania 1982 74.29000
Oceania 1987 75.32000
Oceania 1992 76.94500
Oceania 1997 78.19000
Oceania 2002 79.74000
Oceania 2007 80.71950
[1] "----AVERAGE+MEDIAN EXPECTANCY BY YEAR----"
A tibble: 12 x 3
year means medians
<dbl> <dbl> <dbl>
1952 49.05762 45.1355
1957 51.50740 48.3605
1962 53.60925 50.8810
1967 55.67829 53.8250
1972 57.64739 56.5300
1977 59.57016 59.6720
1982 61.53320 62.4415
1987 63.21261 65.8340
1992 64.16034 67.7030
1997 65.01468 69.3940
2002 65.69492 70.8255
2007 67.00742 71.9355

JOINING

#LOAD DATA
df1 <- read_csv('data/toy-1.csv')
df2 <- read_csv('data/toy-2.csv')

print("----ORIGINAL----")
head(df1)
head(df2)

print("----INNER JOIN----")
df1 %>% inner_join(df2) %>% head()
df1 %>% inner_join(df2, by = c('x')) %>% head()
df1 %>% inner_join(df2, by = c('x' , 'w')) %>% head()

print("----LEFT JOIN----")
df1 %>% left_join(df2) %>% head()
# df1 %>% left_join(df2,by = c('x' , 'y')) %>% head()

print("----RIGHT JOIN----")
df1 %>% right_join(df2) %>% head()

print("----FULL JOIN----")
df1 %>% full_join(df2) %>% head()
Rows: 3 Columns: 3
-- Column specification --------------------------------------------------------
Delimiter: ","
chr (3): x, y, w

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 4 Columns: 4
-- Column specification --------------------------------------------------------
Delimiter: ","
chr (4): x, y, w, z

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
[1] "----ORIGINAL----"
A tibble: 3 x 3
x y w
<chr> <chr> <chr>
x1 y1 w1
x2 y2 w2
x5 y5 w5
A tibble: 4 x 4
x y w z
<chr> <chr> <chr> <chr>
x1 y1 w1 z1
x2 y2 w2 z2
x3 y3 w3 z3
x4 y4 w4 z4
[1] "----INNER JOIN----"
Joining with `by = join_by(x, y, w)`
A tibble: 2 x 4
x y w z
<chr> <chr> <chr> <chr>
x1 y1 w1 z1
x2 y2 w2 z2
A tibble: 2 x 6
x y.x w.x y.y w.y z
<chr> <chr> <chr> <chr> <chr> <chr>
x1 y1 w1 y1 w1 z1
x2 y2 w2 y2 w2 z2
A tibble: 2 x 5
x y.x w y.y z
<chr> <chr> <chr> <chr> <chr>
x1 y1 w1 y1 z1
x2 y2 w2 y2 z2
[1] "----LEFT JOIN----"
Joining with `by = join_by(x, y, w)`
A tibble: 3 x 4
x y w z
<chr> <chr> <chr> <chr>
x1 y1 w1 z1
x2 y2 w2 z2
x5 y5 w5 NA
[1] "----RIGHT JOIN----"
Joining with `by = join_by(x, y, w)`
A tibble: 4 x 4
x y w z
<chr> <chr> <chr> <chr>
x1 y1 w1 z1
x2 y2 w2 z2
x3 y3 w3 z3
x4 y4 w4 z4
[1] "----FULL JOIN----"
Joining with `by = join_by(x, y, w)`
A tibble: 5 x 4
x y w z
<chr> <chr> <chr> <chr>
x1 y1 w1 z1
x2 y2 w2 z2
x5 y5 w5 NA
x3 y3 w3 z3
x4 y4 w4 z4

Mutate, select, filter, arrange, convert

#NOTES:

# %>% (similar to | in linux) is called the forward pipe operator in R.
# It provides a mechanism 
# for chaining commands with a new forward-pipe operator, %>%. 
# This operator will forward a value, or the result of an expression,
# into the next function call/expression.
# It is defined by the package magrittr (CRAN) and is heavily used by dplyr (CRAN).


#DEFINITIONS
# MUTATE  = TRANSFORM A COLUMN WITH SOME FUNCTION
# SELECT  = SELECT SOME COLUMNS IN THE DATA
# FILTER  = KEEP ONLY ROWS THAT MEET SOME DATA CRITERION
# ARRANGE = ORDER THE DATA FRAME BY VALUES OF A COLUMN(S)

#CONVERSION
# CONVERT THE CITY AND HIGHWAY FUEL 
# EFFICIENCY TO KM/L, AND
# FIND AVERAGE FUEL EFFICIENCY
# 1 mile=1.60934KM
# 1 gallon=3.7854 liter


#-----------------------------
print("----READ----")
#-----------------------------
mpg1 <- read_csv('data/mpg.csv')
head(mpg1)

#-----------------------------
print("----MUTATE----")
#-----------------------------
# OPTION-1: SEND MPG TO MUTATE
 mpg2 <- mutate(mpg1, cty = cty * 1.6/3.8,
         hwy = hwy * 1.6/3.8, 
         avg = (cty + hwy)/2)
head(mpg2); #print(mpg2['avg'])

#-----------------------------
print("----MUTATE----")
#-----------------------------
#OPTION-2: PIPE MPG INTO MUTATE 
mpg1 <- mpg1 %>% mutate(cty = cty * 1.6/3.8,
         hwy = hwy * 1.6/3.8, 
         avg = (cty + hwy)/2)
head(mpg1)

#-----------------------------
print("----SELECT----")
#-----------------------------
mpg2 <- mpg1 %>% select(year, manufacturer, avg)
head(mpg2)

#-----------------------------
print("----FILTER----")  
#-----------------------------
mpg2 <- mpg1 %>% filter(manufacturer=='audi')
head(mpg2)

#-----------------------------
print("----ARRANGE----")  
#-----------------------------
mpg2 <-mpg1 %>% arrange(-year)
head(mpg2)

#-----------------------------
print("----FILTER THEN ARRANGE----")  
#-----------------------------
#CHAIN TOGETHER 
mpg2 <-mpg1 %>% filter(manufacturer=='audi') %>% arrange(-year)  
head(mpg2,3)

#-----------------------------
#CONVERT TO FACTOR 
#-----------------------------
mpg2['model']=as.factor(mpg2$model)
head(mpg2,3)
[1] "----READ----"
New names:
* `` -> `...1`
Rows: 234 Columns: 12
-- Column specification --------------------------------------------------------
Delimiter: ","
chr (6): manufacturer, model, trans, drv, fl, class
dbl (6): ...1, displ, year, cyl, cty, hwy

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
A tibble: 6 x 12
...1 manufacturer model displ year cyl trans drv cty hwy fl class
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 audi a4 1.8 1999 4 auto(l5) f 18 29 p compact
2 audi a4 1.8 1999 4 manual(m5) f 21 29 p compact
3 audi a4 2.0 2008 4 manual(m6) f 20 31 p compact
4 audi a4 2.0 2008 4 auto(av) f 21 30 p compact
5 audi a4 2.8 1999 6 auto(l5) f 16 26 p compact
6 audi a4 2.8 1999 6 manual(m5) f 18 26 p compact
[1] "----MUTATE----"
A tibble: 6 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
1 audi a4 1.8 1999 4 auto(l5) f 7.578947 12.21053 p compact 9.894737
2 audi a4 1.8 1999 4 manual(m5) f 8.842105 12.21053 p compact 10.526316
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
5 audi a4 2.8 1999 6 auto(l5) f 6.736842 10.94737 p compact 8.842105
6 audi a4 2.8 1999 6 manual(m5) f 7.578947 10.94737 p compact 9.263158
[1] "----MUTATE----"
A tibble: 6 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
1 audi a4 1.8 1999 4 auto(l5) f 7.578947 12.21053 p compact 9.894737
2 audi a4 1.8 1999 4 manual(m5) f 8.842105 12.21053 p compact 10.526316
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
5 audi a4 2.8 1999 6 auto(l5) f 6.736842 10.94737 p compact 8.842105
6 audi a4 2.8 1999 6 manual(m5) f 7.578947 10.94737 p compact 9.263158
[1] "----SELECT----"
A tibble: 6 x 3
year manufacturer avg
<dbl> <chr> <dbl>
1999 audi 9.894737
1999 audi 10.526316
2008 audi 10.736842
2008 audi 10.736842
1999 audi 8.842105
1999 audi 9.263158
[1] "----FILTER----"
A tibble: 6 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
1 audi a4 1.8 1999 4 auto(l5) f 7.578947 12.21053 p compact 9.894737
2 audi a4 1.8 1999 4 manual(m5) f 8.842105 12.21053 p compact 10.526316
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
5 audi a4 2.8 1999 6 auto(l5) f 6.736842 10.94737 p compact 8.842105
6 audi a4 2.8 1999 6 manual(m5) f 7.578947 10.94737 p compact 9.263158
[1] "----ARRANGE----"
A tibble: 6 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
7 audi a4 3.1 2008 6 auto(av) f 7.578947 11.36842 p compact 9.473684
10 audi a4 quattro 2.0 2008 4 manual(m6) 4 8.421053 11.78947 p compact 10.105263
11 audi a4 quattro 2.0 2008 4 auto(s6) 4 8.000000 11.36842 p compact 9.684211
14 audi a4 quattro 3.1 2008 6 auto(s6) 4 7.157895 10.52632 p compact 8.842105
[1] "----FILTER THEN ARRANGE----"
A tibble: 3 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
7 audi a4 3.1 2008 6 auto(av) f 7.578947 11.36842 p compact 9.473684
A tibble: 3 x 13
...1 manufacturer model displ year cyl trans drv cty hwy fl class avg
<dbl> <chr> <fct> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <dbl>
3 audi a4 2.0 2008 4 manual(m6) f 8.421053 13.05263 p compact 10.736842
4 audi a4 2.0 2008 4 auto(av) f 8.842105 12.63158 p compact 10.736842
7 audi a4 3.1 2008 6 auto(av) f 7.578947 11.36842 p compact 9.473684

Melting and pivoting

pew <- read_csv('data/pew.csv')
print(pew)

# WE NEED TO MELT THIS DATASET TO MAKE IT TIDY
pew1 <- pew %>% pivot_longer(cols = c(-religion), names_to = 'income_groups', values_to = 'counts')
print(pew1)

# There is a difference in row-order between the R and Python solutions.
# R sorts by the id variable(s), Python sorts on the column containing
# the old column headers. Analytically, this can be fixed, and is often not important for visualization.


#WE REVERSE THIS PROCESS BY PIVOTING THE DATA SET
pew2 <- pew1 %>% pivot_wider(id_cols = c(religion), names_from = 'income_groups', values_from = 'counts')
head(pew2,2)
Rows: 18 Columns: 11
-- Column specification --------------------------------------------------------
Delimiter: ","
chr  (1): religion
dbl (10): <$10k, $10-20k, $20-30k, $30-40k, $40-50k, $50-75k, $75-100k, $100...

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
# A tibble: 18 x 11
   religion      `<$10k` $10-2~1 $20-3~2 $30-4~3 $40-5~4 $50-7~5 $75-1~6 $100-~7
   <chr>           <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 "Agnostic"         27      34      60      81      76     137     122     109
 2 "Atheist"          12      27      37      52      35      70      73      59
 3 "Buddhist"         27      21      30      34      33      58      62      39
 4 "Catholic"        418     617     732     670     638    1116     949     792
 5 "Don\u2019t ~      15      14      15      11      10      35      21      17
 6 "Evangelical~     575     869    1064     982     881    1486     949     723
 7 "Hindu"             1       9       7       9      11      34      47      48
 8 "Historicall~     228     244     236     238     197     223     131      81
 9 "Jehovah's W~      20      27      24      24      21      30      15      11
10 "Jewish"           19      19      25      25      30      95      69      87
11 "Mainline Pr~     289     495     619     655     651    1107     939     753
12 "Mormon"           29      40      48      51      56     112      85      49
13 "Muslim"            6       7       9      10       9      23      16       8
14 "Orthodox"         13      17      23      32      32      47      38      42
15 "Other Chris~       9       7      11      13      13      14      18      14
16 "Other Faith~      20      33      40      46      49      63      46      40
17 "Other World~       5       2       3       4       2       7       3       4
18 "Unaffiliate~     217     299     374     365     341     528     407     321
# ... with 2 more variables: `>150k` <dbl>, `Don't know/refused` <dbl>, and
#   abbreviated variable names 1: `$10-20k`, 2: `$20-30k`, 3: `$30-40k`,
#   4: `$40-50k`, 5: `$50-75k`, 6: `$75-100k`, 7: `$100-150k`
# A tibble: 180 x 3
   religion income_groups      counts
   <chr>    <chr>               <dbl>
 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
 7 Agnostic $75-100k              122
 8 Agnostic $100-150k             109
 9 Agnostic >150k                  84
10 Agnostic Don't know/refused     96
# ... with 170 more rows
A tibble: 2 x 11
religion <$10k $10-20k $20-30k $30-40k $40-50k $50-75k $75-100k $100-150k >150k Don't know/refused
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
Agnostic 27 34 60 81 76 137 122 109 84 96
Atheist 12 27 37 52 35 70 73 59 74 76

Splitting

#READ
tb <- read_csv('data/tb.csv')
print("ORIGINAL")
head(tb, 3)

#MELT
tb1 <- tb %>% pivot_longer(cols=m014:f65, names_to='demo', values_to='counts')
print("AFTER MELT")
head(tb1, 3)

#SPLIT
tb2 <- tb1 %>% separate(demo, c('gender','age'), sep = 1)
print("AFTER SPLIT ")
print(head(tb2, 3))

# TB <-  read_csv('data/tb1.csv') %>% 
#   pivot_longer(cols = starts_with('new'),names_to='demo', values_to='counts')
# head(TB, 3)

# TB1 <- TB %>% extract(demo, c('type','gender','age'),  regex = 'new[_]*([a-z]+)_([mf])([0-9]+$)')
# head(TB1)
Rows: 3000 Columns: 16
-- Column specification --------------------------------------------------------
Delimiter: ","
chr  (1): iso2
dbl (15): year, m014, m1524, m2534, m3544, m4554, m5564, m65, f014, f1524, f...

i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.
[1] "ORIGINAL"
A tibble: 3 x 16
iso2 year m014 m1524 m2534 m3544 m4554 m5564 m65 f014 f1524 f2534 f3544 f4554 f5564 f65
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
AF 2000 52 228 183 149 129 94 80 93 414 565 339 205 99 36
AF 2001 129 379 349 274 204 139 103 146 799 888 586 375 179 89
AF 2002 90 476 481 368 246 241 189 192 1119 1251 792 526 320 218
[1] "AFTER MELT"
A tibble: 3 x 4
iso2 year demo counts
<chr> <dbl> <chr> <dbl>
AF 2000 m014 52
AF 2000 m1524 228
AF 2000 m2534 183
[1] "AFTER SPLIT "
# A tibble: 3 x 5
  iso2   year gender age   counts
  <chr> <dbl> <chr>  <chr>  <dbl>
1 AF     2000 m      014       52
2 AF     2000 m      1524     228
3 AF     2000 m      2534     183