This vignette for the unpivotr package demonstrates unpivoting pivot tables of various kinds imported from a spreadsheet via the tidyxl package. It is best read with the spreadsheet open in a spreadsheet program, e.g. Excel, LibreOffice Calc or Gnumeric.
The spreadsheet is in the package directory at system.file("extdata", "purpose.xlsx", package = "unpivotr").
##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union## X2 X3 X4 X5 X6 X7
## 1 <NA> <NA> Female <NA> Male <NA>
## 2 <NA> <NA> 0 - 6 7 - 10 0 - 6 7 - 10
## 3 Bachelor's degree 15 - 24 7000 27000 <NA> 13000
## 4 <NA> 25 - 44 12000 137000 9000 81000
## 5 <NA> 45 - 64 10000 64000 7000 66000
## 6 <NA> 65+ <NA> 18000 7000 17000
## 7 Certificate 15 - 24 29000 161000 30000 190000
## 8 <NA> 25 - 44 34000 179000 31000 219000
## 9 <NA> 45 - 64 30000 210000 23000 199000
## 10 <NA> 65+ 12000 77000 8000 107000
## 11 Diploma 15 - 24 <NA> 14000 9000 11000
## 12 <NA> 25 - 44 10000 66000 8000 47000
## 13 <NA> 45 - 64 6000 68000 5000 58000
## 14 <NA> 65+ 5000 41000 1000 34000
## 15 No Qualification 15 - 24 10000 43000 12000 37000
## 16 <NA> 25 - 44 11000 36000 21000 50000
## 17 <NA> 45 - 64 19000 91000 17000 75000
## 18 <NA> 65+ 16000 118000 9000 66000
## 19 Postgraduate qualification 15 - 24 <NA> 6000 <NA> <NA>
## 20 <NA> 25 - 44 5000 86000 7000 60000
## 21 <NA> 45 - 64 6000 55000 6000 68000
## 22 <NA> 65+ <NA> 13000 <NA> 18000## # A tibble: 6 x 4
## row col data_type chr
## <int> <int> <chr> <chr>
## 1 17 6 chr 75000
## 2 18 6 chr 66000
## 3 19 6 chr <NA>
## 4 20 6 chr 60000
## 5 21 6 chr 68000
## 6 22 6 chr 18000row_headers <-
cells %>%
dplyr::filter(col <= 2, !is.na(chr)) %>% # Select all rows of headers at once
select(row, col, header = chr) %>%
split(.$col) # Return each row of headers in its own element of a list
row_headers## $`1`
## # A tibble: 5 x 3
## row col header
## <int> <int> <chr>
## 1 3 1 Bachelor's degree
## 2 7 1 Certificate
## 3 11 1 Diploma
## 4 15 1 No Qualification
## 5 19 1 Postgraduate qualification
##
## $`2`
## # A tibble: 20 x 3
## row col header
## <int> <int> <chr>
## 1 3 2 15 - 24
## 2 4 2 25 - 44
## 3 5 2 45 - 64
## 4 6 2 65+
## 5 7 2 15 - 24
## 6 8 2 25 - 44
## 7 9 2 45 - 64
## 8 10 2 65+
## 9 11 2 15 - 24
## 10 12 2 25 - 44
## 11 13 2 45 - 64
## 12 14 2 65+
## 13 15 2 15 - 24
## 14 16 2 25 - 44
## 15 17 2 45 - 64
## 16 18 2 65+
## 17 19 2 15 - 24
## 18 20 2 25 - 44
## 19 21 2 45 - 64
## 20 22 2 65+col_headers <-
cells %>%
dplyr::filter(row <= 2, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`1`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 1 3 Female
## 2 1 5 Male
##
## $`2`
## # A tibble: 4 x 3
## row col header
## <int> <int> <chr>
## 1 2 3 0 - 6
## 2 2 4 7 - 10
## 3 2 5 0 - 6
## 4 2 6 7 - 10data_cells <-
cells %>%
dplyr::filter(row >= 3, col >= 3, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
head(data_cells)## # A tibble: 6 x 3
## row col value
## <int> <int> <int>
## 1 3 3 7000
## 2 4 3 12000
## 3 5 3 10000
## 4 7 3 29000
## 5 8 3 34000
## 6 9 3 30000## row col value header.data header.header header.data.data
## 1 3 3 7000 Female 0 - 6 Bachelor's degree
## 2 4 3 12000 Female 0 - 6 Bachelor's degree
## 3 5 3 10000 Female 0 - 6 Bachelor's degree
## 4 3 4 27000 Female 7 - 10 Bachelor's degree
## 5 4 4 137000 Female 7 - 10 Bachelor's degree
## 6 5 4 64000 Female 7 - 10 Bachelor's degree
## 7 6 4 18000 Female 7 - 10 Bachelor's degree
## 8 4 5 9000 Male 0 - 6 Bachelor's degree
## 9 5 5 7000 Male 0 - 6 Bachelor's degree
## 10 6 5 7000 Male 0 - 6 Bachelor's degree
## 11 3 6 13000 Male 7 - 10 Bachelor's degree
## 12 4 6 81000 Male 7 - 10 Bachelor's degree
## 13 5 6 66000 Male 7 - 10 Bachelor's degree
## 14 6 6 17000 Male 7 - 10 Bachelor's degree
## 15 7 3 29000 Female 0 - 6 Certificate
## 16 8 3 34000 Female 0 - 6 Certificate
## 17 9 3 30000 Female 0 - 6 Certificate
## 18 10 3 12000 Female 0 - 6 Certificate
## 19 7 4 161000 Female 7 - 10 Certificate
## 20 8 4 179000 Female 7 - 10 Certificate
## 21 9 4 210000 Female 7 - 10 Certificate
## 22 10 4 77000 Female 7 - 10 Certificate
## 23 7 5 30000 Male 0 - 6 Certificate
## 24 8 5 31000 Male 0 - 6 Certificate
## 25 9 5 23000 Male 0 - 6 Certificate
## 26 10 5 8000 Male 0 - 6 Certificate
## 27 7 6 190000 Male 7 - 10 Certificate
## 28 8 6 219000 Male 7 - 10 Certificate
## 29 9 6 199000 Male 7 - 10 Certificate
## 30 10 6 107000 Male 7 - 10 Certificate
## 31 12 3 10000 Female 0 - 6 Diploma
## 32 13 3 6000 Female 0 - 6 Diploma
## 33 14 3 5000 Female 0 - 6 Diploma
## 34 11 4 14000 Female 7 - 10 Diploma
## 35 12 4 66000 Female 7 - 10 Diploma
## 36 13 4 68000 Female 7 - 10 Diploma
## 37 14 4 41000 Female 7 - 10 Diploma
## 38 11 5 9000 Male 0 - 6 Diploma
## 39 12 5 8000 Male 0 - 6 Diploma
## 40 13 5 5000 Male 0 - 6 Diploma
## 41 14 5 1000 Male 0 - 6 Diploma
## 42 11 6 11000 Male 7 - 10 Diploma
## 43 12 6 47000 Male 7 - 10 Diploma
## 44 13 6 58000 Male 7 - 10 Diploma
## 45 14 6 34000 Male 7 - 10 Diploma
## 46 15 3 10000 Female 0 - 6 No Qualification
## 47 16 3 11000 Female 0 - 6 No Qualification
## 48 17 3 19000 Female 0 - 6 No Qualification
## 49 18 3 16000 Female 0 - 6 No Qualification
## 50 15 4 43000 Female 7 - 10 No Qualification
## 51 16 4 36000 Female 7 - 10 No Qualification
## 52 17 4 91000 Female 7 - 10 No Qualification
## 53 18 4 118000 Female 7 - 10 No Qualification
## 54 15 5 12000 Male 0 - 6 No Qualification
## 55 16 5 21000 Male 0 - 6 No Qualification
## 56 17 5 17000 Male 0 - 6 No Qualification
## 57 18 5 9000 Male 0 - 6 No Qualification
## 58 15 6 37000 Male 7 - 10 No Qualification
## 59 16 6 50000 Male 7 - 10 No Qualification
## 60 17 6 75000 Male 7 - 10 No Qualification
## 61 18 6 66000 Male 7 - 10 No Qualification
## 62 20 3 5000 Female 0 - 6 Postgraduate qualification
## 63 21 3 6000 Female 0 - 6 Postgraduate qualification
## 64 19 4 6000 Female 7 - 10 Postgraduate qualification
## 65 20 4 86000 Female 7 - 10 Postgraduate qualification
## 66 21 4 55000 Female 7 - 10 Postgraduate qualification
## 67 22 4 13000 Female 7 - 10 Postgraduate qualification
## 68 20 5 7000 Male 0 - 6 Postgraduate qualification
## 69 21 5 6000 Male 0 - 6 Postgraduate qualification
## 70 20 6 60000 Male 7 - 10 Postgraduate qualification
## 71 21 6 68000 Male 7 - 10 Postgraduate qualification
## 72 22 6 18000 Male 7 - 10 Postgraduate qualification
## header.header.header
## 1 15 - 24
## 2 25 - 44
## 3 45 - 64
## 4 15 - 24
## 5 25 - 44
## 6 45 - 64
## 7 65+
## 8 25 - 44
## 9 45 - 64
## 10 65+
## 11 15 - 24
## 12 25 - 44
## 13 45 - 64
## 14 65+
## 15 15 - 24
## 16 25 - 44
## 17 45 - 64
## 18 65+
## 19 15 - 24
## 20 25 - 44
## 21 45 - 64
## 22 65+
## 23 15 - 24
## 24 25 - 44
## 25 45 - 64
## 26 65+
## 27 15 - 24
## 28 25 - 44
## 29 45 - 64
## 30 65+
## 31 25 - 44
## 32 45 - 64
## 33 65+
## 34 15 - 24
## 35 25 - 44
## 36 45 - 64
## 37 65+
## 38 15 - 24
## 39 25 - 44
## 40 45 - 64
## 41 65+
## 42 15 - 24
## 43 25 - 44
## 44 45 - 64
## 45 65+
## 46 15 - 24
## 47 25 - 44
## 48 45 - 64
## 49 65+
## 50 15 - 24
## 51 25 - 44
## 52 45 - 64
## 53 65+
## 54 15 - 24
## 55 25 - 44
## 56 45 - 64
## 57 65+
## 58 15 - 24
## 59 25 - 44
## 60 45 - 64
## 61 65+
## 62 25 - 44
## 63 45 - 64
## 64 15 - 24
## 65 25 - 44
## 66 45 - 64
## 67 65+
## 68 25 - 44
## 69 45 - 64
## 70 25 - 44
## 71 45 - 64
## 72 65+row_headers <-
cells %>%
dplyr::filter(col <= 2, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$col)
row_headers## $`1`
## # A tibble: 5 x 3
## row col header
## <int> <int> <chr>
## 1 6 1 Bachelor's degree
## 2 10 1 Certificate
## 3 14 1 Diploma
## 4 18 1 No Qualification
## 5 22 1 Postgraduate qualification
##
## $`2`
## # A tibble: 20 x 3
## row col header
## <int> <int> <chr>
## 1 3 2 15 - 24
## 2 4 2 25 - 44
## 3 5 2 45 - 64
## 4 6 2 65+
## 5 7 2 15 - 24
## 6 8 2 25 - 44
## 7 9 2 45 - 64
## 8 10 2 65+
## 9 11 2 15 - 24
## 10 12 2 25 - 44
## 11 13 2 45 - 64
## 12 14 2 65+
## 13 15 2 15 - 24
## 14 16 2 25 - 44
## 15 17 2 45 - 64
## 16 18 2 65+
## 17 19 2 15 - 24
## 18 20 2 25 - 44
## 19 21 2 45 - 64
## 20 22 2 65+col_headers <-
cells %>%
dplyr::filter(row <= 2, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`1`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 1 4 Female
## 2 1 6 Male
##
## $`2`
## # A tibble: 4 x 3
## row col header
## <int> <int> <chr>
## 1 2 3 0 - 6
## 2 2 4 7 - 10
## 3 2 5 0 - 6
## 4 2 6 7 - 10data_cells <-
cells %>%
dplyr::filter(row >= 3, col >= 3, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
data_cells## # A tibble: 72 x 3
## row col value
## <int> <int> <int>
## 1 3 3 7000
## 2 4 3 12000
## 3 5 3 10000
## 4 7 3 29000
## 5 8 3 34000
## 6 9 3 30000
## 7 10 3 12000
## 8 12 3 10000
## 9 13 3 6000
## 10 14 3 5000
## # … with 62 more rows## row col value header.data header.header header.data.data
## 1 3 3 7000 Female 0 - 6 Bachelor's degree
## 2 4 3 12000 Female 0 - 6 Bachelor's degree
## 3 5 3 10000 Female 0 - 6 Bachelor's degree
## 4 3 4 27000 Female 7 - 10 Bachelor's degree
## 5 4 4 137000 Female 7 - 10 Bachelor's degree
## 6 5 4 64000 Female 7 - 10 Bachelor's degree
## 7 6 4 18000 Female 7 - 10 Bachelor's degree
## 8 4 5 9000 Male 0 - 6 Bachelor's degree
## 9 5 5 7000 Male 0 - 6 Bachelor's degree
## 10 6 5 7000 Male 0 - 6 Bachelor's degree
## 11 3 6 13000 Male 7 - 10 Bachelor's degree
## 12 4 6 81000 Male 7 - 10 Bachelor's degree
## 13 5 6 66000 Male 7 - 10 Bachelor's degree
## 14 6 6 17000 Male 7 - 10 Bachelor's degree
## 15 7 3 29000 Female 0 - 6 Certificate
## 16 8 3 34000 Female 0 - 6 Certificate
## 17 9 3 30000 Female 0 - 6 Certificate
## 18 10 3 12000 Female 0 - 6 Certificate
## 19 7 4 161000 Female 7 - 10 Certificate
## 20 8 4 179000 Female 7 - 10 Certificate
## 21 9 4 210000 Female 7 - 10 Certificate
## 22 10 4 77000 Female 7 - 10 Certificate
## 23 7 5 30000 Male 0 - 6 Certificate
## 24 8 5 31000 Male 0 - 6 Certificate
## 25 9 5 23000 Male 0 - 6 Certificate
## 26 10 5 8000 Male 0 - 6 Certificate
## 27 7 6 190000 Male 7 - 10 Certificate
## 28 8 6 219000 Male 7 - 10 Certificate
## 29 9 6 199000 Male 7 - 10 Certificate
## 30 10 6 107000 Male 7 - 10 Certificate
## 31 12 3 10000 Female 0 - 6 Diploma
## 32 13 3 6000 Female 0 - 6 Diploma
## 33 14 3 5000 Female 0 - 6 Diploma
## 34 11 4 14000 Female 7 - 10 Diploma
## 35 12 4 66000 Female 7 - 10 Diploma
## 36 13 4 68000 Female 7 - 10 Diploma
## 37 14 4 41000 Female 7 - 10 Diploma
## 38 11 5 9000 Male 0 - 6 Diploma
## 39 12 5 8000 Male 0 - 6 Diploma
## 40 13 5 5000 Male 0 - 6 Diploma
## 41 14 5 1000 Male 0 - 6 Diploma
## 42 11 6 11000 Male 7 - 10 Diploma
## 43 12 6 47000 Male 7 - 10 Diploma
## 44 13 6 58000 Male 7 - 10 Diploma
## 45 14 6 34000 Male 7 - 10 Diploma
## 46 15 3 10000 Female 0 - 6 No Qualification
## 47 16 3 11000 Female 0 - 6 No Qualification
## 48 17 3 19000 Female 0 - 6 No Qualification
## 49 18 3 16000 Female 0 - 6 No Qualification
## 50 15 4 43000 Female 7 - 10 No Qualification
## 51 16 4 36000 Female 7 - 10 No Qualification
## 52 17 4 91000 Female 7 - 10 No Qualification
## 53 18 4 118000 Female 7 - 10 No Qualification
## 54 15 5 12000 Male 0 - 6 No Qualification
## 55 16 5 21000 Male 0 - 6 No Qualification
## 56 17 5 17000 Male 0 - 6 No Qualification
## 57 18 5 9000 Male 0 - 6 No Qualification
## 58 15 6 37000 Male 7 - 10 No Qualification
## 59 16 6 50000 Male 7 - 10 No Qualification
## 60 17 6 75000 Male 7 - 10 No Qualification
## 61 18 6 66000 Male 7 - 10 No Qualification
## 62 20 3 5000 Female 0 - 6 Postgraduate qualification
## 63 21 3 6000 Female 0 - 6 Postgraduate qualification
## 64 19 4 6000 Female 7 - 10 Postgraduate qualification
## 65 20 4 86000 Female 7 - 10 Postgraduate qualification
## 66 21 4 55000 Female 7 - 10 Postgraduate qualification
## 67 22 4 13000 Female 7 - 10 Postgraduate qualification
## 68 20 5 7000 Male 0 - 6 Postgraduate qualification
## 69 21 5 6000 Male 0 - 6 Postgraduate qualification
## 70 20 6 60000 Male 7 - 10 Postgraduate qualification
## 71 21 6 68000 Male 7 - 10 Postgraduate qualification
## 72 22 6 18000 Male 7 - 10 Postgraduate qualification
## header.header.header
## 1 15 - 24
## 2 25 - 44
## 3 45 - 64
## 4 15 - 24
## 5 25 - 44
## 6 45 - 64
## 7 65+
## 8 25 - 44
## 9 45 - 64
## 10 65+
## 11 15 - 24
## 12 25 - 44
## 13 45 - 64
## 14 65+
## 15 15 - 24
## 16 25 - 44
## 17 45 - 64
## 18 65+
## 19 15 - 24
## 20 25 - 44
## 21 45 - 64
## 22 65+
## 23 15 - 24
## 24 25 - 44
## 25 45 - 64
## 26 65+
## 27 15 - 24
## 28 25 - 44
## 29 45 - 64
## 30 65+
## 31 25 - 44
## 32 45 - 64
## 33 65+
## 34 15 - 24
## 35 25 - 44
## 36 45 - 64
## 37 65+
## 38 15 - 24
## 39 25 - 44
## 40 45 - 64
## 41 65+
## 42 15 - 24
## 43 25 - 44
## 44 45 - 64
## 45 65+
## 46 15 - 24
## 47 25 - 44
## 48 45 - 64
## 49 65+
## 50 15 - 24
## 51 25 - 44
## 52 45 - 64
## 53 65+
## 54 15 - 24
## 55 25 - 44
## 56 45 - 64
## 57 65+
## 58 15 - 24
## 59 25 - 44
## 60 45 - 64
## 61 65+
## 62 25 - 44
## 63 45 - 64
## 64 15 - 24
## 65 25 - 44
## 66 45 - 64
## 67 65+
## 68 25 - 44
## 69 45 - 64
## 70 25 - 44
## 71 45 - 64
## 72 65+row_headers <-
cells %>%
dplyr::filter(col >= 5, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$col)
row_headers## $`5`
## # A tibble: 20 x 3
## row col header
## <int> <int> <chr>
## 1 1 5 15 - 24
## 2 2 5 25 - 44
## 3 3 5 45 - 64
## 4 4 5 65+
## 5 5 5 15 - 24
## 6 6 5 25 - 44
## 7 7 5 45 - 64
## 8 8 5 65+
## 9 9 5 15 - 24
## 10 10 5 25 - 44
## 11 11 5 45 - 64
## 12 12 5 65+
## 13 13 5 15 - 24
## 14 14 5 25 - 44
## 15 15 5 45 - 64
## 16 16 5 65+
## 17 17 5 15 - 24
## 18 18 5 25 - 44
## 19 19 5 45 - 64
## 20 20 5 65+
##
## $`6`
## # A tibble: 5 x 3
## row col header
## <int> <int> <chr>
## 1 4 6 Bachelor's degree
## 2 8 6 Certificate
## 3 12 6 Diploma
## 4 16 6 No Qualification
## 5 20 6 Postgraduate qualificationcol_headers <-
cells %>%
dplyr::filter(row >= 21, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`21`
## # A tibble: 4 x 3
## row col header
## <int> <int> <chr>
## 1 21 1 0 - 6
## 2 21 2 7 - 10
## 3 21 3 0 - 6
## 4 21 4 7 - 10
##
## $`22`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 22 2 Female
## 2 22 4 Maledata_cells <-
cells %>%
dplyr::filter(row <= 20, col <= 4, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
data_cells## # A tibble: 72 x 3
## row col value
## <int> <int> <int>
## 1 1 1 7000
## 2 2 1 12000
## 3 3 1 10000
## 4 5 1 29000
## 5 6 1 34000
## 6 7 1 30000
## 7 8 1 12000
## 8 10 1 10000
## 9 11 1 6000
## 10 12 1 5000
## # … with 62 more rows## row col value header.data header.header header.data.data
## 1 1 1 7000 Female 0 - 6 Bachelor's degree
## 2 2 1 12000 Female 0 - 6 Bachelor's degree
## 3 3 1 10000 Female 0 - 6 Bachelor's degree
## 4 1 2 27000 Female 7 - 10 Bachelor's degree
## 5 2 2 137000 Female 7 - 10 Bachelor's degree
## 6 3 2 64000 Female 7 - 10 Bachelor's degree
## 7 4 2 18000 Female 7 - 10 Bachelor's degree
## 8 2 3 9000 Male 0 - 6 Bachelor's degree
## 9 3 3 7000 Male 0 - 6 Bachelor's degree
## 10 4 3 7000 Male 0 - 6 Bachelor's degree
## 11 1 4 13000 Male 7 - 10 Bachelor's degree
## 12 2 4 81000 Male 7 - 10 Bachelor's degree
## 13 3 4 66000 Male 7 - 10 Bachelor's degree
## 14 4 4 17000 Male 7 - 10 Bachelor's degree
## 15 5 1 29000 Female 0 - 6 Certificate
## 16 6 1 34000 Female 0 - 6 Certificate
## 17 7 1 30000 Female 0 - 6 Certificate
## 18 8 1 12000 Female 0 - 6 Certificate
## 19 5 2 161000 Female 7 - 10 Certificate
## 20 6 2 179000 Female 7 - 10 Certificate
## 21 7 2 210000 Female 7 - 10 Certificate
## 22 8 2 77000 Female 7 - 10 Certificate
## 23 5 3 30000 Male 0 - 6 Certificate
## 24 6 3 31000 Male 0 - 6 Certificate
## 25 7 3 23000 Male 0 - 6 Certificate
## 26 8 3 8000 Male 0 - 6 Certificate
## 27 5 4 190000 Male 7 - 10 Certificate
## 28 6 4 219000 Male 7 - 10 Certificate
## 29 7 4 199000 Male 7 - 10 Certificate
## 30 8 4 107000 Male 7 - 10 Certificate
## 31 10 1 10000 Female 0 - 6 Diploma
## 32 11 1 6000 Female 0 - 6 Diploma
## 33 12 1 5000 Female 0 - 6 Diploma
## 34 9 2 14000 Female 7 - 10 Diploma
## 35 10 2 66000 Female 7 - 10 Diploma
## 36 11 2 68000 Female 7 - 10 Diploma
## 37 12 2 41000 Female 7 - 10 Diploma
## 38 9 3 9000 Male 0 - 6 Diploma
## 39 10 3 8000 Male 0 - 6 Diploma
## 40 11 3 5000 Male 0 - 6 Diploma
## 41 12 3 1000 Male 0 - 6 Diploma
## 42 9 4 11000 Male 7 - 10 Diploma
## 43 10 4 47000 Male 7 - 10 Diploma
## 44 11 4 58000 Male 7 - 10 Diploma
## 45 12 4 34000 Male 7 - 10 Diploma
## 46 13 1 10000 Female 0 - 6 No Qualification
## 47 14 1 11000 Female 0 - 6 No Qualification
## 48 15 1 19000 Female 0 - 6 No Qualification
## 49 16 1 16000 Female 0 - 6 No Qualification
## 50 13 2 43000 Female 7 - 10 No Qualification
## 51 14 2 36000 Female 7 - 10 No Qualification
## 52 15 2 91000 Female 7 - 10 No Qualification
## 53 16 2 118000 Female 7 - 10 No Qualification
## 54 13 3 12000 Male 0 - 6 No Qualification
## 55 14 3 21000 Male 0 - 6 No Qualification
## 56 15 3 17000 Male 0 - 6 No Qualification
## 57 16 3 9000 Male 0 - 6 No Qualification
## 58 13 4 37000 Male 7 - 10 No Qualification
## 59 14 4 50000 Male 7 - 10 No Qualification
## 60 15 4 75000 Male 7 - 10 No Qualification
## 61 16 4 66000 Male 7 - 10 No Qualification
## 62 18 1 5000 Female 0 - 6 Postgraduate qualification
## 63 19 1 6000 Female 0 - 6 Postgraduate qualification
## 64 17 2 6000 Female 7 - 10 Postgraduate qualification
## 65 18 2 86000 Female 7 - 10 Postgraduate qualification
## 66 19 2 55000 Female 7 - 10 Postgraduate qualification
## 67 20 2 13000 Female 7 - 10 Postgraduate qualification
## 68 18 3 7000 Male 0 - 6 Postgraduate qualification
## 69 19 3 6000 Male 0 - 6 Postgraduate qualification
## 70 18 4 60000 Male 7 - 10 Postgraduate qualification
## 71 19 4 68000 Male 7 - 10 Postgraduate qualification
## 72 20 4 18000 Male 7 - 10 Postgraduate qualification
## header.header.header
## 1 15 - 24
## 2 25 - 44
## 3 45 - 64
## 4 15 - 24
## 5 25 - 44
## 6 45 - 64
## 7 65+
## 8 25 - 44
## 9 45 - 64
## 10 65+
## 11 15 - 24
## 12 25 - 44
## 13 45 - 64
## 14 65+
## 15 15 - 24
## 16 25 - 44
## 17 45 - 64
## 18 65+
## 19 15 - 24
## 20 25 - 44
## 21 45 - 64
## 22 65+
## 23 15 - 24
## 24 25 - 44
## 25 45 - 64
## 26 65+
## 27 15 - 24
## 28 25 - 44
## 29 45 - 64
## 30 65+
## 31 25 - 44
## 32 45 - 64
## 33 65+
## 34 15 - 24
## 35 25 - 44
## 36 45 - 64
## 37 65+
## 38 15 - 24
## 39 25 - 44
## 40 45 - 64
## 41 65+
## 42 15 - 24
## 43 25 - 44
## 44 45 - 64
## 45 65+
## 46 15 - 24
## 47 25 - 44
## 48 45 - 64
## 49 65+
## 50 15 - 24
## 51 25 - 44
## 52 45 - 64
## 53 65+
## 54 15 - 24
## 55 25 - 44
## 56 45 - 64
## 57 65+
## 58 15 - 24
## 59 25 - 44
## 60 45 - 64
## 61 65+
## 62 25 - 44
## 63 45 - 64
## 64 15 - 24
## 65 25 - 44
## 66 45 - 64
## 67 65+
## 68 25 - 44
## 69 45 - 64
## 70 25 - 44
## 71 45 - 64
## 72 65+row_headers <-
cells %>%
dplyr::filter(col >= 5, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$col)
row_headers## $`5`
## # A tibble: 20 x 3
## row col header
## <int> <int> <chr>
## 1 1 5 15 - 24
## 2 2 5 25 - 44
## 3 3 5 45 - 64
## 4 4 5 65+
## 5 5 5 15 - 24
## 6 6 5 25 - 44
## 7 7 5 45 - 64
## 8 8 5 65+
## 9 9 5 15 - 24
## 10 10 5 25 - 44
## 11 11 5 45 - 64
## 12 12 5 65+
## 13 13 5 15 - 24
## 14 14 5 25 - 44
## 15 15 5 45 - 64
## 16 16 5 65+
## 17 17 5 15 - 24
## 18 18 5 25 - 44
## 19 19 5 45 - 64
## 20 20 5 65+
##
## $`6`
## # A tibble: 5 x 3
## row col header
## <int> <int> <chr>
## 1 1 6 Bachelor's degree
## 2 5 6 Certificate
## 3 9 6 Diploma
## 4 13 6 No Qualification
## 5 17 6 Postgraduate qualificationcol_headers <-
cells %>%
dplyr::filter(row >= 21, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`21`
## # A tibble: 4 x 3
## row col header
## <int> <int> <chr>
## 1 21 1 0 - 6
## 2 21 2 7 - 10
## 3 21 3 0 - 6
## 4 21 4 7 - 10
##
## $`22`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 22 1 Female
## 2 22 3 Maledata_cells <-
cells %>%
dplyr::filter(row <= 20, col <= 4, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
data_cells## # A tibble: 72 x 3
## row col value
## <int> <int> <int>
## 1 1 1 7000
## 2 2 1 12000
## 3 3 1 10000
## 4 5 1 29000
## 5 6 1 34000
## 6 7 1 30000
## 7 8 1 12000
## 8 10 1 10000
## 9 11 1 6000
## 10 12 1 5000
## # … with 62 more rows## row col value header.data header.header header.data.data
## 1 1 1 7000 Female 0 - 6 Bachelor's degree
## 2 2 1 12000 Female 0 - 6 Bachelor's degree
## 3 3 1 10000 Female 0 - 6 Bachelor's degree
## 4 1 2 27000 Female 7 - 10 Bachelor's degree
## 5 2 2 137000 Female 7 - 10 Bachelor's degree
## 6 3 2 64000 Female 7 - 10 Bachelor's degree
## 7 4 2 18000 Female 7 - 10 Bachelor's degree
## 8 2 3 9000 Male 0 - 6 Bachelor's degree
## 9 3 3 7000 Male 0 - 6 Bachelor's degree
## 10 4 3 7000 Male 0 - 6 Bachelor's degree
## 11 1 4 13000 Male 7 - 10 Bachelor's degree
## 12 2 4 81000 Male 7 - 10 Bachelor's degree
## 13 3 4 66000 Male 7 - 10 Bachelor's degree
## 14 4 4 17000 Male 7 - 10 Bachelor's degree
## 15 5 1 29000 Female 0 - 6 Certificate
## 16 6 1 34000 Female 0 - 6 Certificate
## 17 7 1 30000 Female 0 - 6 Certificate
## 18 8 1 12000 Female 0 - 6 Certificate
## 19 5 2 161000 Female 7 - 10 Certificate
## 20 6 2 179000 Female 7 - 10 Certificate
## 21 7 2 210000 Female 7 - 10 Certificate
## 22 8 2 77000 Female 7 - 10 Certificate
## 23 5 3 30000 Male 0 - 6 Certificate
## 24 6 3 31000 Male 0 - 6 Certificate
## 25 7 3 23000 Male 0 - 6 Certificate
## 26 8 3 8000 Male 0 - 6 Certificate
## 27 5 4 190000 Male 7 - 10 Certificate
## 28 6 4 219000 Male 7 - 10 Certificate
## 29 7 4 199000 Male 7 - 10 Certificate
## 30 8 4 107000 Male 7 - 10 Certificate
## 31 10 1 10000 Female 0 - 6 Diploma
## 32 11 1 6000 Female 0 - 6 Diploma
## 33 12 1 5000 Female 0 - 6 Diploma
## 34 9 2 14000 Female 7 - 10 Diploma
## 35 10 2 66000 Female 7 - 10 Diploma
## 36 11 2 68000 Female 7 - 10 Diploma
## 37 12 2 41000 Female 7 - 10 Diploma
## 38 9 3 9000 Male 0 - 6 Diploma
## 39 10 3 8000 Male 0 - 6 Diploma
## 40 11 3 5000 Male 0 - 6 Diploma
## 41 12 3 1000 Male 0 - 6 Diploma
## 42 9 4 11000 Male 7 - 10 Diploma
## 43 10 4 47000 Male 7 - 10 Diploma
## 44 11 4 58000 Male 7 - 10 Diploma
## 45 12 4 34000 Male 7 - 10 Diploma
## 46 13 1 10000 Female 0 - 6 No Qualification
## 47 14 1 11000 Female 0 - 6 No Qualification
## 48 15 1 19000 Female 0 - 6 No Qualification
## 49 16 1 16000 Female 0 - 6 No Qualification
## 50 13 2 43000 Female 7 - 10 No Qualification
## 51 14 2 36000 Female 7 - 10 No Qualification
## 52 15 2 91000 Female 7 - 10 No Qualification
## 53 16 2 118000 Female 7 - 10 No Qualification
## 54 13 3 12000 Male 0 - 6 No Qualification
## 55 14 3 21000 Male 0 - 6 No Qualification
## 56 15 3 17000 Male 0 - 6 No Qualification
## 57 16 3 9000 Male 0 - 6 No Qualification
## 58 13 4 37000 Male 7 - 10 No Qualification
## 59 14 4 50000 Male 7 - 10 No Qualification
## 60 15 4 75000 Male 7 - 10 No Qualification
## 61 16 4 66000 Male 7 - 10 No Qualification
## 62 18 1 5000 Female 0 - 6 Postgraduate qualification
## 63 19 1 6000 Female 0 - 6 Postgraduate qualification
## 64 17 2 6000 Female 7 - 10 Postgraduate qualification
## 65 18 2 86000 Female 7 - 10 Postgraduate qualification
## 66 19 2 55000 Female 7 - 10 Postgraduate qualification
## 67 20 2 13000 Female 7 - 10 Postgraduate qualification
## 68 18 3 7000 Male 0 - 6 Postgraduate qualification
## 69 19 3 6000 Male 0 - 6 Postgraduate qualification
## 70 18 4 60000 Male 7 - 10 Postgraduate qualification
## 71 19 4 68000 Male 7 - 10 Postgraduate qualification
## 72 20 4 18000 Male 7 - 10 Postgraduate qualification
## header.header.header
## 1 15 - 24
## 2 25 - 44
## 3 45 - 64
## 4 15 - 24
## 5 25 - 44
## 6 45 - 64
## 7 65+
## 8 25 - 44
## 9 45 - 64
## 10 65+
## 11 15 - 24
## 12 25 - 44
## 13 45 - 64
## 14 65+
## 15 15 - 24
## 16 25 - 44
## 17 45 - 64
## 18 65+
## 19 15 - 24
## 20 25 - 44
## 21 45 - 64
## 22 65+
## 23 15 - 24
## 24 25 - 44
## 25 45 - 64
## 26 65+
## 27 15 - 24
## 28 25 - 44
## 29 45 - 64
## 30 65+
## 31 25 - 44
## 32 45 - 64
## 33 65+
## 34 15 - 24
## 35 25 - 44
## 36 45 - 64
## 37 65+
## 38 15 - 24
## 39 25 - 44
## 40 45 - 64
## 41 65+
## 42 15 - 24
## 43 25 - 44
## 44 45 - 64
## 45 65+
## 46 15 - 24
## 47 25 - 44
## 48 45 - 64
## 49 65+
## 50 15 - 24
## 51 25 - 44
## 52 45 - 64
## 53 65+
## 54 15 - 24
## 55 25 - 44
## 56 45 - 64
## 57 65+
## 58 15 - 24
## 59 25 - 44
## 60 45 - 64
## 61 65+
## 62 25 - 44
## 63 45 - 64
## 64 15 - 24
## 65 25 - 44
## 66 45 - 64
## 67 65+
## 68 25 - 44
## 69 45 - 64
## 70 25 - 44
## 71 45 - 64
## 72 65+## [1] TRUE## [1] TRUE## [1] TRUErow_headers <-
cells %>%
dplyr::filter(col <= 2, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$col)
row_headers## $`1`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 5 1 Female
## 2 10 1 Male
##
## $`2`
## # A tibble: 10 x 3
## row col header
## <int> <int> <chr>
## 1 3 2 Bachelor's degree
## 2 4 2 Certificate
## 3 5 2 Diploma
## 4 6 2 No Qualification
## 5 7 2 Postgraduate qualification
## 6 8 2 Bachelor's degree
## 7 9 2 Certificate
## 8 10 2 Diploma
## 9 11 2 No Qualification
## 10 12 2 Postgraduate qualificationcol_headers <-
cells %>%
dplyr::filter(row <= 2, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`1`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 1 4 0 - 6
## 2 1 7 7 - 10
##
## $`2`
## # A tibble: 6 x 3
## row col header
## <int> <int> <chr>
## 1 2 3 15 - 24
## 2 2 4 25 - 44
## 3 2 5 45 - 64
## 4 2 6 15 - 24
## 5 2 7 25 - 44
## 6 2 8 45 - 64data_cells <-
cells %>%
dplyr::filter(row >= 3, col >= 3, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
data_cells## # A tibble: 55 x 3
## row col value
## <int> <int> <int>
## 1 3 3 7000
## 2 4 3 29000
## 3 6 3 10000
## 4 9 3 30000
## 5 10 3 9000
## 6 11 3 12000
## 7 3 4 12000
## 8 4 4 34000
## 9 5 4 10000
## 10 6 4 11000
## # … with 45 more rows## row col value header.data header.header header.data.data
## 1 3 3 7000 0 - 6 15 - 24 Female
## 2 4 3 29000 0 - 6 15 - 24 Female
## 3 6 3 10000 0 - 6 15 - 24 Female
## 4 3 4 12000 0 - 6 25 - 44 Female
## 5 4 4 34000 0 - 6 25 - 44 Female
## 6 5 4 10000 0 - 6 25 - 44 Female
## 7 6 4 11000 0 - 6 25 - 44 Female
## 8 7 4 5000 0 - 6 25 - 44 Female
## 9 3 5 10000 0 - 6 45 - 64 Female
## 10 4 5 30000 0 - 6 45 - 64 Female
## 11 5 5 6000 0 - 6 45 - 64 Female
## 12 6 5 19000 0 - 6 45 - 64 Female
## 13 7 5 6000 0 - 6 45 - 64 Female
## 14 3 6 27000 7 - 10 15 - 24 Female
## 15 4 6 161000 7 - 10 15 - 24 Female
## 16 5 6 14000 7 - 10 15 - 24 Female
## 17 6 6 43000 7 - 10 15 - 24 Female
## 18 7 6 6000 7 - 10 15 - 24 Female
## 19 3 7 137000 7 - 10 25 - 44 Female
## 20 4 7 179000 7 - 10 25 - 44 Female
## 21 5 7 66000 7 - 10 25 - 44 Female
## 22 6 7 36000 7 - 10 25 - 44 Female
## 23 7 7 86000 7 - 10 25 - 44 Female
## 24 3 8 64000 7 - 10 45 - 64 Female
## 25 4 8 210000 7 - 10 45 - 64 Female
## 26 5 8 68000 7 - 10 45 - 64 Female
## 27 6 8 91000 7 - 10 45 - 64 Female
## 28 7 8 55000 7 - 10 45 - 64 Female
## 29 9 3 30000 0 - 6 15 - 24 Male
## 30 10 3 9000 0 - 6 15 - 24 Male
## 31 11 3 12000 0 - 6 15 - 24 Male
## 32 8 4 9000 0 - 6 25 - 44 Male
## 33 9 4 31000 0 - 6 25 - 44 Male
## 34 10 4 8000 0 - 6 25 - 44 Male
## 35 11 4 21000 0 - 6 25 - 44 Male
## 36 12 4 7000 0 - 6 25 - 44 Male
## 37 8 5 7000 0 - 6 45 - 64 Male
## 38 9 5 23000 0 - 6 45 - 64 Male
## 39 10 5 5000 0 - 6 45 - 64 Male
## 40 11 5 17000 0 - 6 45 - 64 Male
## 41 12 5 6000 0 - 6 45 - 64 Male
## 42 8 6 13000 7 - 10 15 - 24 Male
## 43 9 6 190000 7 - 10 15 - 24 Male
## 44 10 6 11000 7 - 10 15 - 24 Male
## 45 11 6 37000 7 - 10 15 - 24 Male
## 46 8 7 81000 7 - 10 25 - 44 Male
## 47 9 7 219000 7 - 10 25 - 44 Male
## 48 10 7 47000 7 - 10 25 - 44 Male
## 49 11 7 50000 7 - 10 25 - 44 Male
## 50 12 7 60000 7 - 10 25 - 44 Male
## 51 8 8 66000 7 - 10 45 - 64 Male
## 52 9 8 199000 7 - 10 45 - 64 Male
## 53 10 8 58000 7 - 10 45 - 64 Male
## 54 11 8 75000 7 - 10 45 - 64 Male
## 55 12 8 68000 7 - 10 45 - 64 Male
## header.header.header
## 1 Bachelor's degree
## 2 Certificate
## 3 No Qualification
## 4 Bachelor's degree
## 5 Certificate
## 6 Diploma
## 7 No Qualification
## 8 Postgraduate qualification
## 9 Bachelor's degree
## 10 Certificate
## 11 Diploma
## 12 No Qualification
## 13 Postgraduate qualification
## 14 Bachelor's degree
## 15 Certificate
## 16 Diploma
## 17 No Qualification
## 18 Postgraduate qualification
## 19 Bachelor's degree
## 20 Certificate
## 21 Diploma
## 22 No Qualification
## 23 Postgraduate qualification
## 24 Bachelor's degree
## 25 Certificate
## 26 Diploma
## 27 No Qualification
## 28 Postgraduate qualification
## 29 Certificate
## 30 Diploma
## 31 No Qualification
## 32 Bachelor's degree
## 33 Certificate
## 34 Diploma
## 35 No Qualification
## 36 Postgraduate qualification
## 37 Bachelor's degree
## 38 Certificate
## 39 Diploma
## 40 No Qualification
## 41 Postgraduate qualification
## 42 Bachelor's degree
## 43 Certificate
## 44 Diploma
## 45 No Qualification
## 46 Bachelor's degree
## 47 Certificate
## 48 Diploma
## 49 No Qualification
## 50 Postgraduate qualification
## 51 Bachelor's degree
## 52 Certificate
## 53 Diploma
## 54 No Qualification
## 55 Postgraduate qualificationrow_headers <-
cells %>%
dplyr::filter(col >= 7, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$col)
row_headers## $`7`
## # A tibble: 10 x 3
## row col header
## <int> <int> <chr>
## 1 1 7 Bachelor's degree
## 2 2 7 Certificate
## 3 3 7 Diploma
## 4 4 7 No Qualification
## 5 5 7 Postgraduate qualification
## 6 6 7 Bachelor's degree
## 7 7 7 Certificate
## 8 8 7 Diploma
## 9 9 7 No Qualification
## 10 10 7 Postgraduate qualification
##
## $`8`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 3 8 Female
## 2 8 8 Malecol_headers <-
cells %>%
dplyr::filter(row >= 11, !is.na(chr)) %>%
select(row, col, header = chr) %>%
split(.$row)
col_headers## $`11`
## # A tibble: 6 x 3
## row col header
## <int> <int> <chr>
## 1 11 1 15 - 24
## 2 11 2 25 - 44
## 3 11 3 45 - 64
## 4 11 4 15 - 24
## 5 11 5 25 - 44
## 6 11 6 45 - 64
##
## $`12`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 12 2 0 - 6
## 2 12 5 7 - 10data_cells <-
cells %>%
dplyr::filter(row <= 10, col <= 6, !is.na(chr)) %>%
mutate(value = as.integer(chr)) %>%
select(row, col, value)
data_cells## # A tibble: 55 x 3
## row col value
## <int> <int> <int>
## 1 1 1 7000
## 2 2 1 29000
## 3 4 1 10000
## 4 7 1 30000
## 5 8 1 9000
## 6 9 1 12000
## 7 1 2 12000
## 8 2 2 34000
## 9 3 2 10000
## 10 4 2 11000
## # … with 45 more rows## row col value header.data header.header header.data.data
## 1 1 1 7000 0 - 6 15 - 24 Female
## 2 2 1 29000 0 - 6 15 - 24 Female
## 3 4 1 10000 0 - 6 15 - 24 Female
## 4 1 2 12000 0 - 6 25 - 44 Female
## 5 2 2 34000 0 - 6 25 - 44 Female
## 6 3 2 10000 0 - 6 25 - 44 Female
## 7 4 2 11000 0 - 6 25 - 44 Female
## 8 5 2 5000 0 - 6 25 - 44 Female
## 9 1 3 10000 0 - 6 45 - 64 Female
## 10 2 3 30000 0 - 6 45 - 64 Female
## 11 3 3 6000 0 - 6 45 - 64 Female
## 12 4 3 19000 0 - 6 45 - 64 Female
## 13 5 3 6000 0 - 6 45 - 64 Female
## 14 1 4 27000 7 - 10 15 - 24 Female
## 15 2 4 161000 7 - 10 15 - 24 Female
## 16 3 4 14000 7 - 10 15 - 24 Female
## 17 4 4 43000 7 - 10 15 - 24 Female
## 18 5 4 6000 7 - 10 15 - 24 Female
## 19 1 5 137000 7 - 10 25 - 44 Female
## 20 2 5 179000 7 - 10 25 - 44 Female
## 21 3 5 66000 7 - 10 25 - 44 Female
## 22 4 5 36000 7 - 10 25 - 44 Female
## 23 5 5 86000 7 - 10 25 - 44 Female
## 24 1 6 64000 7 - 10 45 - 64 Female
## 25 2 6 210000 7 - 10 45 - 64 Female
## 26 3 6 68000 7 - 10 45 - 64 Female
## 27 4 6 91000 7 - 10 45 - 64 Female
## 28 5 6 55000 7 - 10 45 - 64 Female
## 29 7 1 30000 0 - 6 15 - 24 Male
## 30 8 1 9000 0 - 6 15 - 24 Male
## 31 9 1 12000 0 - 6 15 - 24 Male
## 32 6 2 9000 0 - 6 25 - 44 Male
## 33 7 2 31000 0 - 6 25 - 44 Male
## 34 8 2 8000 0 - 6 25 - 44 Male
## 35 9 2 21000 0 - 6 25 - 44 Male
## 36 10 2 7000 0 - 6 25 - 44 Male
## 37 6 3 7000 0 - 6 45 - 64 Male
## 38 7 3 23000 0 - 6 45 - 64 Male
## 39 8 3 5000 0 - 6 45 - 64 Male
## 40 9 3 17000 0 - 6 45 - 64 Male
## 41 10 3 6000 0 - 6 45 - 64 Male
## 42 6 4 13000 7 - 10 15 - 24 Male
## 43 7 4 190000 7 - 10 15 - 24 Male
## 44 8 4 11000 7 - 10 15 - 24 Male
## 45 9 4 37000 7 - 10 15 - 24 Male
## 46 6 5 81000 7 - 10 25 - 44 Male
## 47 7 5 219000 7 - 10 25 - 44 Male
## 48 8 5 47000 7 - 10 25 - 44 Male
## 49 9 5 50000 7 - 10 25 - 44 Male
## 50 10 5 60000 7 - 10 25 - 44 Male
## 51 6 6 66000 7 - 10 45 - 64 Male
## 52 7 6 199000 7 - 10 45 - 64 Male
## 53 8 6 58000 7 - 10 45 - 64 Male
## 54 9 6 75000 7 - 10 45 - 64 Male
## 55 10 6 68000 7 - 10 45 - 64 Male
## header.header.header
## 1 Bachelor's degree
## 2 Certificate
## 3 No Qualification
## 4 Bachelor's degree
## 5 Certificate
## 6 Diploma
## 7 No Qualification
## 8 Postgraduate qualification
## 9 Bachelor's degree
## 10 Certificate
## 11 Diploma
## 12 No Qualification
## 13 Postgraduate qualification
## 14 Bachelor's degree
## 15 Certificate
## 16 Diploma
## 17 No Qualification
## 18 Postgraduate qualification
## 19 Bachelor's degree
## 20 Certificate
## 21 Diploma
## 22 No Qualification
## 23 Postgraduate qualification
## 24 Bachelor's degree
## 25 Certificate
## 26 Diploma
## 27 No Qualification
## 28 Postgraduate qualification
## 29 Certificate
## 30 Diploma
## 31 No Qualification
## 32 Bachelor's degree
## 33 Certificate
## 34 Diploma
## 35 No Qualification
## 36 Postgraduate qualification
## 37 Bachelor's degree
## 38 Certificate
## 39 Diploma
## 40 No Qualification
## 41 Postgraduate qualification
## 42 Bachelor's degree
## 43 Certificate
## 44 Diploma
## 45 No Qualification
## 46 Bachelor's degree
## 47 Certificate
## 48 Diploma
## 49 No Qualification
## 50 Postgraduate qualification
## 51 Bachelor's degree
## 52 Certificate
## 53 Diploma
## 54 No Qualification
## 55 Postgraduate qualification## [1] TRUEspreadsheet <- system.file("extdata/purpose.xlsx", package = "unpivotr")
cells <- tidy_xlsx(spreadsheet, "ABOVE LEFT border")$data[[1]]## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.# Same as ABOVE LEFT without borders
row_headers <-
cells %>%
dplyr::filter(col <= 3, !is_blank) %>%
select(row, col, header = character) %>%
split(.$col)
row_headers## $`2`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 5 2 Female
## 2 10 2 Male
##
## $`3`
## # A tibble: 10 x 3
## row col header
## <int> <int> <chr>
## 1 4 3 Bachelor's degree
## 2 5 3 Certificate
## 3 6 3 Diploma
## 4 7 3 No Qualification
## 5 8 3 Postgraduate qualification
## 6 9 3 Bachelor's degree
## 7 10 3 Certificate
## 8 11 3 Diploma
## 9 12 3 No Qualification
## 10 13 3 Postgraduate qualificationcol_headers <-
cells %>%
dplyr::filter(row <= 3, !is_blank) %>%
select(row, col, header = character) %>%
split(.$row)
col_headers## $`2`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 2 6 0 - 6
## 2 2 8 7 - 10
##
## $`3`
## # A tibble: 6 x 3
## row col header
## <int> <int> <chr>
## 1 3 4 15 - 24
## 2 3 5 25 - 44
## 3 3 6 45 - 64
## 4 3 7 15 - 24
## 5 3 8 25 - 44
## 6 3 9 45 - 64# Same as ABOVE LEFT without borders
data_cells <-
cells %>%
dplyr::filter(row >= 4, col >= 4, !is_blank) %>%
mutate(content = ifelse(is.na(character), numeric, NA)) %>%
mutate(value = as.integer(content)) %>%
select(row, col, value)
data_cells## # A tibble: 55 x 3
## row col value
## <int> <int> <int>
## 1 4 4 7000
## 2 4 5 12000
## 3 4 6 10000
## 4 4 7 27000
## 5 4 8 137000
## 6 4 9 64000
## 7 5 4 29000
## 8 5 5 34000
## 9 5 6 30000
## 10 5 7 161000
## # … with 45 more rows# Different from ABOVE LEFT without borders
# Find cells with borders on the bottom, and ones with borders on the left
formatting <- tidy_xlsx(spreadsheet)$formats## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.left_borders <- which(!is.na(formatting$local$border$left$style))
top_borders <- which(!is.na(formatting$local$border$top$style))
left_border_cells <-
cells %>%
dplyr::filter(row == 2, local_format_id %in% left_borders) %>%
select(row, col)
top_border_cells <-
cells %>%
dplyr::filter(col == 2, local_format_id %in% top_borders) %>%
select(row, col)data_cells <-
data_cells %>%
enhead(col_headers[[1]], "ABOVE", left_border_cells) %>% # Different from ABOVE LEFT
enhead(col_headers[[2]], "N") %>% # Same as ABOVE LEFT
enhead(row_headers[[1]], "LEFT", top_border_cells) %>% # Different from ABOVE LEFT
enhead(row_headers[[2]], "W") # Same as ABOVE LEFT## row col value header.data header.header header.data.data
## 1 4 4 7000 0 - 6 15 - 24 Female
## 2 4 5 12000 0 - 6 25 - 44 Female
## 3 4 6 10000 0 - 6 45 - 64 Female
## 4 4 7 27000 0 - 6 15 - 24 Female
## 5 5 4 29000 0 - 6 15 - 24 Female
## 6 5 5 34000 0 - 6 25 - 44 Female
## 7 5 6 30000 0 - 6 45 - 64 Female
## 8 5 7 161000 0 - 6 15 - 24 Female
## 9 6 5 10000 0 - 6 25 - 44 Female
## 10 6 6 6000 0 - 6 45 - 64 Female
## 11 6 7 14000 0 - 6 15 - 24 Female
## 12 7 4 10000 0 - 6 15 - 24 Female
## 13 7 5 11000 0 - 6 25 - 44 Female
## 14 7 6 19000 0 - 6 45 - 64 Female
## 15 7 7 43000 0 - 6 15 - 24 Female
## 16 4 8 137000 7 - 10 25 - 44 Female
## 17 4 9 64000 7 - 10 45 - 64 Female
## 18 5 8 179000 7 - 10 25 - 44 Female
## 19 5 9 210000 7 - 10 45 - 64 Female
## 20 6 8 66000 7 - 10 25 - 44 Female
## 21 6 9 68000 7 - 10 45 - 64 Female
## 22 7 8 36000 7 - 10 25 - 44 Female
## 23 7 9 91000 7 - 10 45 - 64 Female
## 24 8 5 5000 0 - 6 25 - 44 Male
## 25 8 6 6000 0 - 6 45 - 64 Male
## 26 8 7 6000 0 - 6 15 - 24 Male
## 27 9 5 9000 0 - 6 25 - 44 Male
## 28 9 6 7000 0 - 6 45 - 64 Male
## 29 9 7 13000 0 - 6 15 - 24 Male
## 30 10 4 30000 0 - 6 15 - 24 Male
## 31 10 5 31000 0 - 6 25 - 44 Male
## 32 10 6 23000 0 - 6 45 - 64 Male
## 33 10 7 190000 0 - 6 15 - 24 Male
## 34 11 4 9000 0 - 6 15 - 24 Male
## 35 11 5 8000 0 - 6 25 - 44 Male
## 36 11 6 5000 0 - 6 45 - 64 Male
## 37 11 7 11000 0 - 6 15 - 24 Male
## 38 12 4 12000 0 - 6 15 - 24 Male
## 39 12 5 21000 0 - 6 25 - 44 Male
## 40 12 6 17000 0 - 6 45 - 64 Male
## 41 12 7 37000 0 - 6 15 - 24 Male
## 42 13 5 7000 0 - 6 25 - 44 Male
## 43 13 6 6000 0 - 6 45 - 64 Male
## 44 8 8 86000 7 - 10 25 - 44 Male
## 45 8 9 55000 7 - 10 45 - 64 Male
## 46 9 8 81000 7 - 10 25 - 44 Male
## 47 9 9 66000 7 - 10 45 - 64 Male
## 48 10 8 219000 7 - 10 25 - 44 Male
## 49 10 9 199000 7 - 10 45 - 64 Male
## 50 11 8 47000 7 - 10 25 - 44 Male
## 51 11 9 58000 7 - 10 45 - 64 Male
## 52 12 8 50000 7 - 10 25 - 44 Male
## 53 12 9 75000 7 - 10 45 - 64 Male
## 54 13 8 60000 7 - 10 25 - 44 Male
## 55 13 9 68000 7 - 10 45 - 64 Male
## header.header.header
## 1 Bachelor's degree
## 2 Bachelor's degree
## 3 Bachelor's degree
## 4 Bachelor's degree
## 5 Certificate
## 6 Certificate
## 7 Certificate
## 8 Certificate
## 9 Diploma
## 10 Diploma
## 11 Diploma
## 12 No Qualification
## 13 No Qualification
## 14 No Qualification
## 15 No Qualification
## 16 Bachelor's degree
## 17 Bachelor's degree
## 18 Certificate
## 19 Certificate
## 20 Diploma
## 21 Diploma
## 22 No Qualification
## 23 No Qualification
## 24 Postgraduate qualification
## 25 Postgraduate qualification
## 26 Postgraduate qualification
## 27 Bachelor's degree
## 28 Bachelor's degree
## 29 Bachelor's degree
## 30 Certificate
## 31 Certificate
## 32 Certificate
## 33 Certificate
## 34 Diploma
## 35 Diploma
## 36 Diploma
## 37 Diploma
## 38 No Qualification
## 39 No Qualification
## 40 No Qualification
## 41 No Qualification
## 42 Postgraduate qualification
## 43 Postgraduate qualification
## 44 Postgraduate qualification
## 45 Postgraduate qualification
## 46 Bachelor's degree
## 47 Bachelor's degree
## 48 Certificate
## 49 Certificate
## 50 Diploma
## 51 Diploma
## 52 No Qualification
## 53 No Qualification
## 54 Postgraduate qualification
## 55 Postgraduate qualification## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.# Same as BELOW RIGHT without borders
row_headers <-
cells %>%
dplyr::filter(col >= 10, !is_blank) %>%
select(row, col, header = character) %>%
split(.$col)
row_headers## $`10`
## # A tibble: 10 x 3
## row col header
## <int> <int> <chr>
## 1 4 10 Bachelor's degree
## 2 5 10 Certificate
## 3 6 10 Diploma
## 4 7 10 No Qualification
## 5 8 10 Postgraduate qualification
## 6 9 10 Bachelor's degree
## 7 10 10 Certificate
## 8 11 10 Diploma
## 9 12 10 No Qualification
## 10 13 10 Postgraduate qualification
##
## $`11`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 7 11 Female
## 2 10 11 Malecol_headers <-
cells %>%
dplyr::filter(row >= 14, !is_blank) %>%
select(row, col, header = character) %>%
split(.$row)
col_headers## $`14`
## # A tibble: 6 x 3
## row col header
## <int> <int> <chr>
## 1 14 4 15 - 24
## 2 14 5 25 - 44
## 3 14 6 45 - 64
## 4 14 7 15 - 24
## 5 14 8 25 - 44
## 6 14 9 45 - 64
##
## $`15`
## # A tibble: 2 x 3
## row col header
## <int> <int> <chr>
## 1 15 6 0 - 6
## 2 15 7 7 - 10# Same as BELOW RIGHT without borders
data_cells <-
cells %>%
dplyr::filter(row <= 13, col <= 9, !is_blank) %>%
mutate(content = ifelse(is.na(character), numeric, NA)) %>%
mutate(value = as.integer(content)) %>%
select(row, col, value)
data_cells## # A tibble: 55 x 3
## row col value
## <int> <int> <int>
## 1 4 4 7000
## 2 4 5 12000
## 3 4 6 10000
## 4 4 7 27000
## 5 4 8 137000
## 6 4 9 64000
## 7 5 4 29000
## 8 5 5 34000
## 9 5 6 30000
## 10 5 7 161000
## # … with 45 more rows# Different from BELOW RIGHT without borders
# Find cells with borders on the bottom, and ones with borders on the left
formatting <- tidy_xlsx(spreadsheet)$formats## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.left_borders <- which(!is.na(formatting$local$border$left$style))
top_borders <- which(!is.na(formatting$local$border$top$style))
left_border_cells <-
cells %>%
dplyr::filter(row == 15, local_format_id %in% left_borders) %>%
select(row, col)
top_border_cells <-
cells %>%
dplyr::filter(col == 11, local_format_id %in% top_borders) %>%
select(row, col)data_cells <-
data_cells %>%
enhead(col_headers[[2]], "BELOW", left_border_cells) %>% # Different from BELOW RIGHT
enhead(col_headers[[1]], "S") %>% # Same as BELOW RIGHT
enhead(row_headers[[2]], "RIGHT", top_border_cells) %>% # Different from BELOW RIGHT
enhead(row_headers[[1]], "E") # Same as BELOW RIGHT## row col value header.data header.header header.data.data
## 1 4 4 7000 0 - 6 15 - 24 Female
## 2 4 5 12000 0 - 6 25 - 44 Female
## 3 4 6 10000 0 - 6 45 - 64 Female
## 4 5 4 29000 0 - 6 15 - 24 Female
## 5 5 5 34000 0 - 6 25 - 44 Female
## 6 5 6 30000 0 - 6 45 - 64 Female
## 7 6 5 10000 0 - 6 25 - 44 Female
## 8 6 6 6000 0 - 6 45 - 64 Female
## 9 7 4 10000 0 - 6 15 - 24 Female
## 10 7 5 11000 0 - 6 25 - 44 Female
## 11 7 6 19000 0 - 6 45 - 64 Female
## 12 8 5 5000 0 - 6 25 - 44 Female
## 13 8 6 6000 0 - 6 45 - 64 Female
## 14 4 7 27000 7 - 10 15 - 24 Female
## 15 4 8 137000 7 - 10 25 - 44 Female
## 16 4 9 64000 7 - 10 45 - 64 Female
## 17 5 7 161000 7 - 10 15 - 24 Female
## 18 5 8 179000 7 - 10 25 - 44 Female
## 19 5 9 210000 7 - 10 45 - 64 Female
## 20 6 7 14000 7 - 10 15 - 24 Female
## 21 6 8 66000 7 - 10 25 - 44 Female
## 22 6 9 68000 7 - 10 45 - 64 Female
## 23 7 7 43000 7 - 10 15 - 24 Female
## 24 7 8 36000 7 - 10 25 - 44 Female
## 25 7 9 91000 7 - 10 45 - 64 Female
## 26 8 7 6000 7 - 10 15 - 24 Female
## 27 8 8 86000 7 - 10 25 - 44 Female
## 28 8 9 55000 7 - 10 45 - 64 Female
## 29 9 5 9000 0 - 6 25 - 44 Male
## 30 9 6 7000 0 - 6 45 - 64 Male
## 31 10 4 30000 0 - 6 15 - 24 Male
## 32 10 5 31000 0 - 6 25 - 44 Male
## 33 10 6 23000 0 - 6 45 - 64 Male
## 34 11 4 9000 0 - 6 15 - 24 Male
## 35 11 5 8000 0 - 6 25 - 44 Male
## 36 11 6 5000 0 - 6 45 - 64 Male
## 37 12 4 12000 0 - 6 15 - 24 Male
## 38 12 5 21000 0 - 6 25 - 44 Male
## 39 12 6 17000 0 - 6 45 - 64 Male
## 40 13 5 7000 0 - 6 25 - 44 Male
## 41 13 6 6000 0 - 6 45 - 64 Male
## 42 9 7 13000 7 - 10 15 - 24 Male
## 43 9 8 81000 7 - 10 25 - 44 Male
## 44 9 9 66000 7 - 10 45 - 64 Male
## 45 10 7 190000 7 - 10 15 - 24 Male
## 46 10 8 219000 7 - 10 25 - 44 Male
## 47 10 9 199000 7 - 10 45 - 64 Male
## 48 11 7 11000 7 - 10 15 - 24 Male
## 49 11 8 47000 7 - 10 25 - 44 Male
## 50 11 9 58000 7 - 10 45 - 64 Male
## 51 12 7 37000 7 - 10 15 - 24 Male
## 52 12 8 50000 7 - 10 25 - 44 Male
## 53 12 9 75000 7 - 10 45 - 64 Male
## 54 13 8 60000 7 - 10 25 - 44 Male
## 55 13 9 68000 7 - 10 45 - 64 Male
## header.header.header
## 1 Bachelor's degree
## 2 Bachelor's degree
## 3 Bachelor's degree
## 4 Certificate
## 5 Certificate
## 6 Certificate
## 7 Diploma
## 8 Diploma
## 9 No Qualification
## 10 No Qualification
## 11 No Qualification
## 12 Postgraduate qualification
## 13 Postgraduate qualification
## 14 Bachelor's degree
## 15 Bachelor's degree
## 16 Bachelor's degree
## 17 Certificate
## 18 Certificate
## 19 Certificate
## 20 Diploma
## 21 Diploma
## 22 Diploma
## 23 No Qualification
## 24 No Qualification
## 25 No Qualification
## 26 Postgraduate qualification
## 27 Postgraduate qualification
## 28 Postgraduate qualification
## 29 Bachelor's degree
## 30 Bachelor's degree
## 31 Certificate
## 32 Certificate
## 33 Certificate
## 34 Diploma
## 35 Diploma
## 36 Diploma
## 37 No Qualification
## 38 No Qualification
## 39 No Qualification
## 40 Postgraduate qualification
## 41 Postgraduate qualification
## 42 Bachelor's degree
## 43 Bachelor's degree
## 44 Bachelor's degree
## 45 Certificate
## 46 Certificate
## 47 Certificate
## 48 Diploma
## 49 Diploma
## 50 Diploma
## 51 No Qualification
## 52 No Qualification
## 53 No Qualification
## 54 Postgraduate qualification
## 55 Postgraduate qualification## [1] TRUE## [1] FALSE## [1] TRUE