Compass Directions

Duncan Garmonsway

2018-01-20

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").

library(dplyr)
## 
## 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
library(tidyxl)
library(unpivotr)
## 
## Attaching package: 'unpivotr'
## The following object is masked from 'package:stats':
## 
##     offset

NNW WNW example

(original <- purpose$`NNW WNW`)
##                            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
tail(cells <- tidy_table(original))
## # A tibble: 6 x 3
##     row   col chr  
##   <int> <int> <chr>
## 1    17     6 75000
## 2    18     6 66000
## 3    19     6 <NA> 
## 4    20     6 60000
## 5    21     6 68000
## 6    22     6 18000

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+
## $`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 - 10

Data

## # 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

Join headers to data

Result

##    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    7   3  29000      Female         0 - 6                Certificate
## 5    8   3  34000      Female         0 - 6                Certificate
## 6    9   3  30000      Female         0 - 6                Certificate
## 7   10   3  12000      Female         0 - 6                Certificate
## 8   12   3  10000      Female         0 - 6                    Diploma
## 9   13   3   6000      Female         0 - 6                    Diploma
## 10  14   3   5000      Female         0 - 6                    Diploma
## 11  15   3  10000      Female         0 - 6           No Qualification
## 12  16   3  11000      Female         0 - 6           No Qualification
## 13  17   3  19000      Female         0 - 6           No Qualification
## 14  18   3  16000      Female         0 - 6           No Qualification
## 15  20   3   5000      Female         0 - 6 Postgraduate qualification
## 16  21   3   6000      Female         0 - 6 Postgraduate qualification
## 17   3   4  27000      Female        7 - 10          Bachelor's degree
## 18   4   4 137000      Female        7 - 10          Bachelor's degree
## 19   5   4  64000      Female        7 - 10          Bachelor's degree
## 20   6   4  18000      Female        7 - 10          Bachelor's degree
## 21   7   4 161000      Female        7 - 10                Certificate
## 22   8   4 179000      Female        7 - 10                Certificate
## 23   9   4 210000      Female        7 - 10                Certificate
## 24  10   4  77000      Female        7 - 10                Certificate
## 25  11   4  14000      Female        7 - 10                    Diploma
## 26  12   4  66000      Female        7 - 10                    Diploma
## 27  13   4  68000      Female        7 - 10                    Diploma
## 28  14   4  41000      Female        7 - 10                    Diploma
## 29  15   4  43000      Female        7 - 10           No Qualification
## 30  16   4  36000      Female        7 - 10           No Qualification
## 31  17   4  91000      Female        7 - 10           No Qualification
## 32  18   4 118000      Female        7 - 10           No Qualification
## 33  19   4   6000      Female        7 - 10 Postgraduate qualification
## 34  20   4  86000      Female        7 - 10 Postgraduate qualification
## 35  21   4  55000      Female        7 - 10 Postgraduate qualification
## 36  22   4  13000      Female        7 - 10 Postgraduate qualification
## 37   4   5   9000        Male         0 - 6          Bachelor's degree
## 38   5   5   7000        Male         0 - 6          Bachelor's degree
## 39   6   5   7000        Male         0 - 6          Bachelor's degree
## 40   7   5  30000        Male         0 - 6                Certificate
## 41   8   5  31000        Male         0 - 6                Certificate
## 42   9   5  23000        Male         0 - 6                Certificate
## 43  10   5   8000        Male         0 - 6                Certificate
## 44  11   5   9000        Male         0 - 6                    Diploma
## 45  12   5   8000        Male         0 - 6                    Diploma
## 46  13   5   5000        Male         0 - 6                    Diploma
## 47  14   5   1000        Male         0 - 6                    Diploma
## 48  15   5  12000        Male         0 - 6           No Qualification
## 49  16   5  21000        Male         0 - 6           No Qualification
## 50  17   5  17000        Male         0 - 6           No Qualification
## 51  18   5   9000        Male         0 - 6           No Qualification
## 52  20   5   7000        Male         0 - 6 Postgraduate qualification
## 53  21   5   6000        Male         0 - 6 Postgraduate qualification
## 54   3   6  13000        Male        7 - 10          Bachelor's degree
## 55   4   6  81000        Male        7 - 10          Bachelor's degree
## 56   5   6  66000        Male        7 - 10          Bachelor's degree
## 57   6   6  17000        Male        7 - 10          Bachelor's degree
## 58   7   6 190000        Male        7 - 10                Certificate
## 59   8   6 219000        Male        7 - 10                Certificate
## 60   9   6 199000        Male        7 - 10                Certificate
## 61  10   6 107000        Male        7 - 10                Certificate
## 62  11   6  11000        Male        7 - 10                    Diploma
## 63  12   6  47000        Male        7 - 10                    Diploma
## 64  13   6  58000        Male        7 - 10                    Diploma
## 65  14   6  34000        Male        7 - 10                    Diploma
## 66  15   6  37000        Male        7 - 10           No Qualification
## 67  16   6  50000        Male        7 - 10           No Qualification
## 68  17   6  75000        Male        7 - 10           No Qualification
## 69  18   6  66000        Male        7 - 10           No 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              25 - 44
## 16              45 - 64
## 17              15 - 24
## 18              25 - 44
## 19              45 - 64
## 20                  65+
## 21              15 - 24
## 22              25 - 44
## 23              45 - 64
## 24                  65+
## 25              15 - 24
## 26              25 - 44
## 27              45 - 64
## 28                  65+
## 29              15 - 24
## 30              25 - 44
## 31              45 - 64
## 32                  65+
## 33              15 - 24
## 34              25 - 44
## 35              45 - 64
## 36                  65+
## 37              25 - 44
## 38              45 - 64
## 39                  65+
## 40              15 - 24
## 41              25 - 44
## 42              45 - 64
## 43                  65+
## 44              15 - 24
## 45              25 - 44
## 46              45 - 64
## 47                  65+
## 48              15 - 24
## 49              25 - 44
## 50              45 - 64
## 51                  65+
## 52              25 - 44
## 53              45 - 64
## 54              15 - 24
## 55              25 - 44
## 56              45 - 64
## 57                  65+
## 58              15 - 24
## 59              25 - 44
## 60              45 - 64
## 61                  65+
## 62              15 - 24
## 63              25 - 44
## 64              45 - 64
## 65                  65+
## 66              15 - 24
## 67              25 - 44
## 68              45 - 64
## 69                  65+
## 70              25 - 44
## 71              45 - 64
## 72                  65+

