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)