Merging Rows with the same Row Value within a dataframe

[FrankTheTank97]

Hello everyone, I have a dataset that looks like this:

I am trying to merge all the rows that have the same name within the column "Tree" and have all values in the other columns summarized. I have tried with:

dfx <- df %>%
group_by(Tree) %>%
summarize_all(...) (I've tried multiple things here)

(and some other solutions I found, but they didn't fit my case.)

but either got a dataframe that included JUST the Tree column or a very weird dataframe where everything was merged appropriately but values were shown as binary (how did that even happen???)

I'm sorry if the solution is actually an easy one, but I haven't found a solution where it wasnt based on multiple columns and I want to refrain from listing 335 columns in my code.

Thanks in advance

[arthur.t]

Try this!

dfx <- df %>% group_by(Tree) %>% summarize_if(is.numeric, mean, na.rm = TRUE, .groups = "drop")

If you're seeing weird results, check the types of your columns with sapply(df, class). I can't tell from your snapshot. It's possible some of those columns that you think are numeric are actually factor or character, in which case the mean will fail.

[FrankTheTank97]

Hi, thanks for the quick reply,
the function itself works, but the values are messed up. It looks like this now

[arthur.t]

Curious -how are the values messed up? Impossible for me to see the whole set of results from the snapshot, but at least for these rows it looked like the values were all zero, and then upon taking the means, the means are sensibly zero too.

[FrankTheTank97]

oh yea, I want the values of the columns to be summarized, not meaned. So if there is for example

Tree Acrotona.aterrima
mof_cst_00020 0
mof_cst_00020 1
mof_cst_00020 1
mof_cst_00020 1
mof_cst_00020 0

I want it to be

Tree Acrotona.aterrima
mof_cst_00020 3

Not 0.6 as the species value

[FrankTheTank97]

well... the format of the last reply didnt quite work as intended.. but the 0 1 1 1 0 and the 3 should be for the species column

[arthur.t]

You want to count the nonzero values? Try this.

dfx <- df %>% group_by(Tree) %>% summarize_if(is.numeric, ~sum(.[. != 0]), .groups = "drop")

[FrankTheTank97]

I want the values of the rows added together. This is species abundance data on different trees. So one tree was counted on seperate months, which is why there are multiple cases of the same trees. I want the amount of individuals captured per months added up, so that I have the total of the number of individuals captured.

[arthur.t]

Just a sum, then? You can put whatever function or tilde function in that second argument.

dfx <- df %>% group_by(Tree) %>% summarize_if(is.numeric, sum, .groups = "drop")

[FrankTheTank97]

Then I get this error: problem with summarise() input Acrotona.aterrima.
x invalid'type' (character) of the argument
i Input Acrotona.aterrima is .Primitive("sum")(Acrotona.aterrima, .groups = "drop").
i The error occurred in group 1: Tree = "mof_cst_00001".

[arthur.t]

Hm.. not sure. Did you overload sum? What do you see if you print(sum)?

Does this work?
iris %>% group_by(Species) %>% summarize_if(is.numeric, sum)

[FrankTheTank97]

This is what I got

[arthur.t]

OK, then I feel like it should have worked. Try it again.

Perhaps you're still trying the summarize_all?

Also useful to do a sapply(df, class). I wonder if some of your columns that appear numeric are stored as factors or character.

[FrankTheTank97]

For sapply it says "integer" for every species and "character" for "Tree"

And im using the exact code you wrote to me, just replaced the dfx and df with the actual dataframe names

[FrankTheTank97]

I have removed the . groups = "drop" and now it seems to work.

Thank you for your assistance and your patience.

[arthur.t]

No prob! Maybe you're using an older version of dplyr. The .groups argument was added about a year ago I think.