NNE WSW example

cells <- tidy_table(purpose$`NNE WSW`)

Headers (same as NNW WNW)

## $`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+
## $`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 - 10

Data (same as NNW WNW)

## # 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

Join headers to data (different from NNW WNW)

Result

##    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    7   3  29000      Female         0 - 6                Certificate
## 5    8   3  34000      Female         0 - 6                Certificate
## 6    9   3  30000      Female         0 - 6                Certificate
## 7   10   3  12000      Female         0 - 6                Certificate
## 8   12   3  10000      Female         0 - 6                    Diploma
## 9   13   3   6000      Female         0 - 6                    Diploma
## 10  14   3   5000      Female         0 - 6                    Diploma
## 11  15   3  10000      Female         0 - 6           No Qualification
## 12  16   3  11000      Female         0 - 6           No Qualification
## 13  17   3  19000      Female         0 - 6           No Qualification
## 14  18   3  16000      Female         0 - 6           No Qualification
## 15  20   3   5000      Female         0 - 6 Postgraduate qualification
## 16  21   3   6000      Female         0 - 6 Postgraduate qualification
## 17   3   4  27000      Female        7 - 10          Bachelor's degree
## 18   4   4 137000      Female        7 - 10          Bachelor's degree
## 19   5   4  64000      Female        7 - 10          Bachelor's degree
## 20   6   4  18000      Female        7 - 10          Bachelor's degree
## 21   7   4 161000      Female        7 - 10                Certificate
## 22   8   4 179000      Female        7 - 10                Certificate
## 23   9   4 210000      Female        7 - 10                Certificate
## 24  10   4  77000      Female        7 - 10                Certificate
## 25  11   4  14000      Female        7 - 10                    Diploma
## 26  12   4  66000      Female        7 - 10                    Diploma
## 27  13   4  68000      Female        7 - 10                    Diploma
## 28  14   4  41000      Female        7 - 10                    Diploma
## 29  15   4  43000      Female        7 - 10           No Qualification
## 30  16   4  36000      Female        7 - 10           No Qualification
## 31  17   4  91000      Female        7 - 10           No Qualification
## 32  18   4 118000      Female        7 - 10           No Qualification
## 33  19   4   6000      Female        7 - 10 Postgraduate qualification
## 34  20   4  86000      Female        7 - 10 Postgraduate qualification
## 35  21   4  55000      Female        7 - 10 Postgraduate qualification
## 36  22   4  13000      Female        7 - 10 Postgraduate qualification
## 37   4   5   9000        Male         0 - 6          Bachelor's degree
## 38   5   5   7000        Male         0 - 6          Bachelor's degree
## 39   6   5   7000        Male         0 - 6          Bachelor's degree
## 40   7   5  30000        Male         0 - 6                Certificate
## 41   8   5  31000        Male         0 - 6                Certificate
## 42   9   5  23000        Male         0 - 6                Certificate
## 43  10   5   8000        Male         0 - 6                Certificate
## 44  11   5   9000        Male         0 - 6                    Diploma
## 45  12   5   8000        Male         0 - 6                    Diploma
## 46  13   5   5000        Male         0 - 6                    Diploma
## 47  14   5   1000        Male         0 - 6                    Diploma
## 48  15   5  12000        Male         0 - 6           No Qualification
## 49  16   5  21000        Male         0 - 6           No Qualification
## 50  17   5  17000        Male         0 - 6           No Qualification
## 51  18   5   9000        Male         0 - 6           No Qualification
## 52  20   5   7000        Male         0 - 6 Postgraduate qualification
## 53  21   5   6000        Male         0 - 6 Postgraduate qualification
## 54   3   6  13000        Male        7 - 10          Bachelor's degree
## 55   4   6  81000        Male        7 - 10          Bachelor's degree
## 56   5   6  66000        Male        7 - 10          Bachelor's degree
## 57   6   6  17000        Male        7 - 10          Bachelor's degree
## 58   7   6 190000        Male        7 - 10                Certificate
## 59   8   6 219000        Male        7 - 10                Certificate
## 60   9   6 199000        Male        7 - 10                Certificate
## 61  10   6 107000        Male        7 - 10                Certificate
## 62  11   6  11000        Male        7 - 10                    Diploma
## 63  12   6  47000        Male        7 - 10                    Diploma
## 64  13   6  58000        Male        7 - 10                    Diploma
## 65  14   6  34000        Male        7 - 10                    Diploma
## 66  15   6  37000        Male        7 - 10           No Qualification
## 67  16   6  50000        Male        7 - 10           No Qualification
## 68  17   6  75000        Male        7 - 10           No Qualification
## 69  18   6  66000        Male        7 - 10           No 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              25 - 44
## 16              45 - 64
## 17              15 - 24
## 18              25 - 44
## 19              45 - 64
## 20                  65+
## 21              15 - 24
## 22              25 - 44
## 23              45 - 64
## 24                  65+
## 25              15 - 24
## 26              25 - 44
## 27              45 - 64
## 28                  65+
## 29              15 - 24
## 30              25 - 44
## 31              45 - 64
## 32                  65+
## 33              15 - 24
## 34              25 - 44
## 35              45 - 64
## 36                  65+
## 37              25 - 44
## 38              45 - 64
## 39                  65+
## 40              15 - 24
## 41              25 - 44
## 42              45 - 64
## 43                  65+
## 44              15 - 24
## 45              25 - 44
## 46              45 - 64
## 47                  65+
## 48              15 - 24
## 49              25 - 44
## 50              45 - 64
## 51                  65+
## 52              25 - 44
## 53              45 - 64
## 54              15 - 24
## 55              25 - 44
## 56              45 - 64
## 57                  65+
## 58              15 - 24
## 59              25 - 44
## 60              45 - 64
## 61                  65+
## 62              15 - 24
## 63              25 - 44
## 64              45 - 64
## 65                  65+
## 66              15 - 24
## 67              25 - 44
## 68              45 - 64
## 69                  65+
## 70              25 - 44
## 71              45 - 64
## 72                  65+

