library(tidyverse)

births <- read_csv2("/home/wn/Workspace/r-learnings/lebendgeburten-essen.csv")
births2 <- births %>%
  mutate(StadtbezirkID = factor(Stadtbezirk)) %>%
  mutate(StadtteilID = factor(Stadtteil)) %>%
  select(-c(Stadtbezirk, Stadtteil))

people <- read_csv2("/home/wn/Workspace/r-learnings/bevoelkerung-nach-geschlecht.csv")
people2 <- people %>%
  mutate(StadtbezirkID = factor(Stadtbezirk)) %>%
  mutate(StadtteilID = factor(Stadtteil)) %>%
  select(-c(Stadtbezirk, Stadtteil, INSGESAMT))

people.and.births <- births2 %>% 
  full_join(people2) %>% 
  mutate(birth.per.woman = LEBENDGEBORENE / BEV_W) %>%
  pivot_longer(c(LEBENDGEBORENE, BEV_M, BEV_W, birth.per.woman), names_to="key", values_to="value") %>%
  mutate(keyid = factor(key)) %>%
  select(-c(key)) %>%
  rename(key = keyid)

ax.left.min = min(people.and.births$value[people.and.births$key %in% c("BEV_W", "BEV_M", "LEBENDGEBORENE")], na.rm=TRUE)
ax.left.max = max(people.and.births$value[people.and.births$key %in% c("BEV_W", "BEV_M", "LEBENDGEBORENE")], na.rm=TRUE)
ax.right.min = min(people.and.births$value[people.and.births$key == "birth.per.woman"], na.rm=TRUE)
ax.right.max = max(people.and.births$value[people.and.births$key == "birth.per.woman"], na.rm=TRUE)

ratio = (ax.left.max - ax.left.min) / (ax.right.max - ax.right.min)

plot <- ggplot(data = people.and.births) + 
  geom_bar(mapping = aes(x = StadtbezirkID, y = value, fill = key), stat = "identity", position = "dodge") +
  scale_y_continuous(name = "value", 
                     sec.axis = sec_axis(~(./ratio), name = "ratio"))  
print(plot)