英文:
Combine rows with same date and site
问题
我有一个像这样的数据框:
我想将具有相同站点ID和日期的物质的值移动到一行。我该怎么做?也许可以用aggregate
、match
或merge
吗?
英文:
I have a dataframe like this:
I want to move the values of the substances that have the same station ID and date on an unique row. How can I do that? maybe with aggregate
or match
or merge
?
答案1
得分: 0
如果我们想要删除NA元素,只保留非NA元素,可以进行分组汇总并得到sum
。
library(dplyr)
df1 %>%
group_by(station, date) %>%
summarise(across(everything(),
~ sum(.x, na.rm = TRUE)), .groups = 'drop')
或者在分组后使用条件返回第一个非NA值。
df1 %>%
group_by(station, date) %>%
summarise(across(everything(), ~if(all(is.na(.x))) NA else
.x[!is.na(.x)][1]))
或者使用base R
方法:
aggregate(.~ station + date, df1, sum, na.rm = TRUE, na.action = NULL)
英文:
If we want to remove the NA elements and keep only the non-NA, do a grouping by summarise and get the sum
library(dplyr)
df1 %>%
group_by(station, date) %>%
summarise(across(everything(),
~ sum(.x, na.rm = TRUE)), .groups = 'drop')
Or use a condition to return the first non-NA after grouping
df1 %>%
group_by(station, date) %>%
summarise(across(everything(), ~if(all(is.na(.x))) NA else
.x[!is.na(.x)][1]))
Or with base R
aggregate(.~ station + date, df1, sum, na.rm = TRUE, na.action = NULL)
答案2
得分: -1
你是指这个吗?
library(tidyverse)
# 定义数据集
df1 <- tibble::tribble(
~station, ~date, ~substance1,
1, "08/07/2009", "5.7",
2, "13/07/2009", "6",
3, "13/07/2009", "2.2",
4, "13/07/2009", "2",
5, "22/09/2009", "1"
)
df2 <- tibble::tribble(
~station, ~date, ~substance4,
1, "08/07/2009", 2,
2, "13/07/2009", 6,
4, "13/07/2009", 3
)
# 按照站点和日期合并
full_join(df1, df2)
#> Joining with `by = join_by(station, date)`
#> # A tibble: 5 × 4
#> station date substance1 substance4
#> <dbl> <chr> <chr> <dbl>
#> 1 1 08/07/2009 5.7 2
#> 2 2 13/07/2009 6 6
#> 3 3 13/07/2009 2.2 NA
#> 4 4 13/07/2009 2 3
#> 5 5 22/09/2009 1 NA
创建于2023-03-03,使用reprex v2.0.2。
英文:
Do you mean this?
library(tidyverse)
# define datasets
df1 <- tibble::tribble(
~station, ~date, ~substance1,
1, "08/07/2009", "5.7",
2, "13/07/2009", "6",
3, "13/07/2009", "2.2",
4, "13/07/2009", "2",
5, "22/09/2009", "1"
)
df2 <- tibble::tribble(
~station, ~date, ~substance4,
1, "08/07/2009", 2,
2, "13/07/2009", 6,
4, "13/07/2009", 3
)
# merge by station and date
full_join(df1, df2)
#> Joining with `by = join_by(station, date)`
#> # A tibble: 5 × 4
#> station date substance1 substance4
#> <dbl> <chr> <chr> <dbl>
#> 1 1 08/07/2009 5.7 2
#> 2 2 13/07/2009 6 6
#> 3 3 13/07/2009 2.2 NA
#> 4 4 13/07/2009 2 3
#> 5 5 22/09/2009 1 NA
<sup>Created on 2023-03-03 with reprex v2.0.2</sup>
Please be sure to include code to create the datasets and a proper description of your desired output next time.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论