SSE ESE example

cells <- tidy_table(purpose$`SSE ESE`)

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 qualification
## $`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 Male

Data

## # 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

Join headers to data

Result

##    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    5   1  29000      Female         0 - 6                Certificate
## 5    6   1  34000      Female         0 - 6                Certificate
## 6    7   1  30000      Female         0 - 6                Certificate
## 7    8   1  12000      Female         0 - 6                Certificate
## 8   10   1  10000      Female         0 - 6                    Diploma
## 9   11   1   6000      Female         0 - 6                    Diploma
## 10  12   1   5000      Female         0 - 6                    Diploma
## 11  13   1  10000      Female         0 - 6           No Qualification
## 12  14   1  11000      Female         0 - 6           No Qualification
## 13  15   1  19000      Female         0 - 6           No Qualification
## 14  16   1  16000      Female         0 - 6           No Qualification
## 15  18   1   5000      Female         0 - 6 Postgraduate qualification
## 16  19   1   6000      Female         0 - 6 Postgraduate qualification
## 17   1   2  27000      Female        7 - 10          Bachelor's degree
## 18   2   2 137000      Female        7 - 10          Bachelor's degree
## 19   3   2  64000      Female        7 - 10          Bachelor's degree
## 20   4   2  18000      Female        7 - 10          Bachelor's degree
## 21   5   2 161000      Female        7 - 10                Certificate
## 22   6   2 179000      Female        7 - 10                Certificate
## 23   7   2 210000      Female        7 - 10                Certificate
## 24   8   2  77000      Female        7 - 10                Certificate
## 25   9   2  14000      Female        7 - 10                    Diploma
## 26  10   2  66000      Female        7 - 10                    Diploma
## 27  11   2  68000      Female        7 - 10                    Diploma
## 28  12   2  41000      Female        7 - 10                    Diploma
## 29  13   2  43000      Female        7 - 10           No Qualification
## 30  14   2  36000      Female        7 - 10           No Qualification
## 31  15   2  91000      Female        7 - 10           No Qualification
## 32  16   2 118000      Female        7 - 10           No Qualification
## 33  17   2   6000      Female        7 - 10 Postgraduate qualification
## 34  18   2  86000      Female        7 - 10 Postgraduate qualification
## 35  19   2  55000      Female        7 - 10 Postgraduate qualification
## 36  20   2  13000      Female        7 - 10 Postgraduate qualification
## 37   2   3   9000        Male         0 - 6          Bachelor's degree
## 38   3   3   7000        Male         0 - 6          Bachelor's degree
## 39   4   3   7000        Male         0 - 6          Bachelor's degree
## 40   5   3  30000        Male         0 - 6                Certificate
## 41   6   3  31000        Male         0 - 6                Certificate
## 42   7   3  23000        Male         0 - 6                Certificate
## 43   8   3   8000        Male         0 - 6                Certificate
## 44   9   3   9000        Male         0 - 6                    Diploma
## 45  10   3   8000        Male         0 - 6                    Diploma
## 46  11   3   5000        Male         0 - 6                    Diploma
## 47  12   3   1000        Male         0 - 6                    Diploma
## 48  13   3  12000        Male         0 - 6           No Qualification
## 49  14   3  21000        Male         0 - 6           No Qualification
## 50  15   3  17000        Male         0 - 6           No Qualification
## 51  16   3   9000        Male         0 - 6           No Qualification
## 52  18   3   7000        Male         0 - 6 Postgraduate qualification
## 53  19   3   6000        Male         0 - 6 Postgraduate qualification
## 54   1   4  13000        Male        7 - 10          Bachelor's degree
## 55   2   4  81000        Male        7 - 10          Bachelor's degree
## 56   3   4  66000        Male        7 - 10          Bachelor's degree
## 57   4   4  17000        Male        7 - 10          Bachelor's degree
## 58   5   4 190000        Male        7 - 10                Certificate
## 59   6   4 219000        Male        7 - 10                Certificate
## 60   7   4 199000        Male        7 - 10                Certificate
## 61   8   4 107000        Male        7 - 10                Certificate
## 62   9   4  11000        Male        7 - 10                    Diploma
## 63  10   4  47000        Male        7 - 10                    Diploma
## 64  11   4  58000        Male        7 - 10                    Diploma
## 65  12   4  34000        Male        7 - 10                    Diploma
## 66  13   4  37000        Male        7 - 10           No Qualification
## 67  14   4  50000        Male        7 - 10           No Qualification
## 68  15   4  75000        Male        7 - 10           No Qualification
## 69  16   4  66000        Male        7 - 10           No 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              25 - 44
## 16              45 - 64
## 17              15 - 24
## 18              25 - 44
## 19              45 - 64
## 20                  65+
## 21              15 - 24
## 22              25 - 44
## 23              45 - 64
## 24                  65+
## 25              15 - 24
## 26              25 - 44
## 27              45 - 64
## 28                  65+
## 29              15 - 24
## 30              25 - 44
## 31              45 - 64
## 32                  65+
## 33              15 - 24
## 34              25 - 44
## 35              45 - 64
## 36                  65+
## 37              25 - 44
## 38              45 - 64
## 39                  65+
## 40              15 - 24
## 41              25 - 44
## 42              45 - 64
## 43                  65+
## 44              15 - 24
## 45              25 - 44
## 46              45 - 64
## 47                  65+
## 48              15 - 24
## 49              25 - 44
## 50              45 - 64
## 51                  65+
## 52              25 - 44
## 53              45 - 64
## 54              15 - 24
## 55              25 - 44
## 56              45 - 64
## 57                  65+
## 58              15 - 24
## 59              25 - 44
## 60              45 - 64
## 61                  65+
## 62              15 - 24
## 63              25 - 44
## 64              45 - 64
## 65                  65+
## 66              15 - 24
## 67              25 - 44
## 68              45 - 64
## 69                  65+
## 70              25 - 44
## 71              45 - 64
## 72                  65+

