Sum of column based on a condition a R

Question

I would like to print out total amount for each date so that my new dataframe will have date and and total amount columns. My data frame looks like this

permitnum	amount
6/1/2022	na
ascas	30.00
olic	40.41
6/2/2022	na
avrey	17.32
fev	32.18
grey	12.20

any advice on how to go about this will be appreciated

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. You seem to have mixed data types in columns which is a problem. How did you import the data? It's better to share a `dput()` so we know exactly what's in the data.frame and how it's coded rather than using HTML tables for sharing data. — MrFlick, Nov 07 '22 at 18:38

score 0 · Answer 1 · answered Nov 07 '22 at 18:51

Here is an option. Split the data by the date marked by a row with a number, then summarize the total in amount and combine the date and all rows.

library(tidyverse)

dat <- read_table("permitnum    amount
6/1/2022    na
ascas   30.00
olic    40.41
6/2/2022    na
avrey   17.32
fev 32.18
grey    12.20")

dat |>
  group_split(id = cumsum(grepl("\\d", permitnum))) |>
  map_dfr(\(x){
    date <- x$permitnum[[1]]
    x |>
      slice(-1) |>
      summarise(date = date,
                total_amount = sum(as.numeric(amount)))
  })
#> # A tibble: 2 x 2
#>   date     total_amount
#>   <chr>           <dbl>
#> 1 6/1/2022         70.4
#> 2 6/2/2022         61.7

score 0 · Accepted Answer · answered Nov 07 '22 at 19:24

Here is another tidyverse option, where I convert to date (and then reformat), then we can fill in the date, so that we can use that to group. Then, get the sum for each date.

library(tidyverse)

df %>% 
  mutate(permitnum = format(as.Date(permitnum, "%m/%d/%Y"), "%m/%d/%Y")) %>% 
  fill(permitnum, .direction = "down") %>% 
  group_by(permitnum) %>% 
  summarise(total_amount = sum(as.numeric(amount), na.rm = TRUE))

Output

  permitnum  total_amount
  <chr>             <dbl>
1 06/01/2022         70.4
2 06/02/2022         61.7

Data

df <- structure(list(permitnum = c("6/1/2022", "ascas", "olic", "6/2/2022", 
"avrey", "fev", "grey"), amount = c("na", "30.00", "40.41", "na", 
"17.32", "32.18", "12.20")), class = "data.frame", row.names = c(NA, 
-7L))

Sum of column based on a condition a R

2 Answers2