在R中将行转换为列

Question

我有这个样本数据集，想将其转换为以下格式：

Type <- c("AGE", "AGE", "REGION", "REGION", "REGION", "DRIVERS", "DRIVERS")
Level <- c("18-25", "26-70", "London", "Southampton", "Newcastle", "1", "2")
Estimate <- c(1.5,1,2,3,1,2,2.5)

df_before <- data.frame(Type, Level, Estimate)


     Type       Level Estimate
1     AGE       18-25      1.5
2     AGE       26-70      1.0
3  REGION      London      2.0
4  REGION Southampton      3.0
5  REGION   Newcastle      1.0
6 DRIVERS           1      2.0
7 DRIVERS           2      2.5

基本上，我希望将数据集转换为以下格式。我试着用函数dcast()来实现，但似乎不能正常工作。

    AGE Estimate_AGE      REGION Estimate_REGION DRIVERS Estimate_DRIVERS
1 18-25          1.5      London               2       1              2.0
2 26-70          1.0 Southampton               3       2              2.5
3  <NA>           NA   Newcastle               1    <NA>               NA

- Nicolas123

---

这个回答解决了你的问题吗？如何将数据从长格式转换为宽格式 - deschen

不好意思，我的数据集格式不同。 - Nicolas123

你可能需要重构你的数据，因为在同一列中混合字符串和数字值并不好。 - user438383

Answer 1

df_before %>%
  group_by(Type) %>%
  mutate(id = row_number(), Estimate = as.character(Estimate))%>%
  pivot_longer(-c(Type, id)) %>%
  pivot_wider(id, names_from = c(Type, name))%>%
  type.convert(as.is = TRUE)

# A tibble: 3 x 7
     id AGE_Level AGE_Estimate REGION_Level REGION_Estimate DRIVERS_Level DRIVERS_Estimate
  <int> <chr>            <dbl> <chr>                  <int>         <int>            <dbl>
1     1 18-25              1.5 London                     2             1              2  
2     2 26-70              1   Southampton                3             2              2.5
3     3 NA                NA   Newcastle                  1            NA             NA  

在 data.table 中：

library(data.table)
setDT(df_before)

dcast(melt(df_before, 'Type'), rowid(Type, variable)~Type + variable)

请注意，由于类型不匹配，您将收到很多警告。您可以使用reshape2 :: melt来避免这种情况。

无论如何，您的数据框不符合标准格式。

在基本R中，版本号大于或等于4.0。

transform(df_before, id = ave(Estimate, Type, FUN = seq_along)) |>
  reshape(v.names = c('Level', 'Estimate'), dir = 'wide', timevar = 'Type', sep = "_")

 id Level_AGE Estimate_AGE Level_REGION Estimate_REGION Level_DRIVERS Estimate_DRIVERS
1  1     18-25          1.5       London               2             1              2.0
2  2     26-70          1.0  Southampton               3             2              2.5
5  3      <NA>           NA    Newcastle               1          <NA>               NA

在基本的 R <4 中

reshape(transform(df_before, id = ave(Estimate, Type, FUN = seq_along)),
       v.names = c('Level', 'Estimate'), dir = 'wide', timevar = 'Type', sep = "_")

- onyambu

Answer 2

更新：

与期望输出完全相同的输出：

df_before %>% 
  group_by(Type) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(
    names_from = Type,
    values_from = c(Level, Estimate)
  ) %>% 
  select(AGE = Level_AGE, Estimate_AGE, REGION = Level_REGION, 
         Estimate_REGION, DRIVERS = Level_DRIVERS, Estimate_DRIVERS) %>% 
  type.convert(as.is=TRUE)

  AGE   Estimate_AGE REGION      Estimate_REGION DRIVERS Estimate_DRIVERS
  <chr>        <dbl> <chr>                 <int>   <int>            <dbl>
1 18-25          1.5 London                    2       1              2  
2 26-70          1   Southampton               3       2              2.5
3 NA            NA   Newcastle                 1      NA             NA  

第一个答案：

主要方面是按照 Onyambu 的解决方案提供的 Type 进行分组。之后我们可以使用一个 pivot_wider：