SSW ENE example

cells <- tidy_table(purpose$`SSW ENE`)

Headers (same as SSE ESE)

## $`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 qualification
## $`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 Male

Data (same as SSE ESE)

## # 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

Join headers to data

Result

##    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    5   1  29000      Female         0 - 6                Certificate
## 5    6   1  34000      Female         0 - 6                Certificate
## 6    7   1  30000      Female         0 - 6                Certificate
## 7    8   1  12000      Female         0 - 6                Certificate
## 8   10   1  10000      Female         0 - 6                    Diploma
## 9   11   1   6000      Female         0 - 6                    Diploma
## 10  12   1   5000      Female         0 - 6                    Diploma
## 11  13   1  10000      Female         0 - 6           No Qualification
## 12  14   1  11000      Female         0 - 6           No Qualification
## 13  15   1  19000      Female         0 - 6           No Qualification
## 14  16   1  16000      Female         0 - 6           No Qualification
## 15  18   1   5000      Female         0 - 6 Postgraduate qualification
## 16  19   1   6000      Female         0 - 6 Postgraduate qualification
## 17   1   2  27000      Female        7 - 10          Bachelor's degree
## 18   2   2 137000      Female        7 - 10          Bachelor's degree
## 19   3   2  64000      Female        7 - 10          Bachelor's degree
## 20   4   2  18000      Female        7 - 10          Bachelor's degree
## 21   5   2 161000      Female        7 - 10                Certificate
## 22   6   2 179000      Female        7 - 10                Certificate
## 23   7   2 210000      Female        7 - 10                Certificate
## 24   8   2  77000      Female        7 - 10                Certificate
## 25   9   2  14000      Female        7 - 10                    Diploma
## 26  10   2  66000      Female        7 - 10                    Diploma
## 27  11   2  68000      Female        7 - 10                    Diploma
## 28  12   2  41000      Female        7 - 10                    Diploma
## 29  13   2  43000      Female        7 - 10           No Qualification
## 30  14   2  36000      Female        7 - 10           No Qualification
## 31  15   2  91000      Female        7 - 10           No Qualification
## 32  16   2 118000      Female        7 - 10           No Qualification
## 33  17   2   6000      Female        7 - 10 Postgraduate qualification
## 34  18   2  86000      Female        7 - 10 Postgraduate qualification
## 35  19   2  55000      Female        7 - 10 Postgraduate qualification
## 36  20   2  13000      Female        7 - 10 Postgraduate qualification
## 37   2   3   9000        Male         0 - 6          Bachelor's degree
## 38   3   3   7000        Male         0 - 6          Bachelor's degree
## 39   4   3   7000        Male         0 - 6          Bachelor's degree
## 40   5   3  30000        Male         0 - 6                Certificate
## 41   6   3  31000        Male         0 - 6                Certificate
## 42   7   3  23000        Male         0 - 6                Certificate
## 43   8   3   8000        Male         0 - 6                Certificate
## 44   9   3   9000        Male         0 - 6                    Diploma
## 45  10   3   8000        Male         0 - 6                    Diploma
## 46  11   3   5000        Male         0 - 6                    Diploma
## 47  12   3   1000        Male         0 - 6                    Diploma
## 48  13   3  12000        Male         0 - 6           No Qualification
## 49  14   3  21000        Male         0 - 6           No Qualification
## 50  15   3  17000        Male         0 - 6           No Qualification
## 51  16   3   9000        Male         0 - 6           No Qualification
## 52  18   3   7000        Male         0 - 6 Postgraduate qualification
## 53  19   3   6000        Male         0 - 6 Postgraduate qualification
## 54   1   4  13000        Male        7 - 10          Bachelor's degree
## 55   2   4  81000        Male        7 - 10          Bachelor's degree
## 56   3   4  66000        Male        7 - 10          Bachelor's degree
## 57   4   4  17000        Male        7 - 10          Bachelor's degree
## 58   5   4 190000        Male        7 - 10                Certificate
## 59   6   4 219000        Male        7 - 10                Certificate
## 60   7   4 199000        Male        7 - 10                Certificate
## 61   8   4 107000        Male        7 - 10                Certificate
## 62   9   4  11000        Male        7 - 10                    Diploma
## 63  10   4  47000        Male        7 - 10                    Diploma
## 64  11   4  58000        Male        7 - 10                    Diploma
## 65  12   4  34000        Male        7 - 10                    Diploma
## 66  13   4  37000        Male        7 - 10           No Qualification
## 67  14   4  50000        Male        7 - 10           No Qualification
## 68  15   4  75000        Male        7 - 10           No Qualification
## 69  16   4  66000        Male        7 - 10           No 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              25 - 44
## 16              45 - 64
## 17              15 - 24
## 18              25 - 44
## 19              45 - 64
## 20                  65+
## 21              15 - 24
## 22              25 - 44
## 23              45 - 64
## 24                  65+
## 25              15 - 24
## 26              25 - 44
## 27              45 - 64
## 28                  65+
## 29              15 - 24
## 30              25 - 44
## 31              45 - 64
## 32                  65+
## 33              15 - 24
## 34              25 - 44
## 35              45 - 64
## 36                  65+
## 37              25 - 44
## 38              45 - 64
## 39                  65+
## 40              15 - 24
## 41              25 - 44
## 42              45 - 64
## 43                  65+
## 44              15 - 24
## 45              25 - 44
## 46              45 - 64
## 47                  65+
## 48              15 - 24
## 49              25 - 44
## 50              45 - 64
## 51                  65+
## 52              25 - 44
## 53              45 - 64
## 54              15 - 24
## 55              25 - 44
## 56              45 - 64
## 57                  65+
## 58              15 - 24
## 59              25 - 44
## 60              45 - 64
## 61                  65+
## 62              15 - 24
## 63              25 - 44
## 64              45 - 64
## 65                  65+
## 66              15 - 24
## 67              25 - 44
## 68              45 - 64
## 69                  65+
## 70              25 - 44
## 71              45 - 64
## 72                  65+

