Fast and Easy Data Reshaping
pivot.Rdpivot() is collapse's data reshaping command. It combines longer-, wider-, and recast-pivoting functionality in a single parsimonious API. Notably, it can also accommodate variable labels.
Usage
pivot(data, # Summary of Documentation:
ids = NULL, # identifier cols to preserve
values = NULL, # cols containing the data
names = NULL, # name(s) of new col(s) | col(s) containing names
labels = NULL, # name of new labels col | col(s) containing labels
how = "longer", # method: "longer"/"l", "wider"/"w" or "recast"/"r"
na.rm = FALSE, # remove rows missing 'values' in reshaped data
factor = c("names", "labels"), # create new id col(s) as factor variable(s)?
check.dups = FALSE, # detect duplicate 'ids'+'names' combinations
# Only apply if how = "wider" or "recast"
FUN = "last", # aggregation function (internal or external)
FUN.args = NULL, # list of arguments passed to aggregation function
nthreads = .op[["nthreads"]], # minor gains as grouping remains serial
fill = NULL, # value to insert for unbalanced data (default NA/NULL)
drop = TRUE, # drop unused levels (=columns) if 'names' is factor
sort = FALSE, # "ids": sort 'ids' and/or "names": alphabetic casting
# Only applies if how = "wider" with multiple long columns ('values')
transpose = FALSE # "columns": applies t_list() before flattening, and/or
) # "names": sets names nami_colj. default: colj_namiArguments
- data
data frame-like object (list of equal-length columns).
- ids
identifier columns to keep. Specified using column names, indices, a logical vector or an identifier function e.g.
is_categorical.- values
columns containing the data to be reshaped. Specified like
ids.- names
names of columns to generate, or retrieve variable names from:
howDescription "longer"list of names for the variable and value column in the long format, respectively. If NULL,list("variable", "value")will be chosen. Alternatively, a named list length 1 or 2 can be provided using "variable"/"value" as keys e.g.list(value = "data_col")."wider"column(s) containing names of the new variables. Specified using a vector of column names, indices, a logical vector or selector function e.g. is.character. Multiple columns will be combined usingfinteractionwith"_"as separator."recast"(named) list with the following elements: [[1]]/[["from"]] - column(s) containing names of the new variables, specified as in "wider"; [[2]]/[["to"]] - name of the variable to generate containing old column names. IfNULL,list("variable", "variable")will be chosen.- labels
names of columns to generate, or retrieve variable labels from:
howDescription "longer"A string specifying the name of the column to store labels - retrieved from the data using vlabels(values).TRUEwill create a column named"label". Alternatively, a (named) list with two elements: [[1]]/[["name"]] - the name of the labels column; [[2]]/[["new"]] - a (named) character vector of new labels for the 'variable', 'label' and 'value' columns in the long-format frame. See Examples."wider"column(s) containing labels of the new variables. Specified using a vector of column names, indices, a logical vector or selector function e.g. is.character. Multiple columns will be combined usingfinteractionwith" - "as separator."recast"(named) list with the following elements: [[1]]/[["from"]] - column(s) containing labels for the new variables, specified as in "wider"; [[2]]/[["to"]] - name of the variable to generate containing old labels; [[3]]/[["new"]] - a (named) character vector of new labels for the generated 'variable' and 'label' columns. If [[1]]/[["from"]] is not supplied, this can also include labels for new variables. Omitting one of the elements via a named list or setting it toNULLin a list of 3 will omit the corresponding operation i.e. either not saving existing labels or not assigning new ones.- how
character. The pivoting method: one of
"longer","wider"or"recast". These can be abbreviated by the first letter i.e."l"/"w"/"r".- na.rm
logical.
TRUEwill remove missing values such that in the reshaped data there is no row missing all data columns - selected through 'values'. For wide/recast pivots using internalFUN's"first"/"last"/"count", this also toggles skipping of missing values.- factor
character. Whether to generate new 'names' and/or 'labels' columns as factor variables. This is generally recommended as factors are more memory efficient than character vectors and also faster in subsequent filtering and grouping. Internally, this argument is evaluated as
factor <- c("names", "labels") %in% factor, so passing anything other than"names"and/or"labels"will disable it.- check.dups
logical.
TRUEchecks for duplicate 'ids'+'names' combinations, and, if 'labels' are specified, also for duplicate 'names'+'labels' combinations. The defaultFALSEimplies that the algorithm just runs through the data, leading effectively to theFUNoption to be executed (default last value). See Details.- FUN
function to aggregate values. At present, only a single function is allowed. Fast Statistical Functions receive vectorized execution. For maximum efficiency, a small set of internal functions is provided:
"first","last","count","sum","mean","min", or"max". In options"first"/"last"/"count"settingna.rm = TRUEskips missing values. In options"sum"/"mean"/"min"/"max"missing values are always skipped (see Details why). Thefillargument is ignored in"count"/"sum"/"mean"/"min"/"max"("count"/"sum"forcefill = 0elseNAis used).- FUN.args
(optional) list of arguments passed to
FUN(if using an external function). Data-length arguments such as weight vectors are supported.- nthreads
integer. if
how = "wider"|"recast": number of threads to use with OpenMP (defaultget_collapse("nthreads"), initialized to 1). Only the distribution of values to columns withhow = "wider"|"recast"is multithreaded here. Since grouping id columns on a long data frame is expensive and serial, the gains are minor. Withhow = "long", multithreading does not make much sense as the most expensive operation is allocating the long results vectors. The rest is a couple ofmemset()'s in C to copy the values.- fill
if
how = "wider"|"recast": value to insert for 'ids'-'names' combinations not present in the long format.NULLusesNAfor atomic vectors andNULLfor lists.- drop
logical. if
how = "wider"|"recast"and 'names' is a single factor variable:TRUEwill check for and drop unused levels in that factor, avoiding the generation of empty columns.- sort
if
how = "wider"|"recast": specifying"ids"applies ordered grouping on the id-columns, returning data sorted by ids. Specifying"names"sorts the names before casting (unless 'names' is a factor), yielding columns cast in alphabetic order. Both options can be passed as a character vector, or, alternatively,TRUEcan be used to enable both.- transpose
if
how = "wider"|"recast"and multiple columns are selected through 'values': specifying"columns"appliest_listto the result before flattening, resulting in a different column order. Specifying"names"generates names of the form nami_colj, instead of colj_nami. Both options can be passed as a character vector, or, alternatively,TRUEcan be used to enable both.
Details
Pivot wider essentially works as follows: compute g_rows = group(ids) and also g_cols = group(names) (using group if sort = FALSE). g_rows gives the row-numbers of the wider data frame and g_cols the column numbers.
Then, a C function generates a wide data frame and runs through each long column ('values'), assigning each value to the corresponding row and column in the wide frame. In this process FUN is always applied. The default, "last", does nothing at all, i.e., if there are duplicates, some values are overwritten. "first" works similarly just that the C-loop is executed the other way around. The other hard-coded options count, sum, average, or compare observations on the fly. Missing values are internally skipped for statistical functions as there is no way to distinguish an incoming NA from an initial NA - apart from counting occurrences using an internal structure of the same size as the result data frame which is costly and thus not implemented.
When passing an R-function to FUN, the data is grouped using g_full = group(g_rows, g_cols), aggregated by groups, and expanded again to full length using TRA before entering the reshaping algorithm. Thus, this is significantly more expensive than the optimized internal functions. With Fast Statistical Functions the aggregation is vectorized across groups, other functions are applied using BY - by far the slowest option.
If check.dups = TRUE, a check of the form fnunique(list(g_rows, g_cols)) < fnrow(data) is run, and an informative warning is issued if duplicates are found.
Recast pivoting works similarly. In long pivots FUN is ignored and the check simply amounts to fnunique(ids) < fnrow(data).
Value
A reshaped data frame with the same class and attributes (except for 'names'/'row-names') as the input frame.
Note
Leaving either 'ids' or 'values' empty will assign all other columns (except for "variable" if how = "wider"|"recast") to the non-specified argument. It is also possible to leave both empty, e.g. for complete melting if how = "longer" or data transposition if how = "recast" (similar to data.table::transpose but supporting multiple names columns and variable labels). See Examples.
pivot currently does not support concurrently melting/pivoting longer to multiple columns. See data.table::melt or pivot_longer from tidyr or tidytable for an efficient alternative with this feature. It is also possible to achieve this with just a little bit of programming. An example is provided below.
Examples
# -------------------------------- PIVOT LONGER ---------------------------------
# Simple Melting (Reshaping Long)
pivot(mtcars) |> head()
#> variable value
#> 1 mpg 21.0
#> 2 mpg 21.0
#> 3 mpg 22.8
#> 4 mpg 21.4
#> 5 mpg 18.7
#> 6 mpg 18.1
pivot(iris, "Species") |> head()
#> Species variable value
#> 1 setosa Sepal.Length 5.1
#> 2 setosa Sepal.Length 4.9
#> 3 setosa Sepal.Length 4.7
#> 4 setosa Sepal.Length 4.6
#> 5 setosa Sepal.Length 5.0
#> 6 setosa Sepal.Length 5.4
pivot(iris, values = 1:4) |> head() # Same thing
#> Species variable value
#> 1 setosa Sepal.Length 5.1
#> 2 setosa Sepal.Length 4.9
#> 3 setosa Sepal.Length 4.7
#> 4 setosa Sepal.Length 4.6
#> 5 setosa Sepal.Length 5.0
#> 6 setosa Sepal.Length 5.4
# Using collapse's datasets
head(wlddev)
#> country iso3c date year decade region income OECD PCGDP
#> 1 Afghanistan AFG 1961-01-01 1960 1960 South Asia Low income FALSE NA
#> 2 Afghanistan AFG 1962-01-01 1961 1960 South Asia Low income FALSE NA
#> 3 Afghanistan AFG 1963-01-01 1962 1960 South Asia Low income FALSE NA
#> 4 Afghanistan AFG 1964-01-01 1963 1960 South Asia Low income FALSE NA
#> 5 Afghanistan AFG 1965-01-01 1964 1960 South Asia Low income FALSE NA
#> 6 Afghanistan AFG 1966-01-01 1965 1960 South Asia Low income FALSE NA
#> LIFEEX GINI ODA POP
#> 1 32.446 NA 116769997 8996973
#> 2 32.962 NA 232080002 9169410
#> 3 33.471 NA 112839996 9351441
#> 4 33.971 NA 237720001 9543205
#> 5 34.463 NA 295920013 9744781
#> 6 34.948 NA 341839996 9956320
pivot(wlddev, 1:8, na.rm = TRUE) |> head()
#> country iso3c date year decade region income OECD variable
#> 1 Afghanistan AFG 2003-01-01 2002 2000 South Asia Low income FALSE PCGDP
#> 2 Afghanistan AFG 2004-01-01 2003 2000 South Asia Low income FALSE PCGDP
#> 3 Afghanistan AFG 2005-01-01 2004 2000 South Asia Low income FALSE PCGDP
#> 4 Afghanistan AFG 2006-01-01 2005 2000 South Asia Low income FALSE PCGDP
#> 5 Afghanistan AFG 2007-01-01 2006 2000 South Asia Low income FALSE PCGDP
#> 6 Afghanistan AFG 2008-01-01 2007 2000 South Asia Low income FALSE PCGDP
#> value
#> 1 330.3036
#> 2 343.0809
#> 3 333.2167
#> 4 357.2347
#> 5 365.2845
#> 6 405.5490
pivot(wlddev, c("iso3c", "year"), c("PCGDP", "LIFEEX"), na.rm = TRUE) |> head()
#> iso3c year variable value
#> 1 AFG 2002 PCGDP 330.3036
#> 2 AFG 2003 PCGDP 343.0809
#> 3 AFG 2004 PCGDP 333.2167
#> 4 AFG 2005 PCGDP 357.2347
#> 5 AFG 2006 PCGDP 365.2845
#> 6 AFG 2007 PCGDP 405.5490
head(GGDC10S)
#> Country Regioncode Region Variable Year AGR MIN
#> 1 BWA SSA Sub-saharan Africa VA 1960 NA NA
#> 2 BWA SSA Sub-saharan Africa VA 1961 NA NA
#> 3 BWA SSA Sub-saharan Africa VA 1962 NA NA
#> 4 BWA SSA Sub-saharan Africa VA 1963 NA NA
#> 5 BWA SSA Sub-saharan Africa VA 1964 16.30154 3.494075
#> 6 BWA SSA Sub-saharan Africa VA 1965 15.72700 2.495768
#> MAN PU CON WRT TRA FIRE GOV OTH
#> 1 NA NA NA NA NA NA NA NA
#> 2 NA NA NA NA NA NA NA NA
#> 3 NA NA NA NA NA NA NA NA
#> 4 NA NA NA NA NA NA NA NA
#> 5 0.7365696 0.1043936 0.6600454 6.243732 1.658928 1.119194 4.822485 2.341328
#> 6 1.0181992 0.1350976 1.3462312 7.064825 1.939007 1.246789 5.695848 2.678338
#> SUM
#> 1 NA
#> 2 NA
#> 3 NA
#> 4 NA
#> 5 37.48229
#> 6 39.34710
pivot(GGDC10S, 1:5, names = list("Sectorcode", "Value"), na.rm = TRUE) |> head()
#> Country Regioncode Region Variable Year Sectorcode Value
#> 1 BWA SSA Sub-saharan Africa VA 1964 AGR 16.30154
#> 2 BWA SSA Sub-saharan Africa VA 1965 AGR 15.72700
#> 3 BWA SSA Sub-saharan Africa VA 1966 AGR 17.68066
#> 4 BWA SSA Sub-saharan Africa VA 1967 AGR 19.14591
#> 5 BWA SSA Sub-saharan Africa VA 1968 AGR 21.09957
#> 6 BWA SSA Sub-saharan Africa VA 1969 AGR 21.86221
# Can also set by name: variable and/or value. Note that 'value' here remains lowercase
pivot(GGDC10S, 1:5, names = list(variable = "Sectorcode"), na.rm = TRUE) |> head()
#> Country Regioncode Region Variable Year Sectorcode value
#> 1 BWA SSA Sub-saharan Africa VA 1964 AGR 16.30154
#> 2 BWA SSA Sub-saharan Africa VA 1965 AGR 15.72700
#> 3 BWA SSA Sub-saharan Africa VA 1966 AGR 17.68066
#> 4 BWA SSA Sub-saharan Africa VA 1967 AGR 19.14591
#> 5 BWA SSA Sub-saharan Africa VA 1968 AGR 21.09957
#> 6 BWA SSA Sub-saharan Africa VA 1969 AGR 21.86221
# Melting including saving labels
pivot(GGDC10S, 1:5, na.rm = TRUE, labels = TRUE) |> head()
#> Country Regioncode Region Variable Year variable label
#> 1 BWA SSA Sub-saharan Africa VA 1964 AGR Agriculture
#> 2 BWA SSA Sub-saharan Africa VA 1965 AGR Agriculture
#> 3 BWA SSA Sub-saharan Africa VA 1966 AGR Agriculture
#> 4 BWA SSA Sub-saharan Africa VA 1967 AGR Agriculture
#> 5 BWA SSA Sub-saharan Africa VA 1968 AGR Agriculture
#> 6 BWA SSA Sub-saharan Africa VA 1969 AGR Agriculture
#> value
#> 1 16.30154
#> 2 15.72700
#> 3 17.68066
#> 4 19.14591
#> 5 21.09957
#> 6 21.86221
pivot(GGDC10S, 1:5, na.rm = TRUE, labels = "description") |> head()
#> Country Regioncode Region Variable Year variable description
#> 1 BWA SSA Sub-saharan Africa VA 1964 AGR Agriculture
#> 2 BWA SSA Sub-saharan Africa VA 1965 AGR Agriculture
#> 3 BWA SSA Sub-saharan Africa VA 1966 AGR Agriculture
#> 4 BWA SSA Sub-saharan Africa VA 1967 AGR Agriculture
#> 5 BWA SSA Sub-saharan Africa VA 1968 AGR Agriculture
#> 6 BWA SSA Sub-saharan Africa VA 1969 AGR Agriculture
#> value
#> 1 16.30154
#> 2 15.72700
#> 3 17.68066
#> 4 19.14591
#> 5 21.09957
#> 6 21.86221
# Also assigning new labels
pivot(GGDC10S, 1:5, na.rm = TRUE, labels = list("description",
c("Sector Code", "Sector Description", "Value"))) |> namlab()
#> Variable Label
#> 1 Country Country
#> 2 Regioncode Region code
#> 3 Region Region
#> 4 Variable Variable
#> 5 Year Year
#> 6 variable Sector Code
#> 7 description Sector Description
#> 8 value Value
# Can leave out value column by providing named vector of labels
pivot(GGDC10S, 1:5, na.rm = TRUE, labels = list("description",
c(variable = "Sector Code", description = "Sector Description"))) |> namlab()
#> Variable Label
#> 1 Country Country
#> 2 Regioncode Region code
#> 3 Region Region
#> 4 Variable Variable
#> 5 Year Year
#> 6 variable Sector Code
#> 7 description Sector Description
#> 8 value <NA>
# Now here is a nice example that is explicit and respects the dataset naming conventions
pivot(GGDC10S, ids = 1:5, na.rm = TRUE,
names = list(variable = "Sectorcode",
value = "Value"),
labels = list(name = "Sector",
new = c(Sectorcode = "GGDC10S Sector Code",
Sector = "Long Sector Description",
Value = "Employment or Value Added"))) |>
namlab(N = TRUE, Nd = TRUE, class = TRUE)
#> Variable Class N Ndist Label
#> 1 Country character 46942 43 Country
#> 2 Regioncode character 46942 6 Region code
#> 3 Region character 46942 6 Region
#> 4 Variable character 46942 2 Variable
#> 5 Year numeric 46942 67 Year
#> 6 Sectorcode factor 46942 11 GGDC10S Sector Code
#> 7 Sector factor 46942 11 Long Sector Description
#> 8 Value numeric 46942 46478 Employment or Value Added
# Note that pivot() currently does not support melting to multiple columns
# But you can tackle the issue with a bit of programming:
wide <- pivot(GGDC10S, c("Country", "Year"), c("AGR", "MAN", "SUM"), "Variable",
how = "wider", na.rm = TRUE)
head(wide)
#> Country Year AGR_VA AGR_EMP MAN_VA MAN_EMP SUM_VA SUM_EMP
#> 1 BWA 1964 16.30154 152.1179 0.7365696 2.420000 37.48229 173.8829
#> 2 BWA 1965 15.72700 153.2971 1.0181992 2.330406 39.34710 178.7637
#> 3 BWA 1966 17.68066 153.8867 0.8038415 1.281642 43.14677 179.7183
#> 4 BWA 1967 19.14591 155.0659 0.9378151 1.041623 41.39519 178.9181
#> 5 BWA 1968 21.09957 156.2451 0.7502521 1.069332 41.14259 181.9292
#> 6 BWA 1969 21.86221 157.4243 2.1396077 2.124402 51.22160 188.0569
library(magrittr)
wide %>% {av(pivot(., 1:2, grep("_VA", names(.))), pivot(gvr(., "_EMP")))} |> head()
#> Country Year variable value variable value
#> 1 BWA 1964 AGR_VA 16.30154 AGR_EMP 152.1179
#> 2 BWA 1965 AGR_VA 15.72700 AGR_EMP 153.2971
#> 3 BWA 1966 AGR_VA 17.68066 AGR_EMP 153.8867
#> 4 BWA 1967 AGR_VA 19.14591 AGR_EMP 155.0659
#> 5 BWA 1968 AGR_VA 21.09957 AGR_EMP 156.2451
#> 6 BWA 1969 AGR_VA 21.86221 AGR_EMP 157.4243
wide %>% {av(av(gv(., 1:2), rm_stub(gvr(., "_VA"), "_VA", pre = FALSE)) |>
pivot(1:2, names = list("Sectorcode", "VA"), labels = "Sector"),
EMP = vec(gvr(., "_EMP")))} |> head()
#> Country Year Sectorcode Sector VA EMP
#> 1 BWA 1964 AGR Agriculture 16.30154 152.1179
#> 2 BWA 1965 AGR Agriculture 15.72700 153.2971
#> 3 BWA 1966 AGR Agriculture 17.68066 153.8867
#> 4 BWA 1967 AGR Agriculture 19.14591 155.0659
#> 5 BWA 1968 AGR Agriculture 21.09957 156.2451
#> 6 BWA 1969 AGR Agriculture 21.86221 157.4243
rm(wide)
# -------------------------------- PIVOT WIDER ---------------------------------
iris_long <- pivot(iris, "Species") # Getting a long frame
head(iris_long)
#> Species variable value
#> 1 setosa Sepal.Length 5.1
#> 2 setosa Sepal.Length 4.9
#> 3 setosa Sepal.Length 4.7
#> 4 setosa Sepal.Length 4.6
#> 5 setosa Sepal.Length 5.0
#> 6 setosa Sepal.Length 5.4
# If 'names'/'values' not supplied, searches for 'variable' and 'value' columns
pivot(iris_long, how = "wider")
#> Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#> 1 setosa 5.0 3.3 1.4 0.2
#> 2 versicolor 5.7 2.8 4.1 1.3
#> 3 virginica 5.9 3.0 5.1 1.8
# But here the records are not identified by 'Species': thus aggregation with last value:
pivot(iris_long, how = "wider", check = TRUE) # issues a warning
#> Warning: duplicates detected: there are 12 unique combinations of id- and name-columns, but the data has 600 rows. This means you have on average 50 duplicates per id-name-combination. If how = 'wider', pivot() will take the last of those duplicates in first-appearance-order. Consider aggregating your data e.g. using collap() before applying pivot().
#> Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#> 1 setosa 5.0 3.3 1.4 0.2
#> 2 versicolor 5.7 2.8 4.1 1.3
#> 3 virginica 5.9 3.0 5.1 1.8
rm(iris_long)
# This works better, these two are inverse operations
wlddev |> pivot(1:8) |> pivot(how = "w") |> head()
#> country iso3c date year decade region income OECD PCGDP
#> 1 Afghanistan AFG 1961-01-01 1960 1960 South Asia Low income FALSE NA
#> 2 Afghanistan AFG 1962-01-01 1961 1960 South Asia Low income FALSE NA
#> 3 Afghanistan AFG 1963-01-01 1962 1960 South Asia Low income FALSE NA
#> 4 Afghanistan AFG 1964-01-01 1963 1960 South Asia Low income FALSE NA
#> 5 Afghanistan AFG 1965-01-01 1964 1960 South Asia Low income FALSE NA
#> 6 Afghanistan AFG 1966-01-01 1965 1960 South Asia Low income FALSE NA
#> LIFEEX GINI ODA POP
#> 1 32.446 NA 116769997 8996973
#> 2 32.962 NA 232080002 9169410
#> 3 33.471 NA 112839996 9351441
#> 4 33.971 NA 237720001 9543205
#> 5 34.463 NA 295920013 9744781
#> 6 34.948 NA 341839996 9956320
# ...but not perfect, we loose labels
namlab(wlddev)
#> Variable
#> 1 country
#> 2 iso3c
#> 3 date
#> 4 year
#> 5 decade
#> 6 region
#> 7 income
#> 8 OECD
#> 9 PCGDP
#> 10 LIFEEX
#> 11 GINI
#> 12 ODA
#> 13 POP
#> Label
#> 1 Country Name
#> 2 Country Code
#> 3 Date Recorded (Fictitious)
#> 4 Year
#> 5 Decade
#> 6 Region
#> 7 Income Level
#> 8 Is OECD Member Country?
#> 9 GDP per capita (constant 2010 US$)
#> 10 Life expectancy at birth, total (years)
#> 11 Gini index (World Bank estimate)
#> 12 Net official development assistance and official aid received (constant 2018 US$)
#> 13 Population, total
wlddev |> pivot(1:8) |> pivot(how = "w") |> namlab()
#> Variable Label
#> 1 country Country Name
#> 2 iso3c Country Code
#> 3 date Date Recorded (Fictitious)
#> 4 year Year
#> 5 decade Decade
#> 6 region Region
#> 7 income Income Level
#> 8 OECD Is OECD Member Country?
#> 9 PCGDP <NA>
#> 10 LIFEEX <NA>
#> 11 GINI <NA>
#> 12 ODA <NA>
#> 13 POP <NA>
# But pivot() supports labels: these are perfect inverse operations
wlddev |> pivot(1:8, labels = "label") |> print(max = 50) |> # Notice the "label" column
pivot(how = "w", labels = "label") |> namlab()
#> country iso3c date year decade region income OECD variable
#> 1 Afghanistan AFG 1961-01-01 1960 1960 South Asia Low income FALSE PCGDP
#> 2 Afghanistan AFG 1962-01-01 1961 1960 South Asia Low income FALSE PCGDP
#> 3 Afghanistan AFG 1963-01-01 1962 1960 South Asia Low income FALSE PCGDP
#> 4 Afghanistan AFG 1964-01-01 1963 1960 South Asia Low income FALSE PCGDP
#> label value
#> 1 GDP per capita (constant 2010 US$) NA
#> 2 GDP per capita (constant 2010 US$) NA
#> 3 GDP per capita (constant 2010 US$) NA
#> 4 GDP per capita (constant 2010 US$) NA
#> [ reached 'max' / getOption("max.print") -- omitted 65876 rows ]
#> Variable
#> 1 country
#> 2 iso3c
#> 3 date
#> 4 year
#> 5 decade
#> 6 region
#> 7 income
#> 8 OECD
#> 9 PCGDP
#> 10 LIFEEX
#> 11 GINI
#> 12 ODA
#> 13 POP
#> Label
#> 1 Country Name
#> 2 Country Code
#> 3 Date Recorded (Fictitious)
#> 4 Year
#> 5 Decade
#> 6 Region
#> 7 Income Level
#> 8 Is OECD Member Country?
#> 9 GDP per capita (constant 2010 US$)
#> 10 Life expectancy at birth, total (years)
#> 11 Gini index (World Bank estimate)
#> 12 Net official development assistance and official aid received (constant 2018 US$)
#> 13 Population, total
# If the data does not have 'variable'/'value' cols: need to specify 'names'/'values'
# Using a single column:
pivot(GGDC10S, c("Country", "Year"), "SUM", "Variable", how = "w") |> head()
#> Country Year VA EMP
#> 1 BWA 1960 NA NA
#> 2 BWA 1961 NA NA
#> 3 BWA 1962 NA NA
#> 4 BWA 1963 NA NA
#> 5 BWA 1964 37.48229 173.8829
#> 6 BWA 1965 39.34710 178.7637
SUM_wide <- pivot(GGDC10S, c("Country", "Year"), "SUM", "Variable", how = "w", na.rm = TRUE)
head(SUM_wide) # na.rm = TRUE here removes all new rows completely missing data
#> Country Year VA EMP
#> 1 BWA 1964 37.48229 173.8829
#> 2 BWA 1965 39.34710 178.7637
#> 3 BWA 1966 43.14677 179.7183
#> 4 BWA 1967 41.39519 178.9181
#> 5 BWA 1968 41.14259 181.9292
#> 6 BWA 1969 51.22160 188.0569
tail(SUM_wide) # But there may still be NA's, notice the NA in the final row
#> Country Year VA EMP
#> 2341 EGY 2008 844222.3 21039.90
#> 2342 EGY 2009 978684.0 21863.86
#> 2343 EGY 2010 1133629.1 22019.88
#> 2344 EGY 2011 1290896.1 22219.39
#> 2345 EGY 2012 1487175.1 22532.56
#> 2346 EGY 2013 1650962.8 NA
# We could use fill to set another value
pivot(GGDC10S, c("Country", "Year"), "SUM", "Variable", how = "w",
na.rm = TRUE, fill = -9999) |> tail()
#> Country Year VA EMP
#> 2341 EGY 2008 844222.3 21039.90
#> 2342 EGY 2009 978684.0 21863.86
#> 2343 EGY 2010 1133629.1 22019.88
#> 2344 EGY 2011 1290896.1 22219.39
#> 2345 EGY 2012 1487175.1 22532.56
#> 2346 EGY 2013 1650962.8 -9999.00
# This will keep the label of "SUM", unless we supply a column with new labels
namlab(SUM_wide)
#> Variable Label
#> 1 Country Country
#> 2 Year Year
#> 3 VA Summation of sector GDP
#> 4 EMP Summation of sector GDP
# Such a column is not available here, but we could use "Variable" twice
pivot(GGDC10S, c("Country", "Year"), "SUM", "Variable", "Variable", how = "w",
na.rm = TRUE) |> namlab()
#> Variable Label
#> 1 Country Country
#> 2 Year Year
#> 3 VA VA
#> 4 EMP EMP
# Alternatively, can of course relabel ex-post
SUM_wide |> relabel(VA = "Value Added", EMP = "Employment") |> namlab()
#> Variable Label
#> 1 Country Country
#> 2 Year Year
#> 3 VA Value Added
#> 4 EMP Employment
rm(SUM_wide)
# Multiple-column pivots
pivot(GGDC10S, c("Country", "Year"), c("AGR", "MAN", "SUM"), "Variable", how = "w",
na.rm = TRUE) |> head()
#> Country Year AGR_VA AGR_EMP MAN_VA MAN_EMP SUM_VA SUM_EMP
#> 1 BWA 1964 16.30154 152.1179 0.7365696 2.420000 37.48229 173.8829
#> 2 BWA 1965 15.72700 153.2971 1.0181992 2.330406 39.34710 178.7637
#> 3 BWA 1966 17.68066 153.8867 0.8038415 1.281642 43.14677 179.7183
#> 4 BWA 1967 19.14591 155.0659 0.9378151 1.041623 41.39519 178.9181
#> 5 BWA 1968 21.09957 156.2451 0.7502521 1.069332 41.14259 181.9292
#> 6 BWA 1969 21.86221 157.4243 2.1396077 2.124402 51.22160 188.0569
# Here we may prefer a transposed column order
pivot(GGDC10S, c("Country", "Year"), c("AGR", "MAN", "SUM"), "Variable", how = "w",
na.rm = TRUE, transpose = "columns") |> head()
#> Country Year AGR_VA MAN_VA SUM_VA AGR_EMP MAN_EMP SUM_EMP
#> 1 BWA 1964 16.30154 0.7365696 37.48229 152.1179 2.420000 173.8829
#> 2 BWA 1965 15.72700 1.0181992 39.34710 153.2971 2.330406 178.7637
#> 3 BWA 1966 17.68066 0.8038415 43.14677 153.8867 1.281642 179.7183
#> 4 BWA 1967 19.14591 0.9378151 41.39519 155.0659 1.041623 178.9181
#> 5 BWA 1968 21.09957 0.7502521 41.14259 156.2451 1.069332 181.9292
#> 6 BWA 1969 21.86221 2.1396077 51.22160 157.4243 2.124402 188.0569
# Can also flip the order of names (independently of columns)
pivot(GGDC10S, c("Country", "Year"), c("AGR", "MAN", "SUM"), "Variable", how = "w",
na.rm = TRUE, transpose = "names") |> head()
#> Country Year VA_AGR EMP_AGR VA_MAN EMP_MAN VA_SUM EMP_SUM
#> 1 BWA 1964 16.30154 152.1179 0.7365696 2.420000 37.48229 173.8829
#> 2 BWA 1965 15.72700 153.2971 1.0181992 2.330406 39.34710 178.7637
#> 3 BWA 1966 17.68066 153.8867 0.8038415 1.281642 43.14677 179.7183
#> 4 BWA 1967 19.14591 155.0659 0.9378151 1.041623 41.39519 178.9181
#> 5 BWA 1968 21.09957 156.2451 0.7502521 1.069332 41.14259 181.9292
#> 6 BWA 1969 21.86221 157.4243 2.1396077 2.124402 51.22160 188.0569
# Can also enable both (complete transposition)
pivot(GGDC10S, c("Country", "Year"), c("AGR", "MAN", "SUM"), "Variable", how = "w",
na.rm = TRUE, transpose = TRUE) |> head() # or tranpose = c("columns", "names")
#> Country Year VA_AGR VA_MAN VA_SUM EMP_AGR EMP_MAN EMP_SUM
#> 1 BWA 1964 16.30154 0.7365696 37.48229 152.1179 2.420000 173.8829
#> 2 BWA 1965 15.72700 1.0181992 39.34710 153.2971 2.330406 178.7637
#> 3 BWA 1966 17.68066 0.8038415 43.14677 153.8867 1.281642 179.7183
#> 4 BWA 1967 19.14591 0.9378151 41.39519 155.0659 1.041623 178.9181
#> 5 BWA 1968 21.09957 0.7502521 41.14259 156.2451 1.069332 181.9292
#> 6 BWA 1969 21.86221 2.1396077 51.22160 157.4243 2.124402 188.0569
# Finally, here is a nice, simple way to reshape the entire dataset.
pivot(GGDC10S, values = 6:16, names = "Variable", na.rm = TRUE, how = "w") |>
namlab(N = TRUE, Nd = TRUE, class = TRUE)
#> Variable Class N Ndist
#> 1 Country character 2346 43
#> 2 Regioncode character 2346 6
#> 3 Region character 2346 6
#> 4 Year numeric 2346 67
#> 5 AGR_VA numeric 2139 2135
#> 6 AGR_EMP numeric 2225 2219
#> 7 MIN_VA numeric 2139 2072
#> 8 MIN_EMP numeric 2216 2153
#> 9 MAN_VA numeric 2139 2139
#> 10 MAN_EMP numeric 2216 2214
#> 11 PU_VA numeric 2139 2097
#> 12 PU_EMP numeric 2215 2141
#> 13 CON_VA numeric 2139 2130
#> 14 CON_EMP numeric 2216 2209
#> 15 WRT_VA numeric 2139 2132
#> 16 WRT_EMP numeric 2216 2212
#> 17 TRA_VA numeric 2139 2131
#> 18 TRA_EMP numeric 2216 2203
#> 19 FIRE_VA numeric 2139 2133
#> 20 FIRE_EMP numeric 2216 2216
#> 21 GOV_VA numeric 1702 1698
#> 22 GOV_EMP numeric 1780 1772
#> 23 OTH_VA numeric 2139 2132
#> 24 OTH_EMP numeric 2109 2106
#> 25 SUM_VA numeric 2139 2139
#> 26 SUM_EMP numeric 2225 2225
#> Label
#> 1 Country
#> 2 Region code
#> 3 Region
#> 4 Year
#> 5 Agriculture
#> 6 Agriculture
#> 7 Mining
#> 8 Mining
#> 9 Manufacturing
#> 10 Manufacturing
#> 11 Utilities
#> 12 Utilities
#> 13 Construction
#> 14 Construction
#> 15 Trade, restaurants and hotels
#> 16 Trade, restaurants and hotels
#> 17 Transport, storage and communication
#> 18 Transport, storage and communication
#> 19 Finance, insurance, real estate and business services
#> 20 Finance, insurance, real estate and business services
#> 21 Government services
#> 22 Government services
#> 23 Community, social and personal services
#> 24 Community, social and personal services
#> 25 Summation of sector GDP
#> 26 Summation of sector GDP
# -------------------------------- PIVOT RECAST ---------------------------------
# Look at the data again
head(GGDC10S)
#> Country Regioncode Region Variable Year AGR MIN
#> 1 BWA SSA Sub-saharan Africa VA 1960 NA NA
#> 2 BWA SSA Sub-saharan Africa VA 1961 NA NA
#> 3 BWA SSA Sub-saharan Africa VA 1962 NA NA
#> 4 BWA SSA Sub-saharan Africa VA 1963 NA NA
#> 5 BWA SSA Sub-saharan Africa VA 1964 16.30154 3.494075
#> 6 BWA SSA Sub-saharan Africa VA 1965 15.72700 2.495768
#> MAN PU CON WRT TRA FIRE GOV OTH
#> 1 NA NA NA NA NA NA NA NA
#> 2 NA NA NA NA NA NA NA NA
#> 3 NA NA NA NA NA NA NA NA
#> 4 NA NA NA NA NA NA NA NA
#> 5 0.7365696 0.1043936 0.6600454 6.243732 1.658928 1.119194 4.822485 2.341328
#> 6 1.0181992 0.1350976 1.3462312 7.064825 1.939007 1.246789 5.695848 2.678338
#> SUM
#> 1 NA
#> 2 NA
#> 3 NA
#> 4 NA
#> 5 37.48229
#> 6 39.34710
# Let's stack the sectors and instead create variable columns
pivot(GGDC10S, .c(Country, Regioncode, Region, Year),
names = list("Variable", "Sectorcode"), how = "r") |> head()
#> Country Regioncode Region Year Sectorcode VA EMP
#> 1 BWA SSA Sub-saharan Africa 1960 AGR NA NA
#> 2 BWA SSA Sub-saharan Africa 1961 AGR NA NA
#> 3 BWA SSA Sub-saharan Africa 1962 AGR NA NA
#> 4 BWA SSA Sub-saharan Africa 1963 AGR NA NA
#> 5 BWA SSA Sub-saharan Africa 1964 AGR 16.30154 152.1179
#> 6 BWA SSA Sub-saharan Africa 1965 AGR 15.72700 153.2971
# Same thing (a bit easier)
pivot(GGDC10S, values = 6:16, names = list("Variable", "Sectorcode"), how = "r") |> head()
#> Country Regioncode Region Year Sectorcode VA EMP
#> 1 BWA SSA Sub-saharan Africa 1960 AGR NA NA
#> 2 BWA SSA Sub-saharan Africa 1961 AGR NA NA
#> 3 BWA SSA Sub-saharan Africa 1962 AGR NA NA
#> 4 BWA SSA Sub-saharan Africa 1963 AGR NA NA
#> 5 BWA SSA Sub-saharan Africa 1964 AGR 16.30154 152.1179
#> 6 BWA SSA Sub-saharan Africa 1965 AGR 15.72700 153.2971
# Removing missing values
pivot(GGDC10S, values = 6:16, names = list("Variable", "Sectorcode"), how = "r",
na.rm = TRUE) |> head()
#> Country Regioncode Region Year Sectorcode VA EMP
#> 1 BWA SSA Sub-saharan Africa 1960 AGR NA NA
#> 2 BWA SSA Sub-saharan Africa 1961 AGR NA NA
#> 3 BWA SSA Sub-saharan Africa 1962 AGR NA NA
#> 4 BWA SSA Sub-saharan Africa 1963 AGR NA NA
#> 5 BWA SSA Sub-saharan Africa 1964 AGR 16.30154 152.1179
#> 6 BWA SSA Sub-saharan Africa 1965 AGR 15.72700 153.2971
# Saving Labels
pivot(GGDC10S, values = 6:16, names = list("Variable", "Sectorcode"),
labels = list(to = "Sector"), how = "r", na.rm = TRUE) |> head()
#> Country Regioncode Region Year Sectorcode Sector VA
#> 1 BWA SSA Sub-saharan Africa 1960 AGR Agriculture NA
#> 2 BWA SSA Sub-saharan Africa 1961 AGR Agriculture NA
#> 3 BWA SSA Sub-saharan Africa 1962 AGR Agriculture NA
#> 4 BWA SSA Sub-saharan Africa 1963 AGR Agriculture NA
#> 5 BWA SSA Sub-saharan Africa 1964 AGR Agriculture 16.30154
#> 6 BWA SSA Sub-saharan Africa 1965 AGR Agriculture 15.72700
#> EMP
#> 1 NA
#> 2 NA
#> 3 NA
#> 4 NA
#> 5 152.1179
#> 6 153.2971
# Supplying new labels for generated columns: as complete as it gets
pivot(GGDC10S, values = 6:16, names = list("Variable", "Sectorcode"),
labels = list(to = "Sector",
new = c(Sectorcode = "GGDC10S Sector Code",
Sector = "Long Sector Description",
VA = "Value Added",
EMP = "Employment")), how = "r", na.rm = TRUE) |>
namlab(N = TRUE, Nd = TRUE, class = TRUE)
#> Variable Class N Ndist Label
#> 1 Country character 27852 43 Country
#> 2 Regioncode character 27852 6 Region code
#> 3 Region character 27852 6 Region
#> 4 Year numeric 27852 67 Year
#> 5 Sectorcode factor 27852 11 GGDC10S Sector Code
#> 6 Sector factor 27852 11 Long Sector Description
#> 7 VA numeric 23092 22915 Value Added
#> 8 EMP numeric 23850 23610 Employment
# Now another (slightly unconventional) use case here is data transposition
# Let's get the data for Botswana
BWA <- GGDC10S |> fsubset(Country == "BWA", Variable, Year, AGR:SUM)
head(BWA)
#> Variable Year AGR MIN MAN PU CON WRT
#> 1 VA 1960 NA NA NA NA NA NA
#> 2 VA 1961 NA NA NA NA NA NA
#> 3 VA 1962 NA NA NA NA NA NA
#> 4 VA 1963 NA NA NA NA NA NA
#> 5 VA 1964 16.30154 3.494075 0.7365696 0.1043936 0.6600454 6.243732
#> 6 VA 1965 15.72700 2.495768 1.0181992 0.1350976 1.3462312 7.064825
#> TRA FIRE GOV OTH SUM
#> 1 NA NA NA NA NA
#> 2 NA NA NA NA NA
#> 3 NA NA NA NA NA
#> 4 NA NA NA NA NA
#> 5 1.658928 1.119194 4.822485 2.341328 37.48229
#> 6 1.939007 1.246789 5.695848 2.678338 39.34710
# By supplying no ids or values, we are simply requesting a transpose operation
pivot(BWA, names = list(from = c("Variable", "Year"), to = "Sectorcode"), how = "r")
#> Sectorcode VA_1960 VA_1961 VA_1962 VA_1963 VA_1964 VA_1965 VA_1966
#> 1 AGR NA NA NA NA 16.3015438 15.7269971 17.6806614
#> 2 MIN NA NA NA NA 3.4940754 2.4957682 1.9703433
#> 3 MAN NA NA NA NA 0.7365696 1.0181992 0.8038415
#> 4 PU NA NA NA NA 0.1043936 0.1350976 0.2026464
#> 5 CON NA NA NA NA 0.6600454 1.3462312 1.3462312
#> 6 WRT NA NA NA NA 6.2437317 7.0648252 8.2710148
#> 7 TRA NA NA NA NA 1.6589283 1.9390071 2.1544523
#> 8 FIRE NA NA NA NA 1.1191939 1.2467893 1.3581926
#> 9 GOV NA NA NA NA 4.8224846 5.6958480 6.3659477
#> 10 OTH NA NA NA NA 2.3413283 2.6783377 2.9934362
#> 11 SUM NA NA NA NA 37.4822946 39.3471004 43.1467674
#> VA_1967 VA_1968 VA_1969 VA_1970 VA_1971 VA_1972 VA_1973
#> 1 19.1459095 21.0995738 21.8622090 23.133268 24.794879 34.729387 46.402436
#> 2 2.2987338 1.8389871 5.2445187 10.182540 9.004610 12.874929 17.400584
#> 3 0.9378151 0.7502521 2.1396077 4.154173 6.720161 9.420786 11.932996
#> 4 0.2026464 0.2026464 0.5779174 1.122060 1.411155 2.017692 2.726929
#> 5 0.8974874 1.2180187 3.4736088 6.744214 9.135153 12.466519 15.535850
#> 6 4.3078202 5.1693843 5.7520247 6.743753 9.018228 12.527254 16.776522
#> 7 1.7235619 2.4417126 2.7169176 3.185352 3.976806 5.631436 7.712533
#> 8 1.5366297 1.0330284 1.2329973 1.534817 2.165369 3.430586 5.838864
#> 9 7.0360475 5.0257482 5.5921995 6.556372 8.500321 11.885096 16.150139
#> 10 3.3085348 2.3632391 2.6295994 3.082979 3.842922 5.373150 7.301339
#> 11 41.3951863 41.1425906 51.2216000 66.439527 78.569605 110.356837 147.778192
#> VA_1974 VA_1975 VA_1976 VA_1977 VA_1978 VA_1979 VA_1980
#> 1 54.722587 61.299821 65.77234 74.366593 76.295915 81.29461 87.95954
#> 2 19.247684 21.653645 44.63057 55.577689 74.464479 156.86863 288.35437
#> 3 15.575701 23.132228 31.45983 38.091070 36.703136 64.77024 43.64280
#> 4 3.016397 6.307011 10.14606 8.409348 9.140596 10.51169 13.71089
#> 5 23.187680 23.187680 30.34010 32.185885 46.029276 49.60549 67.02509
#> 6 21.647018 26.722870 35.38168 43.741905 48.967047 60.31307 94.05256
#> 7 8.977434 8.977434 17.47607 17.356373 21.785240 19.87005 20.46855
#> 8 7.762307 11.325868 12.61923 14.674671 15.376212 21.05076 31.28722
#> 9 19.857846 27.028735 40.17536 52.218780 58.102586 78.05237 92.48608
#> 10 8.977562 10.148548 16.78414 12.880850 15.222822 18.73578 20.29710
#> 11 182.972216 219.783842 304.78538 349.503163 402.087310 561.07268 759.28421
#> VA_1981 VA_1982 VA_1983 VA_1984 VA_1985 VA_1986 VA_1987
#> 1 95.67683 99.09699 89.18729 90.76583 104.18339 116.11010 126.19520
#> 2 290.27914 242.52082 441.01257 561.07000 905.96445 1364.05934 1479.42515
#> 3 74.48578 107.79618 119.36230 124.29717 133.55007 191.53485 258.92674
#> 4 17.64135 19.92650 27.14757 29.52413 39.21316 52.74124 65.99510
#> 5 79.59950 61.14165 66.90972 109.36279 112.36219 110.74713 152.62339
#> 6 96.44120 115.55029 115.69958 131.37500 169.44390 245.88026 270.06521
#> 7 26.09441 29.92478 45.00687 49.55544 59.49046 79.59992 70.74218
#> 8 28.81211 32.94948 37.97006 47.30470 62.99555 88.29200 142.35213
#> 9 124.11154 143.69358 176.05451 206.48482 252.54400 299.43058 393.75534
#> 10 30.05532 34.34893 57.37833 40.20386 65.57524 83.92069 112.02436
#> 11 863.19717 886.94921 1175.72881 1389.94375 1905.32239 2632.31611 3072.10481
#> VA_1988 VA_1989 VA_1990 VA_1991 VA_1992 VA_1993 VA_1994
#> 1 235.02650 252.3991 280.7167 303.1157 333.4364 404.5488 425.4000
#> 2 2048.07392 2556.1296 2493.1152 2646.9495 2690.9391 2624.9283 3369.7000
#> 3 296.70938 377.8317 423.2812 472.6488 537.4274 567.3420 557.5000
#> 4 97.80438 122.0662 136.3214 160.6079 178.4532 219.2183 255.1000
#> 5 213.64967 335.4749 497.1128 580.0876 678.7320 634.2797 764.3000
#> 6 317.98723 470.8669 722.5832 806.7509 725.2577 771.8253 1030.5000
#> 7 117.18544 155.5311 182.9778 232.7884 285.1403 349.7458 424.6000
#> 8 176.62456 248.7809 324.3974 432.6965 517.2141 673.2540 907.3994
#> 9 517.49914 679.2530 818.2491 1073.2626 1234.0119 1487.1932 1773.1000
#> 10 136.61507 179.3166 236.7822 286.5255 343.8907 409.2927 475.2000
#> 11 4157.17530 5377.6500 6115.5369 6995.4333 7524.5030 8141.6281 9982.7994
#> VA_1995 VA_1996 VA_1997 VA_1998 VA_1999 VA_2000 VA_2001
#> 1 608.200 594.800 649.300 641.100 758.500 825.300 830.600
#> 2 3975.100 5175.600 5923.300 5932.200 7986.700 10024.100 10418.200
#> 3 698.100 904.800 1041.000 1258.800 1410.900 1661.700 1872.700
#> 4 270.500 289.700 329.400 401.900 474.800 512.600 570.300
#> 5 812.500 950.400 1081.100 1222.400 1489.900 1474.600 1494.000
#> 6 1275.700 1731.600 1822.700 2079.500 2738.100 2968.800 3428.500
#> 7 491.700 518.900 626.200 699.000 848.500 969.200 1049.900
#> 8 1099.148 1347.386 1441.028 1760.334 2022.732 2477.634 2876.575
#> 9 1995.300 2223.400 2760.700 3238.600 3925.100 4313.500 4788.500
#> 10 560.200 650.700 698.600 771.000 902.100 1042.200 1283.300
#> 11 11786.448 14387.286 16373.328 18004.834 22557.332 26269.634 28612.575
#> VA_2002 VA_2003 VA_2004 VA_2005 VA_2006 VA_2007 VA_2008
#> 1 834.800 1012.200 949.900 927.900 1210.700 1504.700 1887.500
#> 2 10090.300 9725.800 10801.500 16105.100 19019.000 19567.700 19789.800
#> 3 2084.600 2121.400 2334.800 2512.000 3123.100 4206.600 4278.900
#> 4 680.000 776.700 806.800 714.900 785.100 751.500 654.100
#> 5 2276.200 2106.100 2376.300 2417.800 2838.000 3544.100 3726.900
#> 6 4105.600 4813.600 5116.300 5498.900 7032.300 8243.600 10047.100
#> 7 1155.900 1255.300 1449.400 1825.000 2250.500 2697.300 3303.800
#> 8 3216.772 3607.209 3988.915 4471.827 4572.095 5359.804 6566.731
#> 9 5177.400 5684.400 6151.800 7195.500 7818.500 8887.400 10899.500
#> 10 1511.000 1745.500 2110.200 2544.400 2938.900 3255.800 3847.400
#> 11 31132.572 32848.209 36085.915 44213.327 51588.195 58018.504 65001.731
#> VA_2009 VA_2010 VA_2011 EMP_1960 EMP_1961 EMP_1962 EMP_1963 EMP_1964
#> 1 2071.000 2717.300 NA NA NA NA NA 152.117873
#> 2 10063.200 22868.200 NA NA NA NA NA 1.940000
#> 3 4662.300 5548.300 NA NA NA NA NA 2.420000
#> 4 353.000 310.300 NA NA NA NA NA 0.120000
#> 5 4495.000 5056.100 NA NA NA NA NA 2.704000
#> 6 11162.800 13084.300 NA NA NA NA NA 2.468000
#> 7 3992.900 4470.800 NA NA NA NA NA 2.315000
#> 8 6932.062 8262.298 NA NA NA NA NA 1.209227
#> 9 12353.800 14477.100 NA NA NA NA NA 4.514194
#> 10 4593.300 5242.700 NA NA NA NA NA 4.074579
#> 11 60679.362 82037.398 NA NA NA NA NA 173.882873
#> EMP_1965 EMP_1966 EMP_1967 EMP_1968 EMP_1969 EMP_1970
#> 1 153.297081 153.886685 155.065894 156.2451021 157.4243104 158.6035187
#> 2 1.326332 1.002232 1.119163 0.7855491 2.0314103 3.5863292
#> 3 2.330406 1.281642 1.041623 1.0693316 2.1244019 2.8733231
#> 4 0.149767 0.216655 0.208944 0.2015074 0.3562819 0.4915637
#> 5 4.681785 3.974389 2.249251 1.7135639 3.2392029 4.3790547
#> 6 2.756294 3.184975 1.637299 2.4563747 2.4775140 2.6505767
#> 7 2.425857 2.416490 1.733155 2.3039351 2.1119894 2.0534152
#> 8 1.348823 1.547351 1.755430 1.8673465 1.9586299 2.1791274
#> 9 5.547749 6.451645 7.419680 8.0010383 8.5073386 9.5949752
#> 10 4.899574 5.756197 6.687656 7.2854888 7.8258165 8.9166844
#> 11 178.763667 179.718260 178.918095 181.9292374 188.0568956 195.3285682
#> EMP_1971 EMP_1972 EMP_1973 EMP_1974 EMP_1975 EMP_1976
#> 1 159.193123 160.3723312 160.9619353 162.1411436 163.320352 164.499560
#> 2 2.891000 3.2687736 6.6978908 7.5852816 8.151084 9.558774
#> 3 3.238000 3.6346173 3.6274420 3.8977354 4.219898 4.348306
#> 4 0.472000 0.5787263 0.5672389 0.8981206 1.089887 1.232600
#> 5 4.268000 6.1418478 6.9837288 7.9539394 9.033842 6.265076
#> 6 3.253000 4.5259230 5.1368381 5.8168143 6.571146 6.973752
#> 7 2.138000 2.5544856 2.7300810 2.9717791 3.168039 2.892939
#> 8 3.223987 3.8146540 3.9094511 4.0301845 2.319820 3.058496
#> 9 12.035542 15.2624933 16.9292461 18.4245755 22.789741 25.525133
#> 10 10.863458 14.3905090 15.8848432 17.2043175 21.177466 23.604634
#> 11 201.576110 214.5443611 223.4286952 230.9238916 241.841274 247.959269
#> EMP_1977 EMP_1978 EMP_1979 EMP_1980 EMP_1981 EMP_1982 EMP_1983
#> 1 166.268373 166.857977 168.037185 169.668614 171.315882 173.786784 176.257687
#> 2 9.392415 7.814863 10.238216 11.379374 11.204000 11.029531 11.320866
#> 3 3.917193 3.854094 4.470696 4.224188 4.480000 5.247279 7.435863
#> 4 1.530302 1.894643 1.994766 2.206961 2.404000 3.454277 3.150635
#> 5 7.192163 9.772114 12.339449 14.780392 17.085000 15.939162 11.731486
#> 6 6.880771 7.841330 7.234454 7.980734 12.129000 13.231197 12.263000
#> 7 2.353839 2.266344 2.321210 3.056021 3.122000 3.077241 3.369891
#> 8 2.733101 2.999630 3.723425 4.750890 5.214103 6.065385 6.384615
#> 9 28.635466 30.833339 33.010730 34.620043 37.593000 40.091546 41.441448
#> 10 26.352884 28.238336 30.086267 31.400418 33.932000 34.998768 34.989067
#> 11 255.256508 262.372669 273.456398 284.067636 298.478985 306.921170 308.344558
#> EMP_1984 EMP_1985 EMP_1986 EMP_1987 EMP_1988 EMP_1989 EMP_1990
#> 1 177.904955 179.552223 177.904955 175.434053 173.786784 171.315882 169.668614
#> 2 11.935947 11.758907 12.227955 11.551518 12.527106 12.848473 13.860261
#> 3 7.504686 8.142312 10.446625 13.105004 15.221845 21.452605 24.447642
#> 4 3.502551 3.514131 3.906649 4.538445 5.010975 4.831964 5.103095
#> 5 14.143599 15.278827 18.978764 24.411207 33.435722 42.715139 50.760958
#> 6 14.588080 14.831494 16.951628 21.061798 23.733898 28.589722 31.915662
#> 7 4.937480 5.316294 4.941921 6.745154 8.262959 7.932733 9.596445
#> 8 6.597436 7.235897 7.874359 10.428205 11.917949 13.939744 15.642308
#> 9 42.497732 47.534673 50.825891 53.814688 56.772852 57.459720 60.724583
#> 10 34.702493 37.540748 38.821731 39.754675 40.562580 39.705060 40.583022
#> 11 318.314959 330.705506 342.880476 360.844746 381.232669 400.791043 422.302590
#> EMP_1991 EMP_1992 EMP_1993 EMP_1994 EMP_1995 EMP_1996 EMP_1997
#> 1 172.96315 178.728589 183.67039 189.435831 191.906734 193.554002 210.318261
#> 2 13.33600 14.600977 15.00901 15.063291 14.971791 15.727476 15.213835
#> 3 27.54800 23.452808 22.56697 24.292406 26.970992 27.449520 28.751355
#> 4 6.41600 6.277686 5.90611 5.342814 5.058743 5.296536 4.627102
#> 5 58.05000 53.198114 50.62818 45.603518 41.112828 43.357958 36.917652
#> 6 35.19400 35.327890 38.91100 44.347883 47.259490 50.247230 47.216240
#> 7 11.49500 11.909315 11.35032 11.387581 11.289557 12.110658 11.465007
#> 8 18.19615 20.150400 21.43435 21.062769 23.071057 24.271420 22.504751
#> 9 64.78700 70.709309 77.69099 83.064992 88.523306 91.681347 96.932606
#> 10 41.87600 40.679761 39.78294 37.858970 35.911444 33.104018 31.152586
#> 11 449.86130 455.034851 466.95027 477.460056 486.075942 496.800165 505.099394
#> EMP_1998 EMP_1999 EMP_2000 EMP_2001 EMP_2002 EMP_2003 EMP_2004
#> 1 225.357717 227.241298 229.113937 221.2161 223.902900 226.57469 229.231568
#> 2 15.452817 15.148034 14.612977 13.0990 12.995778 12.67912 13.664546
#> 3 28.763403 35.483313 38.497608 38.9680 37.335145 36.10297 39.686649
#> 4 4.520656 4.253078 4.319963 3.8370 4.310157 4.00944 3.710169
#> 5 48.635969 54.258533 54.824475 58.5720 53.201698 49.30083 40.955891
#> 6 52.308431 56.791891 64.047084 73.8370 77.991895 81.97688 80.786189
#> 7 11.898238 14.068847 14.596544 15.2130 14.759658 14.43165 16.656916
#> 8 22.534654 25.807738 28.908955 30.0610 29.438469 29.76693 31.771467
#> 9 108.224346 112.851223 118.288170 121.8490 122.145965 122.02293 124.244844
#> 10 31.780612 32.097085 30.822666 35.0640 34.778895 35.77129 25.112150
#> 11 549.476843 578.001041 598.032379 611.7161 610.860561 612.63673 605.820388
#> EMP_2005 EMP_2006 EMP_2007 EMP_2008 EMP_2009 EMP_2010 EMP_2011
#> 1 232.678712 236.10700 240.925510 245.744020 250.562531 254.577956 NA
#> 2 12.615744 14.17300 14.509275 14.378227 13.118035 13.140701 NA
#> 3 35.344923 35.97300 45.445021 41.457063 41.131670 43.347542 NA
#> 4 3.510698 4.16300 3.979627 4.265959 4.016108 3.934676 NA
#> 5 32.707299 27.58700 28.033913 22.847773 21.009060 18.076307 NA
#> 6 86.804664 92.06800 104.178150 119.639956 120.569064 128.852311 NA
#> 7 16.465525 16.05000 17.911676 18.984046 20.900167 20.965808 NA
#> 8 32.081938 33.67900 37.574806 41.879506 42.154365 46.722958 NA
#> 9 114.214646 117.40400 119.065856 114.632189 114.213746 112.521872 NA
#> 10 31.329916 29.42665 28.105865 28.498881 27.889174 26.216056 NA
#> 11 597.754064 606.63065 639.729699 652.327621 655.563919 668.356186 NA
# Same with labels
pivot(BWA, names = list(from = c("Variable", "Year"), to = "Sectorcode"),
labels = list(to = "Sector"), how = "r")
#> Sectorcode Sector VA_1960
#> 1 AGR Agriculture NA
#> 2 MIN Mining NA
#> 3 MAN Manufacturing NA
#> 4 PU Utilities NA
#> 5 CON Construction NA
#> 6 WRT Trade, restaurants and hotels NA
#> 7 TRA Transport, storage and communication NA
#> 8 FIRE Finance, insurance, real estate and business services NA
#> 9 GOV Government services NA
#> 10 OTH Community, social and personal services NA
#> 11 SUM Summation of sector GDP NA
#> VA_1961 VA_1962 VA_1963 VA_1964 VA_1965 VA_1966 VA_1967
#> 1 NA NA NA 16.3015438 15.7269971 17.6806614 19.1459095
#> 2 NA NA NA 3.4940754 2.4957682 1.9703433 2.2987338
#> 3 NA NA NA 0.7365696 1.0181992 0.8038415 0.9378151
#> 4 NA NA NA 0.1043936 0.1350976 0.2026464 0.2026464
#> 5 NA NA NA 0.6600454 1.3462312 1.3462312 0.8974874
#> 6 NA NA NA 6.2437317 7.0648252 8.2710148 4.3078202
#> 7 NA NA NA 1.6589283 1.9390071 2.1544523 1.7235619
#> 8 NA NA NA 1.1191939 1.2467893 1.3581926 1.5366297
#> 9 NA NA NA 4.8224846 5.6958480 6.3659477 7.0360475
#> 10 NA NA NA 2.3413283 2.6783377 2.9934362 3.3085348
#> 11 NA NA NA 37.4822946 39.3471004 43.1467674 41.3951863
#> VA_1968 VA_1969 VA_1970 VA_1971 VA_1972 VA_1973 VA_1974
#> 1 21.0995738 21.8622090 23.133268 24.794879 34.729387 46.402436 54.722587
#> 2 1.8389871 5.2445187 10.182540 9.004610 12.874929 17.400584 19.247684
#> 3 0.7502521 2.1396077 4.154173 6.720161 9.420786 11.932996 15.575701
#> 4 0.2026464 0.5779174 1.122060 1.411155 2.017692 2.726929 3.016397
#> 5 1.2180187 3.4736088 6.744214 9.135153 12.466519 15.535850 23.187680
#> 6 5.1693843 5.7520247 6.743753 9.018228 12.527254 16.776522 21.647018
#> 7 2.4417126 2.7169176 3.185352 3.976806 5.631436 7.712533 8.977434
#> 8 1.0330284 1.2329973 1.534817 2.165369 3.430586 5.838864 7.762307
#> 9 5.0257482 5.5921995 6.556372 8.500321 11.885096 16.150139 19.857846
#> 10 2.3632391 2.6295994 3.082979 3.842922 5.373150 7.301339 8.977562
#> 11 41.1425906 51.2216000 66.439527 78.569605 110.356837 147.778192 182.972216
#> VA_1975 VA_1976 VA_1977 VA_1978 VA_1979 VA_1980 VA_1981
#> 1 61.299821 65.77234 74.366593 76.295915 81.29461 87.95954 95.67683
#> 2 21.653645 44.63057 55.577689 74.464479 156.86863 288.35437 290.27914
#> 3 23.132228 31.45983 38.091070 36.703136 64.77024 43.64280 74.48578
#> 4 6.307011 10.14606 8.409348 9.140596 10.51169 13.71089 17.64135
#> 5 23.187680 30.34010 32.185885 46.029276 49.60549 67.02509 79.59950
#> 6 26.722870 35.38168 43.741905 48.967047 60.31307 94.05256 96.44120
#> 7 8.977434 17.47607 17.356373 21.785240 19.87005 20.46855 26.09441
#> 8 11.325868 12.61923 14.674671 15.376212 21.05076 31.28722 28.81211
#> 9 27.028735 40.17536 52.218780 58.102586 78.05237 92.48608 124.11154
#> 10 10.148548 16.78414 12.880850 15.222822 18.73578 20.29710 30.05532
#> 11 219.783842 304.78538 349.503163 402.087310 561.07268 759.28421 863.19717
#> VA_1982 VA_1983 VA_1984 VA_1985 VA_1986 VA_1987 VA_1988
#> 1 99.09699 89.18729 90.76583 104.18339 116.11010 126.19520 235.02650
#> 2 242.52082 441.01257 561.07000 905.96445 1364.05934 1479.42515 2048.07392
#> 3 107.79618 119.36230 124.29717 133.55007 191.53485 258.92674 296.70938
#> 4 19.92650 27.14757 29.52413 39.21316 52.74124 65.99510 97.80438
#> 5 61.14165 66.90972 109.36279 112.36219 110.74713 152.62339 213.64967
#> 6 115.55029 115.69958 131.37500 169.44390 245.88026 270.06521 317.98723
#> 7 29.92478 45.00687 49.55544 59.49046 79.59992 70.74218 117.18544
#> 8 32.94948 37.97006 47.30470 62.99555 88.29200 142.35213 176.62456
#> 9 143.69358 176.05451 206.48482 252.54400 299.43058 393.75534 517.49914
#> 10 34.34893 57.37833 40.20386 65.57524 83.92069 112.02436 136.61507
#> 11 886.94921 1175.72881 1389.94375 1905.32239 2632.31611 3072.10481 4157.17530
#> VA_1989 VA_1990 VA_1991 VA_1992 VA_1993 VA_1994 VA_1995
#> 1 252.3991 280.7167 303.1157 333.4364 404.5488 425.4000 608.200
#> 2 2556.1296 2493.1152 2646.9495 2690.9391 2624.9283 3369.7000 3975.100
#> 3 377.8317 423.2812 472.6488 537.4274 567.3420 557.5000 698.100
#> 4 122.0662 136.3214 160.6079 178.4532 219.2183 255.1000 270.500
#> 5 335.4749 497.1128 580.0876 678.7320 634.2797 764.3000 812.500
#> 6 470.8669 722.5832 806.7509 725.2577 771.8253 1030.5000 1275.700
#> 7 155.5311 182.9778 232.7884 285.1403 349.7458 424.6000 491.700
#> 8 248.7809 324.3974 432.6965 517.2141 673.2540 907.3994 1099.148
#> 9 679.2530 818.2491 1073.2626 1234.0119 1487.1932 1773.1000 1995.300
#> 10 179.3166 236.7822 286.5255 343.8907 409.2927 475.2000 560.200
#> 11 5377.6500 6115.5369 6995.4333 7524.5030 8141.6281 9982.7994 11786.448
#> VA_1996 VA_1997 VA_1998 VA_1999 VA_2000 VA_2001 VA_2002
#> 1 594.800 649.300 641.100 758.500 825.300 830.600 834.800
#> 2 5175.600 5923.300 5932.200 7986.700 10024.100 10418.200 10090.300
#> 3 904.800 1041.000 1258.800 1410.900 1661.700 1872.700 2084.600
#> 4 289.700 329.400 401.900 474.800 512.600 570.300 680.000
#> 5 950.400 1081.100 1222.400 1489.900 1474.600 1494.000 2276.200
#> 6 1731.600 1822.700 2079.500 2738.100 2968.800 3428.500 4105.600
#> 7 518.900 626.200 699.000 848.500 969.200 1049.900 1155.900
#> 8 1347.386 1441.028 1760.334 2022.732 2477.634 2876.575 3216.772
#> 9 2223.400 2760.700 3238.600 3925.100 4313.500 4788.500 5177.400
#> 10 650.700 698.600 771.000 902.100 1042.200 1283.300 1511.000
#> 11 14387.286 16373.328 18004.834 22557.332 26269.634 28612.575 31132.572
#> VA_2003 VA_2004 VA_2005 VA_2006 VA_2007 VA_2008 VA_2009
#> 1 1012.200 949.900 927.900 1210.700 1504.700 1887.500 2071.000
#> 2 9725.800 10801.500 16105.100 19019.000 19567.700 19789.800 10063.200
#> 3 2121.400 2334.800 2512.000 3123.100 4206.600 4278.900 4662.300
#> 4 776.700 806.800 714.900 785.100 751.500 654.100 353.000
#> 5 2106.100 2376.300 2417.800 2838.000 3544.100 3726.900 4495.000
#> 6 4813.600 5116.300 5498.900 7032.300 8243.600 10047.100 11162.800
#> 7 1255.300 1449.400 1825.000 2250.500 2697.300 3303.800 3992.900
#> 8 3607.209 3988.915 4471.827 4572.095 5359.804 6566.731 6932.062
#> 9 5684.400 6151.800 7195.500 7818.500 8887.400 10899.500 12353.800
#> 10 1745.500 2110.200 2544.400 2938.900 3255.800 3847.400 4593.300
#> 11 32848.209 36085.915 44213.327 51588.195 58018.504 65001.731 60679.362
#> VA_2010 VA_2011 EMP_1960 EMP_1961 EMP_1962 EMP_1963 EMP_1964 EMP_1965
#> 1 2717.300 NA NA NA NA NA 152.117873 153.297081
#> 2 22868.200 NA NA NA NA NA 1.940000 1.326332
#> 3 5548.300 NA NA NA NA NA 2.420000 2.330406
#> 4 310.300 NA NA NA NA NA 0.120000 0.149767
#> 5 5056.100 NA NA NA NA NA 2.704000 4.681785
#> 6 13084.300 NA NA NA NA NA 2.468000 2.756294
#> 7 4470.800 NA NA NA NA NA 2.315000 2.425857
#> 8 8262.298 NA NA NA NA NA 1.209227 1.348823
#> 9 14477.100 NA NA NA NA NA 4.514194 5.547749
#> 10 5242.700 NA NA NA NA NA 4.074579 4.899574
#> 11 82037.398 NA NA NA NA NA 173.882873 178.763667
#> EMP_1966 EMP_1967 EMP_1968 EMP_1969 EMP_1970 EMP_1971
#> 1 153.886685 155.065894 156.2451021 157.4243104 158.6035187 159.193123
#> 2 1.002232 1.119163 0.7855491 2.0314103 3.5863292 2.891000
#> 3 1.281642 1.041623 1.0693316 2.1244019 2.8733231 3.238000
#> 4 0.216655 0.208944 0.2015074 0.3562819 0.4915637 0.472000
#> 5 3.974389 2.249251 1.7135639 3.2392029 4.3790547 4.268000
#> 6 3.184975 1.637299 2.4563747 2.4775140 2.6505767 3.253000
#> 7 2.416490 1.733155 2.3039351 2.1119894 2.0534152 2.138000
#> 8 1.547351 1.755430 1.8673465 1.9586299 2.1791274 3.223987
#> 9 6.451645 7.419680 8.0010383 8.5073386 9.5949752 12.035542
#> 10 5.756197 6.687656 7.2854888 7.8258165 8.9166844 10.863458
#> 11 179.718260 178.918095 181.9292374 188.0568956 195.3285682 201.576110
#> EMP_1972 EMP_1973 EMP_1974 EMP_1975 EMP_1976 EMP_1977
#> 1 160.3723312 160.9619353 162.1411436 163.320352 164.499560 166.268373
#> 2 3.2687736 6.6978908 7.5852816 8.151084 9.558774 9.392415
#> 3 3.6346173 3.6274420 3.8977354 4.219898 4.348306 3.917193
#> 4 0.5787263 0.5672389 0.8981206 1.089887 1.232600 1.530302
#> 5 6.1418478 6.9837288 7.9539394 9.033842 6.265076 7.192163
#> 6 4.5259230 5.1368381 5.8168143 6.571146 6.973752 6.880771
#> 7 2.5544856 2.7300810 2.9717791 3.168039 2.892939 2.353839
#> 8 3.8146540 3.9094511 4.0301845 2.319820 3.058496 2.733101
#> 9 15.2624933 16.9292461 18.4245755 22.789741 25.525133 28.635466
#> 10 14.3905090 15.8848432 17.2043175 21.177466 23.604634 26.352884
#> 11 214.5443611 223.4286952 230.9238916 241.841274 247.959269 255.256508
#> EMP_1978 EMP_1979 EMP_1980 EMP_1981 EMP_1982 EMP_1983 EMP_1984
#> 1 166.857977 168.037185 169.668614 171.315882 173.786784 176.257687 177.904955
#> 2 7.814863 10.238216 11.379374 11.204000 11.029531 11.320866 11.935947
#> 3 3.854094 4.470696 4.224188 4.480000 5.247279 7.435863 7.504686
#> 4 1.894643 1.994766 2.206961 2.404000 3.454277 3.150635 3.502551
#> 5 9.772114 12.339449 14.780392 17.085000 15.939162 11.731486 14.143599
#> 6 7.841330 7.234454 7.980734 12.129000 13.231197 12.263000 14.588080
#> 7 2.266344 2.321210 3.056021 3.122000 3.077241 3.369891 4.937480
#> 8 2.999630 3.723425 4.750890 5.214103 6.065385 6.384615 6.597436
#> 9 30.833339 33.010730 34.620043 37.593000 40.091546 41.441448 42.497732
#> 10 28.238336 30.086267 31.400418 33.932000 34.998768 34.989067 34.702493
#> 11 262.372669 273.456398 284.067636 298.478985 306.921170 308.344558 318.314959
#> EMP_1985 EMP_1986 EMP_1987 EMP_1988 EMP_1989 EMP_1990 EMP_1991
#> 1 179.552223 177.904955 175.434053 173.786784 171.315882 169.668614 172.96315
#> 2 11.758907 12.227955 11.551518 12.527106 12.848473 13.860261 13.33600
#> 3 8.142312 10.446625 13.105004 15.221845 21.452605 24.447642 27.54800
#> 4 3.514131 3.906649 4.538445 5.010975 4.831964 5.103095 6.41600
#> 5 15.278827 18.978764 24.411207 33.435722 42.715139 50.760958 58.05000
#> 6 14.831494 16.951628 21.061798 23.733898 28.589722 31.915662 35.19400
#> 7 5.316294 4.941921 6.745154 8.262959 7.932733 9.596445 11.49500
#> 8 7.235897 7.874359 10.428205 11.917949 13.939744 15.642308 18.19615
#> 9 47.534673 50.825891 53.814688 56.772852 57.459720 60.724583 64.78700
#> 10 37.540748 38.821731 39.754675 40.562580 39.705060 40.583022 41.87600
#> 11 330.705506 342.880476 360.844746 381.232669 400.791043 422.302590 449.86130
#> EMP_1992 EMP_1993 EMP_1994 EMP_1995 EMP_1996 EMP_1997 EMP_1998
#> 1 178.728589 183.67039 189.435831 191.906734 193.554002 210.318261 225.357717
#> 2 14.600977 15.00901 15.063291 14.971791 15.727476 15.213835 15.452817
#> 3 23.452808 22.56697 24.292406 26.970992 27.449520 28.751355 28.763403
#> 4 6.277686 5.90611 5.342814 5.058743 5.296536 4.627102 4.520656
#> 5 53.198114 50.62818 45.603518 41.112828 43.357958 36.917652 48.635969
#> 6 35.327890 38.91100 44.347883 47.259490 50.247230 47.216240 52.308431
#> 7 11.909315 11.35032 11.387581 11.289557 12.110658 11.465007 11.898238
#> 8 20.150400 21.43435 21.062769 23.071057 24.271420 22.504751 22.534654
#> 9 70.709309 77.69099 83.064992 88.523306 91.681347 96.932606 108.224346
#> 10 40.679761 39.78294 37.858970 35.911444 33.104018 31.152586 31.780612
#> 11 455.034851 466.95027 477.460056 486.075942 496.800165 505.099394 549.476843
#> EMP_1999 EMP_2000 EMP_2001 EMP_2002 EMP_2003 EMP_2004 EMP_2005
#> 1 227.241298 229.113937 221.2161 223.902900 226.57469 229.231568 232.678712
#> 2 15.148034 14.612977 13.0990 12.995778 12.67912 13.664546 12.615744
#> 3 35.483313 38.497608 38.9680 37.335145 36.10297 39.686649 35.344923
#> 4 4.253078 4.319963 3.8370 4.310157 4.00944 3.710169 3.510698
#> 5 54.258533 54.824475 58.5720 53.201698 49.30083 40.955891 32.707299
#> 6 56.791891 64.047084 73.8370 77.991895 81.97688 80.786189 86.804664
#> 7 14.068847 14.596544 15.2130 14.759658 14.43165 16.656916 16.465525
#> 8 25.807738 28.908955 30.0610 29.438469 29.76693 31.771467 32.081938
#> 9 112.851223 118.288170 121.8490 122.145965 122.02293 124.244844 114.214646
#> 10 32.097085 30.822666 35.0640 34.778895 35.77129 25.112150 31.329916
#> 11 578.001041 598.032379 611.7161 610.860561 612.63673 605.820388 597.754064
#> EMP_2006 EMP_2007 EMP_2008 EMP_2009 EMP_2010 EMP_2011
#> 1 236.10700 240.925510 245.744020 250.562531 254.577956 NA
#> 2 14.17300 14.509275 14.378227 13.118035 13.140701 NA
#> 3 35.97300 45.445021 41.457063 41.131670 43.347542 NA
#> 4 4.16300 3.979627 4.265959 4.016108 3.934676 NA
#> 5 27.58700 28.033913 22.847773 21.009060 18.076307 NA
#> 6 92.06800 104.178150 119.639956 120.569064 128.852311 NA
#> 7 16.05000 17.911676 18.984046 20.900167 20.965808 NA
#> 8 33.67900 37.574806 41.879506 42.154365 46.722958 NA
#> 9 117.40400 119.065856 114.632189 114.213746 112.521872 NA
#> 10 29.42665 28.105865 28.498881 27.889174 26.216056 NA
#> 11 606.63065 639.729699 652.327621 655.563919 668.356186 NA
# For simple cases, data.table::transpose() will be more efficient, but with multiple
# columns to generate names and/or variable labels to be saved/assigned, pivot() is handy
rm(BWA)