1

I have some Electric Vehicle charging capacity projections from 2019 to 2050 for different areas and charger types. I want to sum the values across the total area and group by charger type like so:

df %>%
  group_by(ChargerType) %>%
  summarise(sum2019 = sum(df$`2019`))

But I want to do it for all years from 2019 to 2050. This can be done individually but would be very tedious and I'm sure there is a nice way to do it all in one!

Example data for you to try can look like this:

Area <- c(1,1,1,2,2,2,3,3,3)
ChargerType <- c("Domestic", "Public", "Fast", "Domestic", "Public", "Fast", "Domestic", "Public", "Fast")
`2019` <- c(0.1,0,0.3,0.5,0.1,0.2,0,0,0.1)
`2020` <- c(0.2,0.2,0.4,0.6,0.2,0.2,0.1,0,0.2)
`2021` <-c(0.4,0.3,0.4,0.8,0.3,0.2,0.2,0.2,0.3)

df <- data.table(Area, ChargerType, `2019`, `2020`, `2021`)

This is small example to help you, obviously for only up until 2021 but feel free to create more data!

Hope you can help, sure it would be easy for someone out there!

EllisR8
  • 169
  • 2
  • 10

1 Answers1

1

You can get the data in long format and sum the values for each ChargerType and column name.

library(dplyr)

df %>%
  tidyr::pivot_longer(cols = -c(Area, ChargerType)) %>%
  group_by(ChargerType, name) %>%
  summarise(sum = sum(value))

If you have data.table, you can do :

library(data.table)
dt <- melt(df, id.vars = c("Area", "ChargerType"))
dt[, .(value = sum(value)), .(ChargerType, variable)]
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • "Error: Can't subset columns that don't exist." when running the pivot_longer? – EllisR8 Aug 14 '20 at 10:23
  • @EllisR8 Make sure you are using correct dataframe name and column names that is present in your actual data. The data which you have shared returns an error `Error: unexpected input in "2019_"`. Does it work for you? – Ronak Shah Aug 14 '20 at 10:25
  • I've got it to run with your code. But this gives me the total capacity across all years for each area and charger type. I want the total capacity across all areas for each charger type and year. I would like to create a column for year (2019 to 2050), a charger type column and the total capacity sum(Value). – EllisR8 Aug 14 '20 at 10:34
  • I have edited the code in the question to match with the actual data, wasn't sure how to name the years columns. – EllisR8 Aug 14 '20 at 10:37
  • df %>% tidyr:: pivot_longer(cols = -c(Area, ChargerType)) %>% group_by(ChargerType, name) %>% summarise(sum = sum(value)) This worked for me, the pivot_longer creates a column for the Years called "name" – EllisR8 Aug 14 '20 at 10:39