Check that all sheets are parsed correctly

identical(NNW_WNW, NNE_WSW)
## [1] TRUE
identical(SSW_ENE, SSE_ESE)
## [1] TRUE
identical(NNW_WNW[, -1:-2], SSW_ENE[, -1:-2])
## [1] TRUE

ABOVE LEFT example

cells <- tidy_table(purpose$`ABOVE LEFT`)

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 qualification
## $`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 - 64

Data

## # 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

Join headers to data

Result

##    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    9   3  30000       0 - 6       15 - 24             Male
## 5   10   3   9000       0 - 6       15 - 24             Male
## 6   11   3  12000       0 - 6       15 - 24             Male
## 7    3   4  12000       0 - 6       25 - 44           Female
## 8    4   4  34000       0 - 6       25 - 44           Female
## 9    5   4  10000       0 - 6       25 - 44           Female
## 10   6   4  11000       0 - 6       25 - 44           Female
## 11   7   4   5000       0 - 6       25 - 44           Female
## 12   8   4   9000       0 - 6       25 - 44             Male
## 13   9   4  31000       0 - 6       25 - 44             Male
## 14  10   4   8000       0 - 6       25 - 44             Male
## 15  11   4  21000       0 - 6       25 - 44             Male
## 16  12   4   7000       0 - 6       25 - 44             Male
## 17   3   5  10000       0 - 6       45 - 64           Female
## 18   4   5  30000       0 - 6       45 - 64           Female
## 19   5   5   6000       0 - 6       45 - 64           Female
## 20   6   5  19000       0 - 6       45 - 64           Female
## 21   7   5   6000       0 - 6       45 - 64           Female
## 22   8   5   7000       0 - 6       45 - 64             Male
## 23   9   5  23000       0 - 6       45 - 64             Male
## 24  10   5   5000       0 - 6       45 - 64             Male
## 25  11   5  17000       0 - 6       45 - 64             Male
## 26  12   5   6000       0 - 6       45 - 64             Male
## 27   3   6  27000      7 - 10       15 - 24           Female
## 28   4   6 161000      7 - 10       15 - 24           Female
## 29   5   6  14000      7 - 10       15 - 24           Female
## 30   6   6  43000      7 - 10       15 - 24           Female
## 31   7   6   6000      7 - 10       15 - 24           Female
## 32   8   6  13000      7 - 10       15 - 24             Male
## 33   9   6 190000      7 - 10       15 - 24             Male
## 34  10   6  11000      7 - 10       15 - 24             Male
## 35  11   6  37000      7 - 10       15 - 24             Male
## 36   3   7 137000      7 - 10       25 - 44           Female
## 37   4   7 179000      7 - 10       25 - 44           Female
## 38   5   7  66000      7 - 10       25 - 44           Female
## 39   6   7  36000      7 - 10       25 - 44           Female
## 40   7   7  86000      7 - 10       25 - 44           Female
## 41   8   7  81000      7 - 10       25 - 44             Male
## 42   9   7 219000      7 - 10       25 - 44             Male
## 43  10   7  47000      7 - 10       25 - 44             Male
## 44  11   7  50000      7 - 10       25 - 44             Male
## 45  12   7  60000      7 - 10       25 - 44             Male
## 46   3   8  64000      7 - 10       45 - 64           Female
## 47   4   8 210000      7 - 10       45 - 64           Female
## 48   5   8  68000      7 - 10       45 - 64           Female
## 49   6   8  91000      7 - 10       45 - 64           Female
## 50   7   8  55000      7 - 10       45 - 64           Female
## 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                 Certificate
## 5                     Diploma
## 6            No Qualification
## 7           Bachelor's degree
## 8                 Certificate
## 9                     Diploma
## 10           No Qualification
## 11 Postgraduate qualification
## 12          Bachelor's degree
## 13                Certificate
## 14                    Diploma
## 15           No Qualification
## 16 Postgraduate qualification
## 17          Bachelor's degree
## 18                Certificate
## 19                    Diploma
## 20           No Qualification
## 21 Postgraduate qualification
## 22          Bachelor's degree
## 23                Certificate
## 24                    Diploma
## 25           No Qualification
## 26 Postgraduate qualification
## 27          Bachelor's degree
## 28                Certificate
## 29                    Diploma
## 30           No Qualification
## 31 Postgraduate qualification
## 32          Bachelor's degree
## 33                Certificate
## 34                    Diploma
## 35           No Qualification
## 36          Bachelor's degree
## 37                Certificate
## 38                    Diploma
## 39           No Qualification
## 40 Postgraduate qualification
## 41          Bachelor's degree
## 42                Certificate
## 43                    Diploma
## 44           No Qualification
## 45 Postgraduate 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

