How to prepare data for multi barplot on R? (rearrange, merge and change the columns into a new table ready for plotting)

Question

I'm a newbie having a hard time to plot a multi bar graph/plot. Apologize in advance for using weird/wrong terms. I'm working on Funding for the Arts dataset. My current table looks like below (budget numbers are made up for this example)

Category   2010/11   2011/12   2012/13   ...   Total
Music      10000     900000000 8900000         98000000000
Dance      0         8759432   0               789999999
Theatre    7800000   23535352  32424           94832000

1. List item

---

I want to create a new dataset that includes all the data looks (something) like below.

Category      Year       Budget
Music         2010/11    10000
Dance         2011/12    8759432
Theatre       2011/12    23535352

So that I can use it to plot a multi barplot/ graph showing the different allocations of fund to different categories in different years.

Thank you.

Answer 1

You can convert you dataset via pivot_longer from the tidyr package. I also added a mutate to get the year labels right. Try this:

library(dplyr)
library(tidyr)

# Example data

df <- read.table(text="Category   2010/11   2011/12   2012/13   Total
Music      10000     900000000 8900000         98000000000
Dance      0         8759432   0               789999999
Theatre    7800000   23535352  32424           94832000", header = TRUE)
df
#>   Category X2010.11  X2011.12 X2012.13      Total
#> 1    Music    10000 900000000  8900000 9.8000e+10
#> 2    Dance        0   8759432        0 7.9000e+08
#> 3  Theatre  7800000  23535352    32424 9.4832e+07

# Tidy the dataset

df_tidy <- df %>%
  # drop Total. If you need that column: remove the code line
  select(-Total) %>% 
  # Convert to long
  pivot_longer(-Category, names_to = "Year", values_to = "Budget") %>% 
  # Get the labels for the Year right   
  mutate(Year = gsub("^X", "", Year),
         Year = gsub("\\.", "/", Year))
df_tidy
#> # A tibble: 9 x 3
#>   Category Year       Budget
#>   <fct>    <chr>       <int>
#> 1 Music    2010/11     10000
#> 2 Music    2011/12 900000000
#> 3 Music    2012/13   8900000
#> 4 Dance    2010/11         0
#> 5 Dance    2011/12   8759432
#> 6 Dance    2012/13         0
#> 7 Theatre  2010/11   7800000
#> 8 Theatre  2011/12  23535352
#> 9 Theatre  2012/13     32424

^{Created on 2020-04-05 by the reprex package (v0.3.0)}