CODEWITHSUNDEEP
rdataframedplyrreshapedata-cleaning
ngwoke chukwubuikem
ngwoke chukwubuikem

Reputation: 37

Reducing multiple readings of observations to one (the mean) across a dataframe in R

In my dataframe, i have 3 readings of each variable for a each observation. How can i reduce the dataframe to 1 reading of each variable by taking the mean of the 3 readings.

Here is the first few rows of my data

SPECIES   LOCATION X.COLONIZATION SPORE_DENSITY   pH  NO3  NH4    P Organic_C      K   Cu    Mn   Zn   BD X.Sand
1 C. comosa    Gauteng             90           387 5.40 8.24 1.35 1.10      0.95  94.40 3.36 84.40 4.72 1.45   68.0
2 C. comosa    Gauteng             84           270 5.25 8.36 1.37 1.20      0.99  94.87 3.39 84.87 4.77 1.36   76.0
3 C. comosa    Gauteng             96           404 5.55 8.19 1.32 1.11      0.94  94.01 3.35 84.01 4.68 1.54   78.0
4 C. comosa Mpumalanga             79           382 5.84 4.05 3.46 3.04      1.55 130.40 0.28 25.43 2.00 1.66   73.6
5 C. comosa Mpumalanga             82           383 5.49 4.45 3.48 3.09      1.53 131.36 0.27 25.35 2.12 1.45   76.5
6 C. comosa Mpumalanga             86           371 6.19 4.43 3.44 3.04      1.58 129.95 0.29 25.45 2.14 1.87   74.9
  X.Silt X.Clay
1     12      9
2     16     13
3     14     14
4      9     10
5     11     16
6     13     16

so i want to have the row 1 to 3 reduced to 1 row by taking average of each variable for the specie (and location). same with row 4 to 6 reducing to 1 row and so on.

i have tried using reshape, tapply, summarize but all to no avail.

Thank you

Upvotes: 1

Views: 254

Answers (3)

Parfait
Parfait

Reputation: 107767

Simply run aggregate with dot in formula:

aggregate(. ~ SPECIES + LOCATION, mydata, FUN=mean)

Output

#     SPECIES   LOCATION X.COLONIZATION SPORE_DENSITY   pH      NO3      NH4        P Organic_C         K       Cu       Mn       Zn   BD X.Sand X.Silt X.Clay
# 1 C. comosa    Gauteng       90.00000      353.6667 5.40 8.263333 1.346667 1.136667  0.960000  94.42667 3.366667 84.42667 4.723333 1.45     74     14     12
# 2 C. comosa Mpumalanga       82.33333      378.6667 5.84 4.310000 3.460000 3.056667  1.553333 130.57000 0.280000 25.41000 2.086667 1.66     75     11     14

Online Demo

Upvotes: 0

Allan Cameron
Allan Cameron

Reputation: 174506

You can use dplyr to group_by SPECIES and LOCATION, then summarize_all:

library(dplyr)

df %>% group_by(SPECIES, LOCATION) %>% summarise_all(mean)
#> # A tibble: 2 x 17
#> # Groups:   SPECIES [1]
#>   SPECIES LOCATION X.COLONIZATION SPORE_DENSITY    pH   NO3   NH4     P
#>   <fct>   <fct>             <dbl>         <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 C.como~ Gauteng            90            354.  5.4   8.26  1.35  1.14
#> 2 C.como~ Mpumala~           82.3          379.  5.84  4.31  3.46  3.06
#> # ... with 9 more variables: Organic_C <dbl>, K <dbl>, Cu <dbl>, Mn <dbl>,
#> #   Zn <dbl>, BD <dbl>, X.Sand <dbl>, X.Silt <dbl>, X.Clay <dbl>

Created on 2020-06-24 by the reprex package (v0.3.0)

Upvotes: 0

Ian Campbell
Ian Campbell

Reputation: 24878

Here's a simple approach with the new across functionality. Note that columns currently being group_by-ed are not included in everything().

library(dplyr) #version >= 1.0.0
data %>%
  group_by(SPECIES, LOCATION) %>%
  summarise(across(everything(), mean))
## A tibble: 2 x 17
## Groups:   SPECIES [1]
#  SPECIES   LOCATION   X.COLONIZATION SPORE_DENSITY    pH   NO3   NH4     P Organic_C     K    Cu    Mn    Zn    BD X.Sand X.Silt X.Clay
#  <fct>     <fct>               <dbl>         <dbl> <dbl> <dbl> <dbl> <dbl>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>  <dbl>  <dbl>
#1 C. comosa Gauteng              90            354.  5.4   8.26  1.35  1.14      0.96  94.4  3.37  84.4  4.72  1.45     74     14     12
#2 C. comosa Mpumalanga           82.3          379.  5.84  4.31  3.46  3.06      1.55 131.   0.28  25.4  2.09  1.66     75     11     14

Sample Data

data <- structure(list(SPECIES = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = "C. comosa", class = "factor"), 
    LOCATION = structure(c(1L, 1L, 1L, 2L, 2L, 2L), .Label = c("Gauteng", 
    "Mpumalanga"), class = "factor"), X.COLONIZATION = c(90L, 
    84L, 96L, 79L, 82L, 86L), SPORE_DENSITY = c(387L, 270L, 404L, 
    382L, 383L, 371L), pH = c(5.4, 5.25, 5.55, 5.84, 5.49, 6.19
    ), NO3 = c(8.24, 8.36, 8.19, 4.05, 4.45, 4.43), NH4 = c(1.35, 
    1.37, 1.32, 3.46, 3.48, 3.44), P = c(1.1, 1.2, 1.11, 3.04, 
    3.09, 3.04), Organic_C = c(0.95, 0.99, 0.94, 1.55, 1.53, 
    1.58), K = c(94.4, 94.87, 94.01, 130.4, 131.36, 129.95), 
    Cu = c(3.36, 3.39, 3.35, 0.28, 0.27, 0.29), Mn = c(84.4, 
    84.87, 84.01, 25.43, 25.35, 25.45), Zn = c(4.72, 4.77, 4.68, 
    2, 2.12, 2.14), BD = c(1.45, 1.36, 1.54, 1.66, 1.45, 1.87
    ), X.Sand = c(68, 76, 78, 73.6, 76.5, 74.9), X.Silt = c(12L, 
    16L, 14L, 9L, 11L, 13L), X.Clay = c(9L, 13L, 14L, 10L, 16L, 
    16L)), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5", "6"))

Upvotes: 1

Related Questions