BELOW RIGHT example

cells <- tidy_table(purpose$`BELOW RIGHT`)

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 Male
## $`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 - 10

Data

## # 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

Join headers to data

Result

##    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    7   1  30000       0 - 6       15 - 24             Male
## 5    8   1   9000       0 - 6       15 - 24             Male
## 6    9   1  12000       0 - 6       15 - 24             Male
## 7    1   2  12000       0 - 6       25 - 44           Female
## 8    2   2  34000       0 - 6       25 - 44           Female
## 9    3   2  10000       0 - 6       25 - 44           Female
## 10   4   2  11000       0 - 6       25 - 44           Female
## 11   5   2   5000       0 - 6       25 - 44           Female
## 12   6   2   9000       0 - 6       25 - 44             Male
## 13   7   2  31000       0 - 6       25 - 44             Male
## 14   8   2   8000       0 - 6       25 - 44             Male
## 15   9   2  21000       0 - 6       25 - 44             Male
## 16  10   2   7000       0 - 6       25 - 44             Male
## 17   1   3  10000       0 - 6       45 - 64           Female
## 18   2   3  30000       0 - 6       45 - 64           Female
## 19   3   3   6000       0 - 6       45 - 64           Female
## 20   4   3  19000       0 - 6       45 - 64           Female
## 21   5   3   6000       0 - 6       45 - 64           Female
## 22   6   3   7000       0 - 6       45 - 64             Male
## 23   7   3  23000       0 - 6       45 - 64             Male
## 24   8   3   5000       0 - 6       45 - 64             Male
## 25   9   3  17000       0 - 6       45 - 64             Male
## 26  10   3   6000       0 - 6       45 - 64             Male
## 27   1   4  27000      7 - 10       15 - 24           Female
## 28   2   4 161000      7 - 10       15 - 24           Female
## 29   3   4  14000      7 - 10       15 - 24           Female
## 30   4   4  43000      7 - 10       15 - 24           Female
## 31   5   4   6000      7 - 10       15 - 24           Female
## 32   6   4  13000      7 - 10       15 - 24             Male
## 33   7   4 190000      7 - 10       15 - 24             Male
## 34   8   4  11000      7 - 10       15 - 24             Male
## 35   9   4  37000      7 - 10       15 - 24             Male
## 36   1   5 137000      7 - 10       25 - 44           Female
## 37   2   5 179000      7 - 10       25 - 44           Female
## 38   3   5  66000      7 - 10       25 - 44           Female
## 39   4   5  36000      7 - 10       25 - 44           Female
## 40   5   5  86000      7 - 10       25 - 44           Female
## 41   6   5  81000      7 - 10       25 - 44             Male
## 42   7   5 219000      7 - 10       25 - 44             Male
## 43   8   5  47000      7 - 10       25 - 44             Male
## 44   9   5  50000      7 - 10       25 - 44             Male
## 45  10   5  60000      7 - 10       25 - 44             Male
## 46   1   6  64000      7 - 10       45 - 64           Female
## 47   2   6 210000      7 - 10       45 - 64           Female
## 48   3   6  68000      7 - 10       45 - 64           Female
## 49   4   6  91000      7 - 10       45 - 64           Female
## 50   5   6  55000      7 - 10       45 - 64           Female
## 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                 Certificate
## 5                     Diploma
## 6            No Qualification
## 7           Bachelor's degree
## 8                 Certificate
## 9                     Diploma
## 10           No Qualification
## 11 Postgraduate qualification
## 12          Bachelor's degree
## 13                Certificate
## 14                    Diploma
## 15           No Qualification
## 16 Postgraduate qualification
## 17          Bachelor's degree
## 18                Certificate
## 19                    Diploma
## 20           No Qualification
## 21 Postgraduate qualification
## 22          Bachelor's degree
## 23                Certificate
## 24                    Diploma
## 25           No Qualification
## 26 Postgraduate qualification
## 27          Bachelor's degree
## 28                Certificate
## 29                    Diploma
## 30           No Qualification
## 31 Postgraduate qualification
## 32          Bachelor's degree
## 33                Certificate
## 34                    Diploma
## 35           No Qualification
## 36          Bachelor's degree
## 37                Certificate
## 38                    Diploma
## 39           No Qualification
## 40 Postgraduate qualification
## 41          Bachelor's degree
## 42                Certificate
## 43                    Diploma
## 44           No Qualification
## 45 Postgraduate 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

