Create pivot table in R-CodePudding

I am trying to create a pivot table in R.

I know there are many different questions for similar issues, but I have tried their solutions and they don't seem to work.

So, can you please help me get from this data frame

Species	Station1	Station2	Station3
Species1	5	5	5
Species2	10	52	0
Species4	0	3	8
Species2	20	15	5
Species3	4	5	5
Species2	6	9	2
Species1	5	10	5

To this data frame

Species	Station1	Station2	Station3
Species1	10	15	10
Species2	36	76	7
Species4	0	3	8
Species3	4	5	5

Thank you for your help!

CodePudding user response：

It would appear us base R users are becoming a dying breed on SO. Here's a one line solution without the addition of any extra packages.

xy <- read.table(text = "Species    Station1    Station2    Station3
Species1    5   5   5
Species2    10  52  0
Species4    0   3   8
Species2    20  15  5
Species3    4   5   5
Species2    6   9   2
Species1    5   10  5", sep = "\t", header = TRUE)

aggregate(. ~ Species, data = xy, FUN = sum)

    Species Station1 Station2 Station3
1 Species1        10       15       10
2 Species2        36       76        7
3 Species3         4        5        5
4 Species4         0        3        8

CodePudding user response：

You can use dplyr

library(dplyr)

df <- tribble(
  ~Species, ~Station1, ~Station2, ~Station3,
  "Species1",   5,  5,  5,
  "Species2",   10, 52, 0,
  "Species4",   0,  3,  8,
  "Species2",   20, 15, 5,
  "Species3",   4,  5,  5,
  "Species2",   6,  9,  2,
  "Species1",   5,  10, 5
)

df %>% 
  group_by(Species) %>% 
  summarise(across(everything(), sum))

Result:

# A tibble: 4 × 4
  Species  Station1 Station2 Station3
  <chr>       <dbl>    <dbl>    <dbl>
1 Species1       10       15       10
2 Species2       36       76        7
3 Species3        4        5        5
4 Species4        0        3        8