can we solve it with dcast()?

[osman3144]

Hi friend ı have a question,

we have 6 (A,B,C,D,E,X) people and 8 (New York,Los Angeles,Chicago,Miami,Washington,San Diego,San Francisco,Boston) city. we havea df like below.AND THEY TRAVEL BETWEEN CITIES.

df <- data.frame(
PERSON = c("A","B","C","D","E","B","A","B","C","D","A","D","A","B","C","D","E","B","A","B"),
WEIGHT = c(1,2,3,5,7,2,4,5,1,3,2,5,7,8,4,5,2,4,1,3),
PIECE = c(4,5,2,1,1,99,1,5,4,20,5,86,15,50,20,1,100,1,310,825),
TOTAL_WEIGHT = c(4,10,6,5,7,198,4,25,4,60,10,430,105,400,80,5,200,4,310,2475),
FROM = c("New York","Los Angeles","Chicago","Miami","Washington","San Diego","San Francisco","Boston","New York","Los Angeles","Chicago","Miami","Washington","San Diego","San Francisco","Boston","New York","Los Angeles","Chicago","Miami"),
TO = c("Miami","New York","Los Angeles","Chicago","Miami","Washington","San Diego","San Francisco","Boston","New York","Los Angeles","Chicago","Miami","Washington","San Diego","San Francisco","Boston","New York","Los Angeles","Chicago")
)

I try to find sum of the total weights like below table.

Note: "X" person forget all the plan and doesn't travel. but ı report that in table.

How can ı do that?

[nirgrahamuk]

are you simply summing total weight by person by from location (i.e. ignoring to)?
if so is there a reason that miami / B is 2474 and not 2475; or is that a typo ?

[osman3144]

yes "B" is 2475 and ı create it as toy data. also "X" will be in the report not added later as a column. thanks.

[nirgrahamuk]

library(tidyverse)

adf <- data.frame(
  PERSON = c("A", "B", "C", "D", "E", "B", "A", "B", "C", "D", "A", "D", "A", "B", "C", "D", "E", "B", "A", "B"),
  WEIGHT = c(1, 2, 3, 5, 7, 2, 4, 5, 1, 3, 2, 5, 7, 8, 4, 5, 2, 4, 1, 3),
  PIECE = c(4, 5, 2, 1, 1, 99, 1, 5, 4, 20, 5, 86, 15, 50, 20, 1, 100, 1, 310, 825),
  TOTAL_WEIGHT = c(4, 10, 6, 5, 7, 198, 4, 25, 4, 60, 10, 430, 105, 400, 80, 5, 200, 4, 310, 2475),
  FROM = c("New York", "Los Angeles", "Chicago", "Miami", "Washington", "San Diego", "San Francisco", "Boston", "New York", "Los Angeles", "Chicago", "Miami", "Washington", "San Diego", "San Francisco", "Boston", "New York", "Los Angeles", "Chicago", "Miami"),
  TO = c("Miami", "New York", "Los Angeles", "Chicago", "Miami", "Washington", "San Diego", "San Francisco", "Boston", "New York", "Los Angeles", "Chicago", "Miami", "Washington", "San Diego", "San Francisco", "Boston", "New York", "Los Angeles", "Chicago")
)

mylevels <- c(unique(adf$PERSON), "X")
result <- adf |>
  mutate(PERSON = factor(PERSON, 
                         levels = mylevels)) |>
  group_by(
    PERSON, FROM,
    .drop = FALSE
  ) |>
  summarise(w = sum(TOTAL_WEIGHT)) |>
  pivot_wider(
    names_from = PERSON,
    values_from = w
  ) |>
  filter(!is.na(FROM))

result

[osman3144]

thanks for your great effort my friend . good solution.

[FactOREO]

Just for the record, regarding the title "can we solve it with dcast?" - yes, we can:

library('collapse')
library('data.table')

adf |>
  as.data.table() |>
  ftransform(PERSON = factor(PERSON, levels = c(funique(adf$PERSON),'X'))) |>
  dcast.data.table(FROM ~ PERSON,
                   value.var = 'TOTAL_WEIGHT',
                   fun.aggregate = sum,drop = FALSE,
                   fill = NA_integer_)
#>             FROM   A    B  C   D   E  X
#> 1:        Boston  NA   25 NA   5  NA NA
#> 2:       Chicago 320   NA  6  NA  NA NA
#> 3:   Los Angeles  NA   14 NA  60  NA NA
#> 4:         Miami  NA 2475 NA 435  NA NA
#> 5:      New York   4   NA  4  NA 200 NA
#> 6:     San Diego  NA  598 NA  NA  NA NA
#> 7: San Francisco   4   NA 80  NA  NA NA
#> 8:    Washington 105   NA NA  NA   7 NA

^{Created on 2022-11-11 by the reprex package (v2.0.1)}

Best regards

[osman3144]

@FactOREO

library('collapse')
library('data.table')

adf |>
  as.data.table() |>
  ftransform(PERSON = factor(PERSON, levels = c(funique(adf$PERSON),'X'))) |>
  dcast.data.table(FROM ~ PERSON,
                   value.var = 'TOTAL_WEIGHT',
                   fun.aggregate = sum,drop = FALSE,
                   fill = NA_integer_)

thanks for your great solution my friend @ [FactOREO]