合并具有相同日期和站点的行

huangapple go评论69阅读模式
英文:

Combine rows with same date and site

问题

我有一个像这样的数据框:

合并具有相同日期和站点的行

我想将具有相同站点ID和日期的物质的值移动到一行。我该怎么做?也许可以用aggregatematchmerge吗?

英文:

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 aggregateor matchor 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 &lt;- tibble::tribble(
  ~station,        ~date, ~substance1,
         1, &quot;08/07/2009&quot;,       &quot;5.7&quot;,
         2, &quot;13/07/2009&quot;,         &quot;6&quot;,
         3, &quot;13/07/2009&quot;,       &quot;2.2&quot;,
         4, &quot;13/07/2009&quot;,         &quot;2&quot;,
         5, &quot;22/09/2009&quot;,         &quot;1&quot;
  )

df2 &lt;- tibble::tribble(
  ~station,       ~date, ~substance4,
  1, &quot;08/07/2009&quot;,           2,
  2, &quot;13/07/2009&quot;,           6,
  4, &quot;13/07/2009&quot;,           3
)

# merge by station and date
full_join(df1, df2)
#&gt; Joining with `by = join_by(station, date)`
#&gt; # A tibble: 5 &#215; 4
#&gt;   station date       substance1 substance4
#&gt;     &lt;dbl&gt; &lt;chr&gt;      &lt;chr&gt;           &lt;dbl&gt;
#&gt; 1       1 08/07/2009 5.7                 2
#&gt; 2       2 13/07/2009 6                   6
#&gt; 3       3 13/07/2009 2.2                NA
#&gt; 4       4 13/07/2009 2                   3
#&gt; 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.

huangapple
  • 本文由 发表于 2023年3月4日 01:57:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/75630402.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定