英文:
How to get maxium value of a group and corresponding value from another column of the original dataset using dplyr
问题
Here's a simplified solution to achieve your goal:
library(dplyr)
max_fatality <- fire_data %>%
group_by(Description.of.fire.accident.scene, Type) %>%
summarize(Total_fatality = sum(dead)) %>%
group_by(Type) %>%
slice(which.max(Total_fatality)) %>%
ungroup() %>%
select(Description.of.fire.accident.scene, Type, Total_fatality)
max_fatality
This code does the following:
- Groups the data by
Description.of.fire.accident.sceneandType. - Calculates the total fatality (
Total_fatality) for each combination of scene description and type. - Groups the data by
Typeand selects the row with the maximum total fatality for each type. - Ungroups the data and selects the desired columns.
This should give you the expected result in a simpler and more concise way.
英文:
I have a dataset which is:
fire_data <- data.frame(Year = c(2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,
2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,
2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,
2022,2022,2022,2022,2022,
2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,
2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,
2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,2021,
2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,
2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,
2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020
),
Description.of.fire.accident.scene = c("factory-industry fire",
"factory-industry fire", "factory-industry fire", "slum fire",
"slum fire", "slum fire", "gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire"),
Type = c("male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee"
),
injured = c(35, 5, 17, 21, 8, 0, 14, 7, 0, 28, 2, 0, 5, 1,
0, 9, 9, 4, 15, 5, 0, 28, 6, 3, 6, 0, 0, 35, 7, 1, 5, 2, 0, 1,
2, 0, 101, 20, 5, 70, 39, 0, 5, 5, 0, 62, 29, 0, 59, 16, 0, 3,
0, 0, 18, 16, 4, 47, 20, 1, 27, 7, 0, 8, 5, 0, 43, 2, 1, 11,
4, 0, 0, 0, 0, 64, 10, 0, 21, 0, 0, 22, 10, 3, 30, 10, 0, 49,
18, 0, 5, 0, 0, 10, 5, 5, 27, 20, 2, 20, 8, 0, 0, 4, 0, 24, 1,
2, 3, 0, 3, 0, 0, 0, 28, 2, 0),
dead = c(47, 3, 13, 2, 4, 0,
7, 0, 0, 0, 1, 0, 0, 0, 0, 4, 2, 0, 1, 0, 0, 0, 0, 0, 4, 0, 0,
2, 0, 0, 0, 0, 0, 0, 0, 0, 5, 3, 0, 57, 85, 0, 1, 1, 0, 1, 1,
0, 5, 3, 0, 0, 0, 0, 3, 3, 0, 11, 1, 0, 12, 5, 0, 0, 0, 0, 11,
0, 0, 0, 0, 0, 0, 0, 0, 15, 4, 0, 4, 1, 0, 7, 5, 0, 2, 2, 0,
6, 0, 0, 1, 0, 0, 1, 2, 0, 3, 1, 0, 9, 89, 0, 5, 0, 0, 5, 4,
0, 0, 0, 0, 0, 0, 0, 5, 2, 0)
)
My goal is to get a tibble which will show Description.of.the.fire.scene with the total (for all year) maximum number of Death of each Type like this photo.
Now for the data set given in the link, I at first tried this one:
max_fatality <- fire_data |>
group_by(Description.of.fire.accident.scene, Type) |>
summarize(
Total_fatality = sum(dead),
) |>
group_by(Type) |>
summarise(max_total_fatality = max(Total_fatality))
max_fatality
Now this gives me the following tibble:
which is close to my solution by I want the corresponding Description.of.the.fire.scene also. So with the help of other stackoverflow questions, I improvise:
max_fatality <- fire_data |>
group_by(Description.of.fire.accident.scene, Type) |>
summarize(
Total_fatality = sum(dead),
) |>
group_by(Type) |>
mutate(
fatality_count = max(Total_fatality),
new_value = Description.of.fire.accident.scene[which.max(Total_fatality)])|>
filter(
Total_fatality == fatality_count
) |>
select(1, 2, 4)
max_fatality
This gives me my expected result:
But I think my solution is very much clunky. Can you suggest me a simple solution.
答案1
得分: 1
以下是翻译好的部分：
"看起来你想要筛选出每个类别类型的最大值"
这段代码应该有效：
library(dplyr)
fire_data |>
summarise(max_total_fatality = sum(dead), .by = c(Description.of.fire.accident.scene, Type)) |>
filter(max_total_fatality == max(max_total_fatality), .by = Type)
Description.of.fire.accident.scene Type max_total_fatality
1 factory-industry fire 男性 108
2 factory-industry fire fire employee 13
3 vehicle fire 女性 94
英文:
It seems like you're looking to filter out the max for each category type
This code should work:
library(dplyr)
fire_data |>
summarise(max_total_fatality = sum(dead), .by = c(Description.of.fire.accident.scene, Type)) |>
filter(max_total_fatality == max(max_total_fatality), .by = Type)
Description.of.fire.accident.scene Type max_total_fatality
1 factory-industry fire male 108
2 factory-industry fire fire employee 13
3 vehicle fire female 94
答案2
得分: 0
以下是您要翻译的内容：
"One way could be:
In case of ties you will get more lines per group like the first 3 rows:
library(dplyr)
fire_data %>%
group_by(Description.of.fire.accident.scene, Type) %>%
summarise(max_total_fatality = sum(dead)) %>%
slice_max(max_total_fatality)
data:
Description.of.fire.accident.scene Type max_total_fatality
<chr> <chr> <dbl>
1 KPI and govt. institute fire female 0
2 KPI and govt. institute fire fire employee 0
3 KPI and govt. institute fire male 0
4 boiler fire male 1
5 education institute fire female 0
6 education institute fire fire employee 0
7 education institute fire male 0
8 factory-industry fire male 108
9 gas cylinder fire male 11
10 gas distribution pipeline fire male 10
11 hospital fire male 9
12 market-haat-bazaar-shop fire male 18
13 multi-storied building fire male 8
14 other building fire male 15
15 other scene fire male 25
16 slum fire female 10
17 slum fire male 10
18 vehicle fire female 94
fire_data <- structure(list(year = c(2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020), Description.of.fire.accident.scene = c("factory-industry fire",
"factory-industry fire", "factory-industry fire", "slum fire",
"slum fire", "slum fire", "gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution
<details>
<summary>英文:</summary>
One way could be:
In case of ties you will get more lines per group like the first 3 rows:
library(dplyr)
fire_data %>%
group_by(Description.of.fire.accident.scene, Type) %>%
summarise(max_total_fatality = sum(dead)) %>%
slice_max(max_total_fatality)
data:
Description.of.fire.accident.scene Type max_total_fatality
<chr> <chr> <dbl>
1 KPI and govt. institute fire female 0
2 KPI and govt. institute fire fire employee 0
3 KPI and govt. institute fire male 0
4 boiler fire male 1
5 education institute fire female 0
6 education institute fire fire employee 0
7 education institute fire male 0
8 factory-industry fire male 108
9 gas cylinder fire male 11
10 gas distribution pipeline fire male 10
11 hospital fire male 9
12 market-haat-bazaar-shop fire male 18
13 multi-storied building fire male 8
14 other building fire male 15
15 other scene fire male 25
16 slum fire female 10
17 slum fire male 10
18 vehicle fire female 94
fire_data <- structure(list(year = c(2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022,
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2021,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2021, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020
), Description.of.fire.accident.scene = c("factory-industry fire",
"factory-industry fire", "factory-industry fire", "slum fire",
"slum fire", "slum fire", "gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire", "factory-industry fire", "factory-industry fire",
"factory-industry fire", "slum fire", "slum fire", "slum fire",
"gas distribution pipeline fire", "gas distribution pipeline fire",
"gas distribution pipeline fire", "gas cylinder fire", "gas cylinder fire",
"gas cylinder fire", "boiler fire", "boiler fire", "boiler fire",
"multi-storied building fire", "multi-storied building fire",
"multi-storied building fire", "other building fire", "other building fire",
"other building fire", "vehicle fire", "vehicle fire", "vehicle fire",
"hospital fire", "hospital fire", "hospital fire", "market-haat-bazaar-shop fire",
"market-haat-bazaar-shop fire", "market-haat-bazaar-shop fire",
"KPI and govt. institute fire", "KPI and govt. institute fire",
"KPI and govt. institute fire", "education institute fire", "education institute fire",
"education institute fire", "other scene fire", "other scene fire",
"other scene fire"), Type = c("male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee",
"male", "female", "fire employee", "male", "female", "fire employee"
), injured = c(35, 5, 17, 21, 8, 0, 14, 7, 0, 28, 2, 0, 5, 1,
0, 9, 9, 4, 15, 5, 0, 28, 6, 3, 6, 0, 0, 35, 7, 1, 5, 2, 0, 1,
2, 0, 101, 20, 5, 70, 39, 0, 5, 5, 0, 62, 29, 0, 59, 16, 0, 3,
0, 0, 18, 16, 4, 47, 20, 1, 27, 7, 0, 8, 5, 0, 43, 2, 1, 11,
4, 0, 0, 0, 0, 64, 10, 0, 21, 0, 0, 22, 10, 3, 30, 10, 0, 49,
18, 0, 5, 0, 0, 10, 5, 5, 27, 20, 2, 20, 8, 0, 0, 4, 0, 24, 1,
2, 3, 0, 3, 0, 0, 0, 28, 2, 0), dead = c(47, 3, 13, 2, 4, 0,
7, 0, 0, 0, 1, 0, 0, 0, 0, 4, 2, 0, 1, 0, 0, 0, 0, 0, 4, 0, 0,
2, 0, 0, 0, 0, 0, 0, 0, 0, 5, 3, 0, 57, 85, 0, 1, 1, 0, 1, 1,
0, 5, 3, 0, 0, 0, 0, 3, 3, 0, 11, 1, 0, 12, 5, 0, 0, 0, 0, 11,
0, 0, 0, 0, 0, 0, 0, 0, 15, 4, 0, 4, 1, 0, 7, 5, 0, 2, 2, 0,
6, 0, 0, 1, 0, 0, 1, 2, 0, 3, 1, 0, 9, 89, 0, 5, 0, 0, 5, 4,
0, 0, 0, 0, 0, 0, 0, 5, 2, 0)), row.names = c(NA, -117L), spec = structure(list(
cols = list(year = structure(list(), class = c("collector_double",
"collector")), Description.of.fire.accident.scene = structure(list(), class = c("collector_character",
"collector")), Type = structure(list(), class = c("collector_character",
"collector")), injured = structure(list(), class = c("collector_double",
"collector")), dead = structure(list(), class = c("collector_double",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), delim = ","), class = "col_spec"), problems = <pointer: 0x0000026ef8a79610>, class = c("spec_tbl_df",
"tbl_df", "tbl", "data.frame"))
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论