How to convert a Cross Table to Long Table in R?

Question

Is there a general way to convert a cross table into a long table?

I had made up the following table for illustration, in the real case it has 7 horizontal variables and 9 vertical variables, and all of the variables could repeat, but with only unique combinations of (verticle_var, horiz_var)

cross_table <- matrix(c(c(NA, NA, "Monday", "Monday", "Tuesday", "Tuesday"),
    c(NA, NA, "AM", "PM", "AM", "PM"),
    c("Store1", "Item1", 1, 2, 1, 3),
    c("Store1", "Item3", 2, 5, 1, 6),
    c("Store2", "Item1", 3, 8, 1, 3),
    c("Store2", "Item2", 5, 2, 5, 0)), ncol = 6)

		Monday	Monday	Tuesday	Tuesday
		AM	PM	AM	PM
Store1	Item1	1	2	1	3
Store1	Item3	2	5	1	6
Store2	Item1	3	8	1	3
Store2	Item2	5	2	5	0

I want to convert it into a long table that looks like this:

t(matrix(c(
    c("Store1", "Item1", "Monday", "AM", 1),
    c("Store1", "Item1", "Monday", "PM", 1),
    c("Store1", "Item1", "Tuesday", "AM", 1),
    c("Store1", "Item1", "Tuesday", "PM", 1),
    c("Store1", "Item2", "Monday", "AM", NA),
    c("Store1", "Item2", "Monday", "PM", NA),
    c("Store1", "Item2", "Tuesday", "AM", NA),
    c("Store1", "Item2", "Tuesday", "PM", NA),
    c("Store1", "Item3", "Monday", "AM", 2),
    c("Store1", "Item3", "Monday", "PM", 5),
    c("Store1", "Item3", "Tuesday", "AM", 1),
    c("Store1", "Item3", "Tuesday", "PM", 6),
    c("Store2", "Item1", "Monday", "AM", 3),
    c("Store2", "Item1", "Monday", "PM", 8),
    c("Store2", "Item1", "Tuesday", "AM", 1),
    c("Store2", "Item1", "Tuesday", "PM", 3),
    c("Store2", "Item2", "Monday", "AM", 5),
    c("Store2", "Item2", "Monday", "PM", 2),
    c("Store2", "Item2", "Tuesday", "AM", 5),
    c("Store2", "Item2", "Tuesday", "PM", 0),
    c("Store2", "Item3", "Monday", "AM", NA),
    c("Store2", "Item3", "Monday", "PM", NA),
    c("Store2", "Item3", "Tuesday", "AM", NA),
    c("Store2", "Item3", "Tuesday", "PM", NA)),ncol = 24))

StoreID	ItemID	Day	Time	Sales
Store1	Item1	Monday	AM	1
Store1	Item1	Monday	PM	2
Store1	Item1	Tuesday	AM	1
Store1	Item1	Tuesday	PM	3
Store1	Item2	Monday	AM	NA
Store1	Item2	Monday	PM	NA
Store1	Item2	Tuesday	AM	NA
Store1	Item2	Tuesday	PM	NA
Store1	Item3	Monday	AM	2
Store1	Item3	Monday	PM	5
Store1	Item3	Tuesday	AM	1
Store1	Item3	Tuesday	PM	6
Store2	Item1	Monday	AM	3
Store2	Item1	Monday	PM	8
Store2	Item1	Tuesday	AM	1
Store2	Item1	Tuesday	PM	3
Store2	Item2	Monday	AM	5
Store2	Item2	Monday	PM	2
Store2	Item2	Tuesday	AM	5
Store2	Item2	Tuesday	PM	0
Store2	Item3	Monday	AM	NA
Store2	Item3	Monday	PM	NA
Store2	Item3	Tuesday	AM	NA
Store2	Item3	Tuesday	PM	NA

How can I achieve this?

Don't know how to start

Answer 1

tribble(
    ~store, ~item, ~monday_am, ~monday_pm, ~tuesday_am, ~tuesday_pm,
    "Store1", "Item1", 1,2,1,3,
    "Store1", "Item3", 2,5,1,6,
    "Store2", "Item1", 3, 8,1,3,
    "Store2", "Item2", 5,2,5,0) |>
