CODEWITHSUNDEEP
r
Skn U
Skn U

Reputation: 61

How to assign value when corresponding column equal to 1?

Year=c(2001,2001,2001,2001,2001,2001,
       2002,2002,2002,2002,2002,2002,
       2003,2003,2003,2003,2003,2003)
Factory=c("A","B","C","A","B","C",
          "A","B","C","A","B","C",
          "A","B","C","A","B","C")
Distribution=c(10023,12034,13948,10294,20494,34549,
               28979,34924,29845,18346,47377,65297,
               39081,85393,18273,82634,87635,64576,
               72368,25764,25863,45786,32789,38297)
Product=c("Cola","Cola","Cola","PepsiCo","PepsiCo","PepsiCo",
          "Cola","Cola","Cola","PepsiCo","PepsiCo","PepsiCo",
          "Cola","Cola","Cola","PepsiCo","PepsiCo","PepsiCo")

df=data.frame(Year,Factory,Distribution,Product)

This is my coding to separate by Factory.

df %>%
  mutate(Product = 1) %>%
  pivot_wider(names_from = Factory,
              values_from = Product,
              values_fill = list(Product = 0))

enter image description here

How can I assign Distribution column values to A B C separately when they =1 and arrange according to Year?

Expected Output as Below:

enter image description here

Upvotes: 2

Views: 44

Answers (3)

TarJae
TarJae

Reputation: 79164

Here is a version with dplyr only:

library(dplyr)
df %>% 
    cbind(model.matrix(~ Factory + 0, .)*1) %>% 
    mutate(across(c(FactoryA, FactoryB, FactoryC), ~ifelse(. == 1, Distribution, NA)),
           FactoryB = lead(FactoryB),
           FactoryC = lead(FactoryC, 2), .keep="unused") %>% 
    na.omit() %>% 
    select(-Factory, -Product)

   Year FactoryA FactoryB FactoryC
1  2001    10023    12034    13948
4  2001    10294    20494    34549
7  2002    28979    34924    29845
10 2002    18346    47377    65297
13 2003    39081    85393    18273
16 2003    82634    87635    64576

Upvotes: 2

Rui Barradas
Rui Barradas

Reputation: 76565

Here is a way. Use Product as a id column and after reshaping, remove it from the result.

library(dplyr)
library(tidyr)

df %>%
  pivot_wider(
    names_from = Factory,
    values_from = Distribution,
    values_fill = list(Distribution = 0)
  ) %>%
  select(-Product)
## A tibble: 6 x 4
#   Year     A     B     C
#  <dbl> <dbl> <dbl> <dbl>
#1  2001 10023 12034 13948
#2  2001 10294 20494 34549
#3  2002 28979 34924 29845
#4  2002 18346 47377 65297
#5  2003 39081 85393 18273
#6  2003 82634 87635 64576

Upvotes: 3

akrun
akrun

Reputation: 887501

We create a sequence column and then use pivot_wider

library(dplyr)
library(tidyr)
library(data.table)
df %>%
     select(-Product) %>%
     mutate(rn = rowid(Factory))  %>%
     pivot_wider(names_from = Factory, values_from = Distribution) %>%
     select(-rn)

-output

# A tibble: 6 x 4
   Year     A     B     C
  <dbl> <dbl> <dbl> <dbl>
1  2001 10023 12034 13948
2  2001 10294 20494 34549
3  2002 28979 34924 29845
4  2002 18346 47377 65297
5  2003 39081 85393 18273
6  2003 82634 87635 64576

data

df <- structure(list(Year = c(2001, 2001, 2001, 2001, 2001, 2001, 2002, 
2002, 2002, 2002, 2002, 2002, 2003, 2003, 2003, 2003, 2003, 2003
), Factory = c("A", "B", "C", "A", "B", "C", "A", "B", "C", "A", 
"B", "C", "A", "B", "C", "A", "B", "C"), Distribution = c(10023, 
12034, 13948, 10294, 20494, 34549, 28979, 34924, 29845, 18346, 
47377, 65297, 39081, 85393, 18273, 82634, 87635, 64576), Product = c("Cola", 
"Cola", "Cola", "PepsiCo", "PepsiCo", "PepsiCo", "Cola", "Cola", 
"Cola", "PepsiCo", "PepsiCo", "PepsiCo", "Cola", "Cola", "Cola", 
"PepsiCo", "PepsiCo", "PepsiCo")), class = "data.frame", row.names = c(NA, 
-18L))

Upvotes: 3

Related Questions