Check that all sheets are parsed correctly

identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT[, -1:-2])
## [1] TRUE

ABOVE LEFT border example

spreadsheet <- 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.

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 qualification
## $`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

Data

## # 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

Borders

## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

Join headers to data

Result

##    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      7 - 10       15 - 24           Female
## 5    4   8 137000      7 - 10       25 - 44           Female
## 6    4   9  64000      7 - 10       45 - 64           Female
## 7    5   4  29000       0 - 6       15 - 24           Female
## 8    5   5  34000       0 - 6       25 - 44           Female
## 9    5   6  30000       0 - 6       45 - 64           Female
## 10   5   7 161000      7 - 10       15 - 24           Female
## 11   5   8 179000      7 - 10       25 - 44           Female
## 12   5   9 210000      7 - 10       45 - 64           Female
## 13   6   5  10000       0 - 6       25 - 44           Female
## 14   6   6   6000       0 - 6       45 - 64           Female
## 15   6   7  14000      7 - 10       15 - 24           Female
## 16   6   8  66000      7 - 10       25 - 44           Female
## 17   6   9  68000      7 - 10       45 - 64           Female
## 18   7   4  10000       0 - 6       15 - 24           Female
## 19   7   5  11000       0 - 6       25 - 44           Female
## 20   7   6  19000       0 - 6       45 - 64           Female
## 21   7   7  43000      7 - 10       15 - 24           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           Female
## 25   8   6   6000       0 - 6       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   9   7  13000      7 - 10       15 - 24             Male
## 32   9   8  81000      7 - 10       25 - 44             Male
## 33   9   9  66000      7 - 10       45 - 64             Male
## 34  10   4  30000       0 - 6       15 - 24             Male
## 35  10   5  31000       0 - 6       25 - 44             Male
## 36  10   6  23000       0 - 6       45 - 64             Male
## 37  10   7 190000      7 - 10       15 - 24             Male
## 38  10   8 219000      7 - 10       25 - 44             Male
## 39  10   9 199000      7 - 10       45 - 64             Male
## 40  11   4   9000       0 - 6       15 - 24             Male
## 41  11   5   8000       0 - 6       25 - 44             Male
## 42  11   6   5000       0 - 6       45 - 64             Male
## 43  11   7  11000      7 - 10       15 - 24             Male
## 44  11   8  47000      7 - 10       25 - 44             Male
## 45  11   9  58000      7 - 10       45 - 64             Male
## 46  12   4  12000       0 - 6       15 - 24             Male
## 47  12   5  21000       0 - 6       25 - 44             Male
## 48  12   6  17000       0 - 6       45 - 64             Male
## 49  12   7  37000      7 - 10       15 - 24             Male
## 50  12   8  50000      7 - 10       25 - 44             Male
## 51  12   9  75000      7 - 10       45 - 64             Male
## 52  13   5   7000       0 - 6       25 - 44             Male
## 53  13   6   6000       0 - 6       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           Bachelor's degree
## 6           Bachelor's degree
## 7                 Certificate
## 8                 Certificate
## 9                 Certificate
## 10                Certificate
## 11                Certificate
## 12                Certificate
## 13                    Diploma
## 14                    Diploma
## 15                    Diploma
## 16                    Diploma
## 17                    Diploma
## 18           No Qualification
## 19           No Qualification
## 20           No Qualification
## 21           No Qualification
## 22           No Qualification
## 23           No Qualification
## 24 Postgraduate qualification
## 25 Postgraduate qualification
## 26 Postgraduate qualification
## 27 Postgraduate qualification
## 28 Postgraduate qualification
## 29          Bachelor's degree
## 30          Bachelor's degree
## 31          Bachelor's degree
## 32          Bachelor's degree
## 33          Bachelor's degree
## 34                Certificate
## 35                Certificate
## 36                Certificate
## 37                Certificate
## 38                Certificate
## 39                Certificate
## 40                    Diploma
## 41                    Diploma
## 42                    Diploma
## 43                    Diploma
## 44                    Diploma
## 45                    Diploma
## 46           No Qualification
## 47           No Qualification
## 48           No Qualification
## 49           No Qualification
## 50           No Qualification
## 51           No Qualification
## 52 Postgraduate qualification
## 53 Postgraduate qualification
## 54 Postgraduate qualification
## 55 Postgraduate qualification

