How to pivot a table using R standard package or dplyr?-CodePudding

I have a table:

library("eurostat")
library("ggplot2")
library("dplyr")

d <- get_eurostat("prc_hicp_manr") %>%
  filter(coicop == "CP00")

d2 <- label_eurostat(d) %>%
  filter(time %in% as.Date("2000-02-01"):as.Date("2022-09-30"), 
         geo %in% c("Austria", "Belgium", "Bulgaria", "Croatia", "Cyprus", "Czechia", "Denmark", "Estonia", "Finland", "France", "Germany", "Greece", "Hungary",
                    "Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Poland", "Portugal", "Romania", "Slovakia", "Slovenia", 
                    "Spain", "Sweden"))

I want to create a matrix with dates in rows and countries in columns, how do I do that?

Note, I can't use other than the listed above libraries.

CodePudding user response：

We could first create geo factor class and then use xtabs

d2$geo <- factor(d2$geo)
xtabs(values ~ time   geo, d2)

     geo
time         Austria Belgium Bulgaria Croatia Cyprus Czechia Denmark Estonia Finland France Greece Hungary Ireland Italy Latvia Lithuania Luxembourg Malta Netherlands Poland Portugal
  2000-02-01     1.9     2.1      8.9     3.2    4.7     3.5     2.8     2.9     2.7    1.4    2.6    10.0     4.6   2.4    3.4       0.9        2.6   3.3         1.5   10.5      1.6
  2000-03-01     1.9     2.5      9.6     3.5    5.2     3.8     2.9     3.1     3.2    1.6    2.8     9.8     5.0   2.5    3.2       1.0        3.0   3.4         1.6   10.3      1.4
  2000-04-01     1.8     2.3      9.2     3.9    5.9     3.4     2.9     3.0     2.5    1.3    2.1     9.4     5.1   2.4    3.7       0.9        3.2   3.4         1.7    9.9      1.9
  2000-05-01     1.6     2.4     10.3     3.4    6.0     3.7     2.9     2.8     2.7    1.5    2.6     9.2     5.1   2.5    3.0       0.5        2.9   3.6         2.0    9.8      2.4
  2000-06-01     2.4     3.0     11.6     4.5    5.2     4.1     2.9     3.1     3.1    1.8    2.2     9.3     5.4   2.8    2.4       1.3        4.4   3.3         2.5   10.6      2.8....
.....etc.

CodePudding user response：

You could use pivot_wider if you want a tidyverse solution.

tidyr::pivot_wider(d2[3:5], names_from = geo, values_from = values)
#>          time Austria Belgium Bulgaria Cyprus Czechia Denmark Estonia
#> 1  2022-09-01    11.0    12.1     15.6    9.0    17.8    11.1    24.1
#> 2  2022-08-01     9.3    10.5     15.0    9.6    17.1     9.9    25.2
#> 3  2022-07-01     9.4    10.4     14.9   10.6    17.3     9.6    23.2
#> 4  2022-06-01     8.7    10.5     14.8    9.0    16.6     9.1    22.0
#> 5  2022-05-01     7.7     9.9     13.4    8.8    15.2     8.2    20.1
#> 6  2022-04-01     7.1     9.3     12.1    8.6    13.2     7.4    19.1
#> 7  2022-03-01     6.6     9.3     10.5    6.2    11.9     6.0    14.8
#> 8  2022-02-01     5.5     9.5      8.4    5.8    10.0     5.3    11.6
#> 9  2022-01-01     4.5     8.5      7.7    5.0     8.8     4.9    11.0
#> 10 2021-12-01     3.8     6.6      6.6    4.8     5.4     3.4    12.0
#> ...etc