[R] getting data from a "vertical" table into a "2-dimensional" grid

[Kelly Thompson]

# I think this might be a better example.

# I have data presented in a "vertical" dataframe as shown below in
data_original.
# I want this data in a matrix or "grid", as shown below.
# What I show below seems like one way this can be done.

# My question: Are there easier or better ways to do this, especially
in Base R, and also in R packages?

#create data
set.seed(1)
data_original <- data.frame(year = rep(1990:1999, length  = 50),
category = sample(1:5, size = 50, replace = TRUE),  sales =
sample(0:99999, size = 50 , replace = TRUE) )
dim(data_original)

#remove rows where data_original$year == 1990 & data_original$category
== 5, to ensure there is at least one NA in the "grid"
data_original <- data_original[ (data_original$year == 1990 &
data_original$category == 5) == FALSE, ]
dim(data_original)

#aggregate data
data_aggregate_sum_by_year_and_category <- aggregate(x =
data_original$sales, by = list(year = data_original$year, category =
data_original$category), FUN = sum)
colnames(data_aggregate_sum_by_year_and_category) <- c('year',
'category', 'sum_of_sales')
dim(data_aggregate_sum_by_year_and_category)

data_expanded <- expand.grid(year =
unique(data_aggregate_sum_by_year_and_category$year), category =
unique(data_aggregate_sum_by_year_and_category$category))
dim(data_expanded)
data_expanded <- merge(data_expanded,
data_aggregate_sum_by_year_and_category, all = TRUE)
dim(data_expanded)

mat <- matrix(data = data_expanded$sum_of_sales, nrow =
length(unique(data_expanded$year)), ncol =
length(unique(data_expanded$category)) , byrow = TRUE, dimnames =
list( unique(data_expanded$year), unique(data_expanded$category) ) )


data_original
data_expanded
mat

On Fri, Oct 21, 2022 at 5:03 PM Kelly Thompson <kt1572757 using gmail.com> wrote:
>
> ###
> #I have data presented in a "vertical" data frame as shown below in
> data_original.
> #I want this data in a matrix or "grid", as shown below.
> #What I show below seems like one way this can be done.
>
> #My question: Are there easier or better ways to do this, especially
> in Base R, and also in R packages?
>
> #reproducible example
>
> data_original <- data.frame(year = c('1990', '1999', '1990', '1989'),
> size = c('s', 'l', 'xl', 'xs'),  n = c(99, 33, 3, 4) )
>
> data_expanded <- expand.grid(unique(data_original$year),
> unique(data_original$size), stringsAsFactors = FALSE )
> colnames(data_expanded) <- c('year', 'size')
> data_expanded <- merge(data_expanded, data_original, all = TRUE)
>
> mat <- matrix(data = data_expanded $n, nrow =
> length(unique(data_expanded $year)), ncol =
> length(unique(data_expanded $size)) , byrow = TRUE, dimnames = list(
> unique(data_expanded$year), unique(data_expanded$size) ) )
>
> data_original
> data_expanded
> mat