BELOW RIGHT border example

cells <- tidy_xlsx(spreadsheet, "BELOW RIGHT border")$data[[1]]
## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

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 Male
## $`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

Data

## # 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

Borders

## Warning: 'tidy_xlsx()' is deprecated.
## Use 'xlsx_cells()' or 'xlsx_formats()' instead.

Join headers to data

Result

##    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      7 - 10       15 - 24           Female
## 5    4   8 137000      7 - 10       25 - 44           Female
## 6    4   9  64000      7 - 10       45 - 64           Female
## 7    5   4  29000       0 - 6       15 - 24           Female
## 8    5   5  34000       0 - 6       25 - 44           Female
## 9    5   6  30000       0 - 6       45 - 64           Female
## 10   5   7 161000      7 - 10       15 - 24           Female
## 11   5   8 179000      7 - 10       25 - 44           Female
## 12   5   9 210000      7 - 10       45 - 64           Female
## 13   6   5  10000       0 - 6       25 - 44           Female
## 14   6   6   6000       0 - 6       45 - 64           Female
## 15   6   7  14000      7 - 10       15 - 24           Female
## 16   6   8  66000      7 - 10       25 - 44           Female
## 17   6   9  68000      7 - 10       45 - 64           Female
## 18   7   4  10000       0 - 6       15 - 24           Female
## 19   7   5  11000       0 - 6       25 - 44           Female
## 20   7   6  19000       0 - 6       45 - 64           Female
## 21   7   7  43000      7 - 10       15 - 24           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           Female
## 25   8   6   6000       0 - 6       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   9   7  13000      7 - 10       15 - 24             Male
## 32   9   8  81000      7 - 10       25 - 44             Male
## 33   9   9  66000      7 - 10       45 - 64             Male
## 34  10   4  30000       0 - 6       15 - 24             Male
## 35  10   5  31000       0 - 6       25 - 44             Male
## 36  10   6  23000       0 - 6       45 - 64             Male
## 37  10   7 190000      7 - 10       15 - 24             Male
## 38  10   8 219000      7 - 10       25 - 44             Male
## 39  10   9 199000      7 - 10       45 - 64             Male
## 40  11   4   9000       0 - 6       15 - 24             Male
## 41  11   5   8000       0 - 6       25 - 44             Male
## 42  11   6   5000       0 - 6       45 - 64             Male
## 43  11   7  11000      7 - 10       15 - 24             Male
## 44  11   8  47000      7 - 10       25 - 44             Male
## 45  11   9  58000      7 - 10       45 - 64             Male
## 46  12   4  12000       0 - 6       15 - 24             Male
## 47  12   5  21000       0 - 6       25 - 44             Male
## 48  12   6  17000       0 - 6       45 - 64             Male
## 49  12   7  37000      7 - 10       15 - 24             Male
## 50  12   8  50000      7 - 10       25 - 44             Male
## 51  12   9  75000      7 - 10       45 - 64             Male
## 52  13   5   7000       0 - 6       25 - 44             Male
## 53  13   6   6000       0 - 6       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           Bachelor's degree
## 6           Bachelor's degree
## 7                 Certificate
## 8                 Certificate
## 9                 Certificate
## 10                Certificate
## 11                Certificate
## 12                Certificate
## 13                    Diploma
## 14                    Diploma
## 15                    Diploma
## 16                    Diploma
## 17                    Diploma
## 18           No Qualification
## 19           No Qualification
## 20           No Qualification
## 21           No Qualification
## 22           No Qualification
## 23           No Qualification
## 24 Postgraduate qualification
## 25 Postgraduate qualification
## 26 Postgraduate qualification
## 27 Postgraduate qualification
## 28 Postgraduate qualification
## 29          Bachelor's degree
## 30          Bachelor's degree
## 31          Bachelor's degree
## 32          Bachelor's degree
## 33          Bachelor's degree
## 34                Certificate
## 35                Certificate
## 36                Certificate
## 37                Certificate
## 38                Certificate
## 39                Certificate
## 40                    Diploma
## 41                    Diploma
## 42                    Diploma
## 43                    Diploma
## 44                    Diploma
## 45                    Diploma
## 46           No Qualification
## 47           No Qualification
## 48           No Qualification
## 49           No Qualification
## 50           No Qualification
## 51           No Qualification
## 52 Postgraduate qualification
## 53 Postgraduate qualification
## 54 Postgraduate qualification
## 55 Postgraduate qualification

Check that all sheets are parsed correctly

identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT[, -1:-2])
## [1] TRUE
identical(ABOVE_LEFT[, -1:-2], ABOVE_LEFT_borders[, -1:-2])
## [1] TRUE
identical(ABOVE_LEFT[, -1:-2], BELOW_RIGHT_borders[, -1:-2])
## [1] TRUE