How do I safely remove duplication after using a group_by and summarise?

Stephen Poole 0
Viewed 22
    test_df <- tibble(
  Project = c("Project 1","Project 1","Project 2","Project 3","Project 3","Project 3"),
  Cost = c(100,50,400,50,75,150),
  `Percent Over Cost` = c(.25,.25,.14,.05,.05,.05)
)

Here is what the data frame above looks like:

Visual

I am trying to reduce the duplication in my data frame. Basically, there are three variables of interest: Project, Cost, and Percent over Cost. What I want to do is sum the Costs. So, for instance, Project 1 should have a Cost of 150. But I don't want to sum the percentages. So my ideal data frame should have three rows: one for Project 1, one for Project 2, and one for Project 3, with the Cost column summed by Project, and the Percentage unsummed.

I am trying to reduce the duplication in my data frame. Basically, there are three variables of interest: Project, Cost, and Percent over Cost. What I want to do is sum the Costs. So, for instance, Project 1 should have a Cost of 150. But I don't want to sum the percentages. So my ideal data frame should have three rows: one for Project 1, one for Project 2, and one for Project 3, with the Cost column summed by Project, and the Percentage unsummed.

The below code sums the numbers but leaves duplication in the Projects. See the image below.

test_df %>% 
  group_by(Project) %>% 
  summarise(
    Project,
    `Percent Over Cost`,
    Cost = sum(Cost)
  )

This is what the code returns.

enter image description here

If I use the unique() function on the resulting data frame, I can get what I want... but I'm just unsure if that's bad practice. I'm nervous to use that approach of my company's data. Can anyone advise if there's a better way to approach this?

dplyr r
1 Answers

If Percent Over Cost represents how much the realized cost was over some benchmark (Budget), then we could calculate that, sum it like Cost, and get the overall Percent Over Cost for each project.

test_df %>%
  mutate(Budget = Cost / (1 + `Percent Over Cost`)) %>%  # find implied benchmark
  group_by(Project) %>%
  summarise(across(c(Cost, Budget), sum)) %>%
  mutate(`Percent Over Cost` = Cost / Budget - 1)

# A tibble: 3 × 4
  Project    Cost Budget `Percent Over Cost`
  <chr>     <dbl>  <dbl>               <dbl>
1 Project 1   150   120               0.25  
2 Project 2   400   351.              0.140 
3 Project 3   275   262.              0.0500
Jon Spring0
Related