The text we overlapping

library(openxlsx)
library(dplyr)
library(magrittr)
library(extrafont)
#font_import(paths = c("H:\\monika_desktop\\cross_country_with_R\\verdanab.ttf", prompt = TRUE))
loadfonts(device = "win", quiet = TRUE)
library(ggplot2)
library(grid)
my_trend <- png::readPNG("./trend2.png")
rast <- grid::rasterGrob(my_trend, interpolate = T, width=unit(2.10,"cm"), x=0.61, y=1.152) 
#vector to create legend
cols <- c("Article Score %"="#0089cb", "No. of Active Articles"="#003b7e")
df0 <- read.xlsx("buy_ES_cross_country.xlsx")
df <- df0 %>%
  arrange(-score, -articles)
df$country <- factor(df$country, levels = df$country)
#create min and max values for y and x1 axes
y1 <- min(df$articles)
y2 <- max(df$articles)
second_y1 <- 25
second_y2 <- 100
#coefficients for axes
b <- (y2 - y1) / (second_y2 - second_y1)
a <- y1 - b * second_y1
#graph
graph <-
  df %>%
  mutate(score.scaled = a + b * score) %>%
  ggplot(aes(country, articles, label=sprintf("%0.2f", round(trend, digits = 2)))) +
  geom_col(
    data = df %>% distinct(country, .keep_all = TRUE), width = 0.6,
    aes(country, articles, fill="No. of Active Articles")) + 
 # geom_point(aes(country, score.scaled*1.05, colour='Article Score %')) +
  geom_label(aes(label=sprintf("%0.1f", round(score, digits = 2)), y = score.scaled), fill='white', size=3.8, fontface='bold', vjust=-1, label.size = NA) +
  scale_y_continuous(
    sec.axis = sec_axis(~ (. - a) / b), labels=function(x) format(x, big.mark = ",", scientific = FALSE), expand = c(0,0)) +
  coord_cartesian(ylim = c(0, max(df$articles)), clip='off') +
  theme_bw() + theme(panel.border = element_blank(), panel.grid.major = element_blank(),
                     panel.grid.minor = element_blank(), axis.line = element_line(colour = "black")) +
  theme(axis.title.x=element_blank(), axis.title.y=element_blank(), axis.text.x = element_text(size=9, face="bold", colour = "black"), axis.text.y = element_text(angle = 90, vjust = 0.5, hjust=0.7, size=9, face="bold", colour = "black")) +
  geom_label(aes(label=sprintf("%0.2f", round(trend, digits = 2)), y = score.scaled*0.95), fill=ifelse(df$trend < 0, '#e52d12', ifelse(df$trend == 0, '#FFFF00', '#00b050')), color=ifelse(df$trend == 0,'black','white'), size=3, fontface='bold', label.size=NA, label.padding = unit(0.2, "lines"), vjust = -0.1)
  graph2 <- graph + geom_text(data=subset(df, articles > max(df$articles) * 0.0125), aes(x=country, y = articles, label = format(articles, big.mark = ",", scientific = FALSE)), angle=90, color='white', size = 3.5, position = position_stack(vjust = 0.5)) +geom_text(data=subset(df, articles <= 1300), aes(label = format(articles, big.mark = ",", scientific = FALSE)), angle=90, color='black', size = 3.5, position = position_stack(vjust = 2)) +
  theme(axis.text.y.right = element_text(angle = 0, size=9, face="bold", colour = "black")) +
  scale_color_manual(values=cols) +
  scale_fill_manual(values=cols) +
  theme(legend.position = "top", legend.justification='right', legend.box.margin=margin(0,0,10,0), legend.title=element_blank(), legend.box = "horizontal", legend.text=element_text(size=9, face='bold', family="Verdana")) +
  annotation_custom(rast) +
  labs(tag = "Trend") +
  theme(plot.tag.position = c(0.66, 0.952), plot.tag=element_text(size=9, face='bold', family="Verdana")) 
print(graph2)
#add empty column 
df$empty_col <- NA 
#save df to Excel
write.xlsx(df, 'output_01_buy_ES_cross_country.xlsx')
ggsave("ES_buyv2.png", plot = graph2, width = 12.5, height = 4.3, dpi = 300, units = "in", device='png')

1 Like

Hi @deepika.shinde , remember put a reproducible example of data for better help you all the community.

# If the data frame is data
dput(data[ 1:40 , ] )  # the 1 to 40 rows and all the columns.