如何在R中将Cross Table转换为Long Table？

pcww981p 于 2023-09-27 发布在其他

关注(0)|答案(3)|浏览(86)

有没有一个通用的方法可以将十字桌转换成长桌？
我做了下面的表来说明，在真实的情况下，它有7个水平变量和9个垂直变量，所有的变量都可以重复，但只有（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)

| | | 周二|周二| Tuesday | Tuesday |
| --|--|--|--|--|--|
| | | 是|下午| 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 |
我想把它转换成一个长表，看起来像这样：

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|天|时间|销售|
| --|--|--|--|--|
| Store1| Item1|周一|是| 1 |
| Store1| Item1|周一|下午| 2 |
| Store1| Item1|周二|是| 1 |
| Store1| Item1|周二|下午| 3 |
| Store1| Item2|周一|是|NA|
| Store1| Item2|周一|下午|NA|
| Store1| Item2|周二|是|NA|
| Store1| Item2|周二|下午|NA|
| Store1| Item3|周一|是| 2 |
| Store1| Item3|周一|下午| 5 |
| Store1| Item3|周二|是| 1 |
| Store1| Item3|周二|下午| 6 |
| Store2| Item1|周一|是| 3 |
| Store2| Item1|周一|下午| 8 |
| Store2| Item1|周二|是| 1 |
| Store2| Item1|周二|下午| 3 |
| Store2| Item2|周一|是| 5 |
| Store2| Item2|周一|下午| 2 |
| Store2| Item2|周二|是| 5 |
| Store2| Item2|周二|下午| 0 |
| Store2| Item3|周一|是|NA|
| Store2| Item3|周一|下午|NA|
| Store2| Item3|周二|是|NA|
| Store2| Item3|周二|下午|NA|
我如何才能做到这一点？
不知如何开始

来源：https://stackoverflow.com/questions/77141946/how-to-convert-a-cross-table-to-long-table-in-r

3条答案

按热度按时间

y3bcpkx11#

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)

生产

# 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

赞(0）回复(0）举报 2023-09-27

0g0grzrc2#

你可以使用tidyr包中的pivot_longer函数来归档：

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

赞(0）回复(0）举报 2023-09-27

voase2hg3#

由于daya来自于一个excel表，所以使用tidyxl和unpivotr函数可能更容易。
给定以下excelfile Map1.xlsx

使用以下代码：

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)

以输出

# 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

赞(0）回复(0）举报 2023-09-27

我来回答

如何在R中将Cross Table转换为Long Table？

3条答案

生产

相关问题

热门标签

最新问答