library("tidyverse")
library("readxl")

d <- read_excel("baza.xlsx")

tab0 <- d %>% 
  group_by(Mucilage, `Plant species`) %>% 
  summarize(
    Seeds = sum(`Seeds count from pigeon`),
    Germination = sum(`Germination energy`),
    Control = sum(Control)
  ) %>% 
  mutate(
    Seeds_perc = round(Seeds / 2700 * 100, 1),
    Germination_perc = round(Germination / Seeds * 100, 1),
    Control_perc = round(Control / Seeds * 100, 1)
  )
tab0

tab <- d %>% 
  group_by(Mucilage) %>% 
  summarize(
    Seeds = sum(`Seeds count from pigeon`),
    Germination = sum(`Germination energy`),
    Control = sum(Control)
  ) %>% 
  mutate(
    Seeds_perc = round(Seeds / (2700 * ifelse(Mucilage == 1, 7, 3)) * 100, 1),
    Germination_perc = round(Germination / Seeds * 100, 1),
    Control_perc = round( Control / Seeds * 100, 1)
  )
tab

tab2 <- data.frame(tab$Seeds, 2700 * ifelse(tab$Mucilage == 1, 7, 3) - tab$Seeds)
colnames(tab2) <- c("Seed count from pigeon", "Rest")
rownames(tab2) <- c("Without mucilage", "Mucilage")
tab2
chisq.test(tab2)

pic3 <- data.frame(
  `From pigeon` = c("Yes", "Yes", "No", "No"),
  Mucilage = c("No", "Yes", "No", "Yes"),
  Seeds = unlist(tab2)
)
names(pic3)[1] <- "From pigeon"
ggplot(pic3, aes(Mucilage, Seeds, fill = `From pigeon`)) +
  geom_col()

x <- c(tab$Germination[2], tab$Control[2])
tab3 <- data.frame(x, tab$Seeds[2] - x)
colnames(tab3) <- c("Germination", "Rest")
rownames(tab3) <- c("Experimental group", "Control")
tab3
chisq.test(tab3)

pic3 <- data.frame(
  Germination = c("Yes", "Yes", "No", "No"),
  Group = c("Experimental", "Control", "Experimental", "Control"),
  Seeds = unlist(tab3)
)
ggplot(pic3, aes(Group, Seeds, fill = Germination)) +
  geom_col()