Reshaping data by appending rows from different groups to the same row

Question

I have data as follows:

DT <- structure(list(Area = c("A", "A", "A", "A", "B", "B", "B", "B"
), Year = c(1, 1, 2, 2, 1, 1, 2, 2), Group = c(1, 2, 1, 2, 1, 
2, 1, 2), Population_Count = c(10, 12, 10, 12, 10, 13, 10, 11
), Male_Count = c(5, 7, 5, 4, 5, 8, 5, 6), Female_Count = c(5, 
5, 5, 8, 5, 5, 5, 5)), row.names = c(NA, -8L), class = c("tbl_df", 
"tbl", "data.frame"))

# A tibble: 8 x 6
  Area   Year Group Population_Count Male_Count Female_Count
  <chr> <dbl> <dbl>            <dbl>      <dbl>        <dbl>
1 A         1     1               10          5            5
2 A         1     2               12          7            5
3 A         2     1               10          5            5
4 A         2     2               12          4            8
5 B         1     1               10          5            5
6 B         1     2               13          8            5
7 B         2     1               10          5            5
8 B         2     2               11          6            5

I would like to keep one observations per Area-Year, without losing any information. I tried to do

DTcast <- dcast(DT, Area + Year ~ Group + Population_Count + Male_Count + Female_Count)

But that results in a lot of rubbish:

  Area Year 1_10_5_5 2_11_6_5 2_12_4_8 2_12_7_5 2_13_8_5
1    A    1        5       NA       NA        5       NA
2    A    2        5       NA        8       NA       NA
3    B    1        5       NA       NA       NA        5
4    B    2        5        5       NA       NA       NA

In addition, when I apply it to the actual data, I get:

Using 'H_FEMALE' as value column. Use 'value.var' to override
Error in CJ(1:72284, 1:1333365) : 
  Cross product of elements provided to CJ() would result in 96380955660 rows which exceeds .Machine$integer.max == 2147483647

So I think I am doing something wrong. I think it maybe has to do with the value.var which I do not know how to select.

Desired result:

# A tibble: 4 x 9
  Area   Year Group `Population_Count_ Group_1` `Male_Count_ Group_1` `Female_Count_ Group_1` `Population_Count_ Group_2` `Male_Count_ Group_2` `Female_Count_ Group_2`
  <chr> <dbl> <dbl>                       <dbl>                 <dbl>                   <dbl>                       <dbl>                 <dbl>                   <dbl>
1 A         1     1                          10                     5                       5                          12                     7                       5
2 A         2     1                          10                     5                       5                          12                     4                       8
3 B         1     1                          10                     5                       5                          13                     8                       5
4 B         2     1                          10                     5                       5                          11                     6                       5

you need `tidyr::pivot_wider` – AnilGoyal Dec 18 '20 at 09:37 — AnilGoyal, Dec 18 '20 at 09:37

AnilGoyal · Accepted Answer · 2020-12-18T09:49:03.513

library(tidyverse)

DT %>% pivot_wider(id_cols = c("Area", "Year"), names_from = "Group", values_from = 4:6)

> DT %>% pivot_wider(id_cols = c("Area", "Year"), names_from = "Group", values_from = 4:6)
# A tibble: 4 x 8
  Area   Year Population_Count_1 Population_Count_2 Male_Count_1 Male_Count_2 Female_Count_1 Female_Count_2
  <chr> <dbl>              <dbl>              <dbl>        <dbl>        <dbl>          <dbl>          <dbl>
1 A         1                 10                 12            5            7              5              5
2 A         2                 10                 12            5            4              5              8
3 B         1                 10                 13            5            8              5              5
4 B         2                 10                 11            5            6              5              5

This will name your columns as desired

DT %>% pivot_wider(id_cols = c("Area", "Year"), 
                   names_from = "Group", 
                   values_from = 4:6,
                   names_sep = "_Group_")

Thanks a lot, works great! I think I got close with a data.table solution (after googling `pivot_wider` https://stackoverflow.com/questions/64940108/go-from-long-to-wide-using-tidyrs-pivot-wider) `DT <- dcast(melt(DT, id.vars = c("Group")), Area + Year ~ Group + Population_Count + Male_Count + Female_Count , value.var = 'value')`, but yours works well and is quite fast. Thanks a lot. — Tom, Dec 18 '20 at 09:50
use `dcast(dt, formula = Area + Year ~ Group, value.var = c("Population_Count", "Male_Count", "Female_Count"))` — Yuriy Saraykin, Dec 18 '20 at 10:18

score 1 · Answer 2 · answered Dec 18 '20 at 10:14

use data.table

library(data.table)
dt <- structure(list(Area = c("A", "A", "A", "A", "B", "B", "B", "B"
), Year = c(1, 1, 2, 2, 1, 1, 2, 2), Group = c(1, 2, 1, 2, 1, 
                                               2, 1, 2), Population_Count = c(10, 12, 10, 12, 10, 13, 10, 11
                                               ), Male_Count = c(5, 7, 5, 4, 5, 8, 5, 6), Female_Count = c(5, 
                                                                                                           5, 5, 8, 5, 5, 5, 5)), row.names = c(NA, -8L), class = c("tbl_df", 
                                                                                                                                                                    "tbl", "data.frame"))
setDT(dt)

dcast(
  dt, 
  formula = Area + Year ~ Group, 
  value.var = grep("_Count", names(dt), value = T)
)
#>    Area Year Population_Count_1 Population_Count_2 Male_Count_1 Male_Count_2
#> 1:    A    1                 10                 12            5            7
#> 2:    A    2                 10                 12            5            4
#> 3:    B    1                 10                 13            5            8
#> 4:    B    2                 10                 11            5            6
#>    Female_Count_1 Female_Count_2
#> 1:              5              5
#> 2:              5              8
#> 3:              5              5
#> 4:              5              5

^{Created on 2020-12-18 by the reprex package (v0.3.0)}

Reshaping data by appending rows from different groups to the same row

2 Answers2