How to transform selected rows into a single column in R

user_12084960 0
Viewed 105

I have a data frame which I need to transform. I need to change the unique rows into single columns based on the value of a column.

My data below:

df1 <- data.frame(V1 = c("a", "a", "b", "b","b"), 
                  V2 = c("product1", "transport", "product1", "product2","transport"),
                  V3 = c("100", "10", "100", "100","10"))

> df1
  V1        V2  V3
1  a  product1 100
2  a transport  10
3  b  product1 100
4  b  product2 100
5  b transport  10

I need the following transformation and the division of the value of V3 into the number of products included in V1.

> df2
  V1       V2 transport  V3
1  a product1        10 100
2  b product1         5 100
3  b product2         5 100
data.table tidyr dplyr dataframe r
6 Answers

Here is one method with data.table - convert to data.table (setDT), make sure that the 'V3' is numeric (for division - it was created as character), grouped by 'V1', create the 'transport' by extracting the 'V3' value where 'V2' is 'transport' and divide by the number of elements in 'V2' that are not "transport", then subset the data by removing the 'transport' elements from 'V2'

library(data.table)
df1$V3 <- as.numeric(df1$V3)
setDT(df1)[, transport := V3[V2 == "transport"]/
       sum(V2 != "transport"), by = V1]
df1[V2 != "transport"]
       V1       V2    V3 transport
   <char>   <char> <num>     <num>
1:      a product1   100        10
2:      b product1   100         5
3:      b product2   100         5

Or another option with dplyr/tidyr

library(dplyr)
library(tidyr)
df1 %>%
   type.convert(as.is = TRUE) %>% 
   mutate(transport = case_when(V2 == 'transport' ~ V3)) %>% 
   group_by(V1) %>%
   fill(transport, .direction = "downup") %>%
   mutate(transport = transport/sum(V2 != "transport")) %>% 
   ungroup %>% 
   filter(V2 != "transport")
# A tibble: 3 × 4
  V1    V2          V3 transport
  <chr> <chr>    <int>     <dbl>
1 a     product1   100        10
2 b     product1   100         5
3 b     product2   100         5
user_37322710

Here's a tidyverse-based solution, using tidyr and dplyr:

  1. convert V3 to numeric
  2. pivot wider to create a separate column for transport, then pivot longer to collapse "product1" and "product2" back into one column
  3. divide transport by number of cases per V1.
library(dplyr)
library(tidyr)

df1 %>% 
  mutate(V3 = as.numeric(V3)) %>% 
  pivot_wider(names_from = V2, values_from = V3) %>% 
  pivot_longer(
    cols = c(product1, product2), 
    names_to = "V2", 
    values_to = "V3", 
    values_drop_na = TRUE
  ) %>% 
  group_by(V1) %>% 
  mutate(transport = transport / n()) %>% 
  ungroup()

#> # A tibble: 3 x 4
#>   V1    transport V2          V3
#>   <chr>     <dbl> <chr>    <dbl>
#> 1 a            10 product1   100
#> 2 b             5 product1   100
#> 3 b             5 product2   100

Created on 2022-03-17 by the reprex package (v2.0.1)

user_173038050

Here is another approach to get your desired output if I got your point right:

library(dplyr)
library(tidyr)

df1 %>%
  group_by(V1) %>%
  mutate(V3 = ifelse(V2 == "transport", as.numeric(V3) / (n() - 1), as.numeric(V3))) %>%
  ungroup() %>%
  pivot_wider(names_from = V2, values_from = V3) %>%
  pivot_longer(c(product1, product2), names_to = "V2", values_to = "V3") %>%
  drop_na()

# A tibble: 3 x 4
  V1    transport V2          V3
  <chr>     <dbl> <chr>    <dbl>
1 a            10 product1   100
2 b             5 product1   100
3 b             5 product2   100
user_143145200

Another possible solution:

library(tidyverse)

df1 %>%
  mutate(V3 = as.numeric(V3)) %>% 
  group_by(V1) %>% 
  mutate(transport = if_else(V2 == "transport", V3 / (n()-1), NA_real_)) %>% 
  fill(transport, .direction = "up") %>% ungroup %>% 
  filter(V2 != "transport")

#> # A tibble: 3 × 4
#>   V1    V2          V3 transport
#>   <chr> <chr>    <dbl>     <dbl>
#> 1 a     product1   100        10
#> 2 b     product1   100         5
#> 3 b     product2   100         5
user_115644870

Here is one more with pivoting:

library(dplyr)
library(tidyr)

df1 %>% 
  pivot_wider(
    names_from = V2,
    values_from = V3
  ) %>% 
  pivot_longer(
    -c(V1, transport),
    names_to = "V2",
    values_to = "V3"
  )%>% 
  type.convert(as.is = TRUE) %>% 
  na.omit() %>% 
  group_by(V1) %>% 
  mutate(transport = transport/max(row_number()))

  V1    transport V2          V3
  <chr>     <dbl> <chr>    <int>
1 a            10 product1   100
2 b             5 product1   100
3 b             5 product2   100
user_133216470

Here's a data.table approach:

f <- function(p,v) {  
  ps=grepl("^p",p)
  list(V2 = p[ps], transport=rep(v[!ps]/sum(ps), sum(ps)), V3 = v[ps])
}
setDT(df1)[,f(V2,as.numeric(V3)), V1 ]

       V1       V2 transport    V3
   <char>   <char>     <num> <num>
1:      a product1        10   100
2:      b product1         5   100
3:      b product2         5   100
user_44475400
Related