library(dplyr)
library(tidyr)

df_before %>% 
  group_by(Type) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(
    names_from = Type,
    values_from = c(Level, Estimate)
  )

     id Level_AGE Level_REGION Level_DRIVERS Estimate_AGE Estimate_REGION Estimate_DRIVERS
  <int> <chr>     <chr>        <chr>                <dbl>           <dbl>            <dbl>
1     1 18-25     London       1                      1.5               2              2  
2     2 26-70     Southampton  2                      1                 3              2.5
3     3 NA        Newcastle    NA                    NA                 1             NA  

- TarJae

Answer 3

我们可以尝试这个：

library(tidyverse)

Type <- c("AGE", "AGE", "REGION", "REGION", "REGION", "DRIVERS", "DRIVERS")
Level <- c("18-25", "26-70", "London", "Southampton", "Newcastle", "1", "2")
Estimate <- c(1.5, 1, 2, 3, 1, 2, 2.5)
df_before <- data.frame(Type, Level, Estimate)

data <-
  df_before %>% group_split(Type)

data <-
  map2(
    data, map(data, ~ unique(.$Type)),
    ~ mutate(., "{.y}" := Level, "Estimate_{.y}" := Estimate) %>%
      select(-c("Type", "Level", "Estimate"))
  )

#get the longest number of rows to be able to join the columns
max_rows <- map_dbl(data, nrow) %>%
  max()

#add rows if needed
map_if(
  data, ~ nrow(.) < max_rows,
  ~ rbind(., NA)
) %>%
  bind_cols()
#> # A tibble: 3 × 6
#>   AGE   Estimate_AGE DRIVERS Estimate_DRIVERS REGION      Estimate_REGION
#>   <chr>        <dbl> <chr>              <dbl> <chr>                 <dbl>
#> 1 18-25          1.5 1                    2   London                    2
#> 2 26-70          1   2                    2.5 Southampton               3
#> 3 <NA>          NA   <NA>                NA   Newcastle                 1

^{由reprex package (v2.0.1)于2021年12月07日创建}

- jpdugo17

Answer 4

一种基于 tidyr::pivot_wider 和 purrr::map_dfc 的解决方案：

library(tidyverse)

Type <- c("AGE", "AGE", "REGION", "REGION", "REGION", "DRIVERS", "DRIVERS")
Level <- c("18-25", "26-70", "London", "Southampton", "Newcastle", "1", "2")
Estimate <- c(1.5,1,2,3,1,2,2.5)
df_before <- data.frame(Type, Level, Estimate)

df_before %>% 
  pivot_wider(names_from=Type, values_from=c(Level, Estimate), values_fn=list) %>% 
  map_dfc(~ c(unlist(.x), rep(NA, max(table(df_before$Type))-length(unlist(.x)))))

#> # A tibble: 3 × 6
#>   Level_AGE Level_REGION Level_DRIVERS Estimate_AGE Estimate_REGION
#>   <chr>     <chr>        <chr>                <dbl>           <dbl>
#> 1 18-25     London       1                      1.5               2
#> 2 26-70     Southampton  2                      1                 3
#> 3 <NA>      Newcastle    <NA>                  NA                 1
#> # … with 1 more variable: Estimate_DRIVERS <dbl>

---

另一种基于 dplyr::group_split 和 purrr::map_dfc 的解决方案：

library(tidyverse)

df_before %>% 
  mutate(maxn = max(table(.$Type))) %>% 
  group_by(Type) %>% group_split() %>% 
  map_dfc(
    ~ data.frame(c(.x$Level, rep(NA, .x$maxn[1] - nrow(.x))),
      c(.x$Estimate, rep(NA, .x$maxn[1] - nrow(.x)))) %>%
      set_names(c(.x$Type[1], paste0("Estimate_", .x$Type[1])))) %>% 
  type.convert(as.is=T)

#>     AGE Estimate_AGE DRIVERS Estimate_DRIVERS      REGION Estimate_REGION
#> 1 18-25          1.5       1              2.0      London               2
#> 2 26-70          1.0       2              2.5 Southampton               3
#> 3  <NA>           NA      NA               NA   Newcastle               1

- PaulS