dplyr creating new column based on some condition

Question

I have the following df:

df<-data.frame(geo_num=c(11,12,22,41,42,43,77,71),
               cust_id=c("A","A","B","C","C","C","D","D"),
               sales=c(2,3,2,1,2,4,6,3))
> df
  geo_num cust_id sales
1      11       A     2
2      12       A     3
3      22       B     2
4      41       C     1
5      42       C     2
6      43       C     4
7      77       D     6
8      71       D     3

Require to create a new column 'geo_num_new' which has for every group from 'cust_id' has first values from 'geo_num' as shown below:

> df_new
  geo_num cust_id sales geo_num_new
1      11       A     2          11
2      12       A     3          11
3      22       B     2          22
4      41       C     1          41
5      42       C     2          41
6      43       C     4          41
7      77       D     6          77
8      71       D     3          77

thanks.

akrun · Accepted Answer · 2021-07-31T04:45:52.820

We could use first after grouping by 'cust_id'. The single value will be recycled for the entire grouping

library(dplyr)
df <- df %>% 
    group_by(cust_id) %>%
    mutate(geo_num_new = first(geo_num)) %>%
    ungroup

-ouptut

df
# A tibble: 8 x 4
  geo_num cust_id sales geo_num_new
    <dbl> <chr>   <dbl>       <dbl>
1      11 A           2          11
2      12 A           3          11
3      22 B           2          22
4      41 C           1          41
5      42 C           2          41
6      43 C           4          41
7      77 D           6          77
8      71 D           3          77

Or use data.table

library(data.table)
setDT(df)[, geo_num_new := first(geo_num), by = cust_id]

or with base R

df$geo_num_new <- with(df, ave(geo_num, cust_id, FUN = function(x) x[1]))

Or an option with collapse

library(collapse)
tfm(df, geo_num_new = ffirst(geo_num, g = cust_id, TRA = "replace"))
  geo_num cust_id sales geo_num_new
1      11       A     2          11
2      12       A     3          11
3      22       B     2          22
4      41       C     1          41
5      42       C     2          41
6      43       C     4          41
7      77       D     6          77
8      71       D     3          77

dplyr creating new column based on some condition

1 Answers1