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

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.

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

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

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.

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

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

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

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)

1 Like

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

@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!

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)

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?

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.