R - change chart format

Question

]1

I have datas that look like the excel table shown in the screen capture and I want to plot it like the picture called meff_exemple. Someone wrote me the following script but it does not work...

dat <- readxl::read_xlsx(  "/Users/XX/Desktop/XXX.xlsx" ) |>
janitor::clean_names()

dat |>
    mutate(meff = str_replace_all(meff, "not calculated", "NA")) |>
    mutate(meff = as.numeric(meff)) |>
    ggplot(aes(year, meff)) +
      geom_col(aes(fill = land_type), position = "dodge") +
      facet_wrap(~place) +    theme_light(base_size = 6)

ggsave(    filename = "lcc_graph_example.png",    plot = last_plot(),
width = 6, height = 3, device = "png", dpi = 300 )

Error in `mutate()`: ! Problem while computing `meff =  
str_replace_all(meff, "not calculated", "NA")`. Caused by error in
`stri_replace_all_regex()`: ! object 'meff' not found Run
`rlang::last_error()` to see where the error occurred.
> ggsave(
+    filename = "lcc_graph_example.png",
+    plot = last_plot(), width = 6, height = 3, device = "png", dpi = 300
+ )
> rlang::last_error() <error/dplyr:::mutate_error> Error in `mutate()`: ! Problem while computing `meff =   str_replace_all(meff,
"not calculated", "NA")`. Caused by error in
`stri_replace_all_regex()`: ! object 'meff' not found
--- Backtrace:
 1. ggplot2::ggplot(...)
 8. stringr::str_replace_all(meff, "not calculated", "NA")
 9. stringi::stri_replace_all_regex(...) Run `rlang::last_trace()` to see the full context.

Answer 1

First I think after using janitors clean_names function your column names change and therefore the error is thrown. Therefore after using clean_names check your colnames before you apply it to ggplot:

With the provided data, NOTE the columnnames differ from your original data:

df <- structure(list(year = c(2006L, 2006L, 2006L, 2012L, 2012L, 2012L, 
2012L, 2018L, 2018L, 2018L, 2018L, 2006L, 2006L, 2006L, 2006L, 
2012L, 2012L, 2012L, 2012L, 2018L, 2018L, 2018L, 2018L, 2006L, 
2006L), Land_type = c("agri", "veg", "water", "builtup", "agri", 
"veg", "water", "builtup", "agri", "veg", "water", "builtup", 
"agri", "veg", "water", "builtup", "agri", "veg", "water", "builtup", 
"agri", "veg", "water", "builtup", "agri"), Number_of = c(527L, 
553L, 109L, 187L, 484L, 563L, 116L, 187L, 488L, 568L, 116L, 275L, 
514L, 576L, 78L, 255L, 553L, 585L, 79L, 260L, 552L, 588L, 80L, 
244L, 321L), Surface_area = c(6685711862.8, 768653815.57, 312468072.81, 
116066157.56, 5262867684.7, 768161871.26, 310017599.41, 116514682.47, 
5260754055.01, 767637869.97, 310153538.11, 178287615.44, 6128954404.04, 
841086242.13, 173774819.17, 177414985.17, 4947201090.32, 860886793.84, 
163631552.84, 181233125.41, 4943016856.72, 858888487.92, 164284392.5, 
181942576.09, 5118818530.69), Meff_Km2 = c("1365.90", "40.20", 
"52.58", "not calculated", "680.67", "41.28", "49.39", "not calculated", 
"945.44", "18.08", "28.89", "not calculated", "1509.17", "18.02", 
"28.07", "not calculated", "826.11", "5.56", "504.28", "not calculated", 
"944.67", "18.07", "28.77", "not calculated", "949.54"), Place = c("Random Layer 1", 
"Random Layer 1", "Random Layer 1", "Random Layer 1", "Random Layer 1", 
"Random Layer 1", "Random Layer 1", "Random Layer 1", "Random Layer 1", 
"Random Layer 1", "Random Layer 1", "Random Layer 2", "Random Layer 2", 
"Random Layer 2", "Random Layer 2", "Random Layer 2", "Random Layer 2", 
"Random Layer 2", "Random Layer 2", "Random Layer 2", "Random Layer 2", 
"Random Layer 2", "Random Layer 2", "Random Layer 3", "Random Layer 3"
)), class = "data.frame", row.names = c(NA, -25L))

We could do:

library(tidyverse)
df %>% 
  as_tibble() %>% 
  mutate(Meff_Km2 = as.numeric(na_if(Meff_Km2, "not calculated"))) %>% 
  ggplot(aes(x=factor(year), y=Meff_Km2, fill=factor(Land_type)))+
  geom_col(position = position_dodge())+
  facet_wrap(.~Place)+
  labs(fill="Land type", x="year", y="meff")+
  theme_bw()

And we will get: