How to attach newly generated variable by group or category to original dataset

Question

I have a following dataframe (df) and generated a variable of summation of sales by industry (ksic2). I want to attach this new variable to the original df so that I construct a new dataframe. Any help please?

aggregate(df$sales, by=list(ksic2=df$ksic2), FUN=sum)

structure(list(firmid = structure(c("016090", "002070", "009270", 
"007700", "005800", "014990", "001460", "001460", "005800", "014990", 
"002070", "016090", "007700", "009270", "001460", "007700", "014990", 
"005800", "016090", "002070", "009270", "009270", "002070", "016090", 
"007980", "014990", "005800", "007700", "001460", "016090", "007980", 
"002070", "001460", "009270", "014990", "007700", "005800", "009270", 
"001460", "014990", "020000", "016090", "002070", "007980", "005800", 
"007700", "007980", "002070", "007700", "001460"), format.stata = "%-6s"), 
    year = structure(c(1992, 1992, 1992, 1992, 1992, 1992, 1992, 
    1993, 1993, 1993, 1993, 1993, 1993, 1993, 1994, 1994, 1994, 
    1994, 1994, 1994, 1994, 1995, 1995, 1995, 1995, 1995, 1995, 
    1995, 1995, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 1996, 
    1997, 1997, 1997, 1997, 1997, 1997, 1997, 1997, 1997, 1998, 
    1998, 1998, 1998), format.stata = "%9.0g"), ksic2 = structure(c("M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel", 
    "M) Apparel", "M) Apparel", "M) Apparel", "M) Apparel"), label = "classification", format.stata = "%-62s"), 
    sales = structure(c(89881001984, 118717997056, 167806992384, 
    18667999232, 107579998208, 105812000768, 313181995008, 323294003200, 
    97535000576, 125405003776, 119427997696, 98385002496, 19235999744, 
    222712004608, 356682989568, 17685999616, 174042005504, 98319998976, 
    130714001408, 126674001920, 359814987776, 477821992960, 129806000128, 
    198731005952, 56521998336, 254710005760, 108176998400, 18386999296, 
    374637002752, 234864001024, 87393001472, 131512999936, 338225004544, 
    578683994112, 340619984896, 28804999168, 111874998272, 824554029056, 
    303513010176, 236455002112, 78887002112, 241608998912, 140156993536, 
    85182996480, 121896001536, 38905999360, 1.37216e+11, 126850998272, 
    32975998976, 241390993408), format.stata = "%9.0g")), row.names = c(NA, 
-50L), class = c("tbl_df", "tbl", "data.frame"))

akrun · Accepted Answer · 2022-01-05T17:18:32.493

2

Use either ave from base R

df$Sumsales <- with(df, ave(sales, ksic2, FUN = sum))

or can mutate to create the column

df <- df %>% 
    group_by(ksic2) %>% 
    mutate(Sumsales = sum(sales, na.rm = TRUE), .before = 3) %>%
    ungroup

edited Jan 05 '22 at 17:18

answered Jan 05 '22 at 17:08

akrun

874,273
37
540
662

thanks, could you let me know how to make the new column (Sumsales) as the 3rd column in the df? – jck21 Jan 05 '22 at 17:17
1

@joe Updated.. In the `mutate`, you can specify the position with `.before` – akrun Jan 05 '22 at 17:19

How to attach newly generated variable by group or category to original dataset

1 Answers1