How to use dcast and aggregate function in R for a list of data?

[Shadan]

I want to apply this code to a list of data in R
dcast(df, PROVINCE ~ ETHNICITY, value.var = "WEIGHT", fun.aggregate = sum)

this is the output of code:

1       11 845.19 41.43 214.38 93.66 273.78 19.7 13656.12 48 10.43  0.00 0.00 10.12 20.40   30 19.9
2       14   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 0.00  0.00  9.89    0  0.0
3       16   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00 10.11 0.00  0.00  0.00    0  0.0
4       19   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 9.57  0.00  0.00    0  0.0
5       32  29.58  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 0.00  0.00  9.78    0  0.0
6       33   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 0.00  0.00 49.39    0  0.0
7       35   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 0.00  0.00 49.63    0  0.0
8       63   0.00  0.00   0.00  0.00   0.00  0.0     0.00  0  0.00  0.00 0.00  0.00  9.43    0  0.0


I do not know how to apply it.

[FJCC]

There is no code in your post, only the final output. Please edit your post to provide the initial data and your code.

[Shadan]

This is the code:
dcast(df, PROVINCE ~ ETHNICITY, value.var = "WEIGHT", fun.aggregate = sum)

[nirgrahamuk]

@Shadan

I want to apply this code to a list of data in R

Do you mean that you want to iterate over a list applying the same dcast function on each member of the list?
If so use purrr package map function.

[Shadan]

@nirgrahamuk How should I use purrr package? I don know.

[Shadan]

If you know another code , please let me know. Thanks.

[nirgrahamuk]

9 Functionals | Advanced R

[Shadan]

@nirgrahamuk Thank you for sharing it. But I do not know how to use it.

[FJCC]

Is this the sort of thing you are looking for? If not, please post a small sample of you initial data.

DF1 <- data.frame(PROVINCE = rep(c("A", "B", "C"), 4),
                 ETHNICITY = rep(c("Y", "Z"), 6),
                 WEIGHT = runif(12, min = 40, max = 60))

DF2 <- data.frame(PROVINCE = rep(c("A", "B", "C"), 4),
                  ETHNICITY = rep(c("Y", "Z"), 6),
                  WEIGHT = runif(12, min = 40, max = 60))

MyList <- list(DF1, DF2)
MyFunc <- function(DatFrame) {
  reshape2::dcast(data = DatFrame, formula = PROVINCE ~ ETHNICITY, 
                  value.var = "WEIGHT", fun.aggregate = sum)
}
DCAST_out <- lapply(X = MyList, FUN = MyFunc)
DCAST_out
#> [[1]]
#>   PROVINCE         Y        Z
#> 1        A  89.98376 90.79929
#> 2        B  97.12802 90.68774
#> 3        C 100.62682 91.20288
#> 
#> [[2]]
#>   PROVINCE         Y         Z
#> 1        A 106.09127  94.19237
#> 2        B 111.30426 107.42695
#> 3        C  84.44623 102.04221

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

[Shadan]

@FJCC thank you very much! It is the code that I was looking.

[Shadan]

@FJCC I want to add two rows ( total and percentage) for each data table. If you know how to add, please let me know. Thank you!

[FJCC]

Is this what you mean by adding a total row?

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
DF1 <- data.frame(PROVINCE = rep(c("A", "B", "C"), 4),
                  ETHNICITY = rep(c("Y", "Z"), 6),
                  WEIGHT = runif(12, min = 40, max = 60))

DF2 <- data.frame(PROVINCE = rep(c("A", "B", "C"), 4),
                  ETHNICITY = rep(c("Y", "Z"), 6),
                  WEIGHT = runif(12, min = 40, max = 60))

MyList <- list(DF1, DF2)
MyFunc <- function(DatFrame) {
  tmp <- reshape2::dcast(data = DatFrame, formula = PROVINCE ~ ETHNICITY, 
                  value.var = "WEIGHT", fun.aggregate = sum)
  SUMS <- tmp %>% summarize(across(.col=where(is.numeric), sum))
  SummaryRow <- bind_cols(data.frame(PROVINCE="TOTAL"),SUMS)
  bind_rows(tmp, SummaryRow)
}
DCAST_out <- lapply(X = MyList, FUN = MyFunc)
DCAST_out
#> [[1]]
#>   PROVINCE         Y         Z
#> 1        A 108.14376 109.16078
#> 2        B  97.54265 114.88119
#> 3        C  91.41058  98.30363
#> 4    TOTAL 297.09699 322.34560
#> 
#> [[2]]
#>   PROVINCE         Y         Z
#> 1        A 100.91991  94.33169
#> 2        B 106.15745 114.00632
#> 3        C  98.59599 103.73327
#> 4    TOTAL 305.67335 312.07127

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

[Shadan]

Hi @FJCC
Thank you write nice code. It just for the total. I am looking for the percentage now. Do you know to add the percentage of them?