pivot_longer(
    cols = c(ends_with("PM"), ends_with("AM")),
    names_to = "day",
    values_to = "n"
) |>
mutate(
    t = sub(".*_([A-Za-z]{2})", "\\1", day),
    day = sub("(.*)_[A-Za-z]{2}", "\\1", day)) |>
relocate(t, .after = day)

produces

# A tibble: 16 × 5
   store  item  day     t         n
   <chr>  <chr> <chr>   <chr> <dbl>
 1 Store1 Item1 monday  pm        2
 2 Store1 Item1 tuesday pm        3
 3 Store1 Item1 monday  am        1
 4 Store1 Item1 tuesday am        1
 5 Store1 Item3 monday  pm        5
 6 Store1 Item3 tuesday pm        6
 7 Store1 Item3 monday  am        2
 8 Store1 Item3 tuesday am        1
 9 Store2 Item1 monday  pm        8
10 Store2 Item1 tuesday pm        3
11 Store2 Item1 monday  am        3
12 Store2 Item1 tuesday am        1
13 Store2 Item2 monday  pm        2
14 Store2 Item2 tuesday pm        0
15 Store2 Item2 monday  am        5
16 Store2 Item2 tuesday am        5

Answer 2

You can archive that using the pivot_longer function from the tidyr package:

# Load the tidyr package
library(tidyr)

# your cross table as data frame
cross_table <- data.frame(
  StoreID = c("Store1", "Store1", "Store2", "Store2"),
  ItemID = c("Item1", "Item3", "Item1", "Item2"),
  Monday_AM = c(1, 2, 3, 5),
  Monday_PM = c(2, 5, 8, 2),
  Tuesday_AM = c(1, 1, 1, 5),
  Tuesday_PM = c(3, 6, 3, 0)
)

# Use pivot_longer to convert to a long table
long_table <- pivot_longer(
  cross_table,
  cols = -c(StoreID, ItemID),
  names_to = c("Day", "Time"),
  names_pattern = "(.+)_(.+)",
  values_to = "Sales"
)

# Reorder the columns as needed
long_table <- long_table[, c("StoreID", "ItemID", "Day", "Time", "Sales")]

# Print the long table
print(long_table)

Answer 3

Since the daya is coming from a excel-table, it is probably easier to use tidyxl and unpivotr-functions.

Given the following excelfile Map1.xlsx

Use the following code:

library(tidyverse)
library(unpivotr)
library(tidyxl)

xlsx_cells("./Map1.xlsx") %>%
  filter(!is_blank) %>%
  behead("up-left", "Day") %>%
  behead("up", "Time") %>%
  behead("left", "ItemID") %>%
  behead("left", "StoreID") %>%
  select(StoreID, ItemID, Day, Time, Sales = numeric) %>%
  complete(StoreID, ItemID, Day, Time)

to output

# A tibble: 24 × 5
   StoreID ItemID Day     Time  Sales
   <chr>   <chr>  <chr>   <chr> <dbl>
 1 Item1   Store1 Monday  AM        1
 2 Item1   Store1 Monday  PM        2
 3 Item1   Store1 Tuesday AM        1
 4 Item1   Store1 Tuesday PM        3
 5 Item1   Store2 Monday  AM        3
 6 Item1   Store2 Monday  PM        8
 7 Item1   Store2 Tuesday AM        1
 8 Item1   Store2 Tuesday PM        3
 9 Item2   Store1 Monday  AM       NA
10 Item2   Store1 Monday  PM       NA
11 Item2   Store1 Tuesday AM       NA
12 Item2   Store1 Tuesday PM       NA
13 Item2   Store2 Monday  AM        5
14 Item2   Store2 Monday  PM        2
15 Item2   Store2 Tuesday AM        5
16 Item2   Store2 Tuesday PM        0
17 Item3   Store1 Monday  AM        2
18 Item3   Store1 Monday  PM        5
19 Item3   Store1 Tuesday AM        1
20 Item3   Store1 Tuesday PM        6
21 Item3   Store2 Monday  AM       NA
22 Item3   Store2 Monday  PM       NA
23 Item3   Store2 Tuesday AM       NA
24 Item3   Store2 Tuesday PM       NA