How to fold multiple columns with common attributes into rows in R?

Question

I am a beginner in R trying to replicate something which I could do in Excel but need R for scale.

I have a dataframe with the following structure:

      Add_0  Sub_0 Mul_0 Add_1 Sub_1 Mul_1 Add_2 Sub_2 Mul_2 ...
Eqn1    5      2     1     6    4      2     1     8    2
Eqn2    4      0     1     3    2      9     7     2    2
Eqn3    7      0     6     3    2      2     3     8    1
Eqn4    1      3     5     0    1      7     1     6    5

I want to group them into multiple data sets and plot a stacked bar plot as follows:

Eqn1 stacked barplot     Eqn2 stacked barplot     ....

Add      Sub       Mul  Add       Sub       Mul
0         0         0    0         0         0
1         1         1    1         1         1
2         2         2    2         2         2
.         .         .    .         .         .
.         .         .    .         .         .
.         .         .    .         .         .

PS: The 0, 1, 2 ... are representations but the data would actually be the corresponding values from Add/Sub/Mul for 0/1/2 for Eqn n on the stacked bar plot.

In Excel, I could just select the row for Eqn n and split them manually, plot each and place them side by side. However, I have 100+ operation types and 100s of equations, so thought R might have a simple solution to use.

I am able to group the Add_0, Add_1, Add_2 using grepl on colnames, but unsure how to end up getting the data into the right format to generate the list of stacked bar plots beside each other, and pass each equation into a function to generate a list for each Eqn.

Thanks in advance.

Answer 1

Perhaps like this?

library(tidyverse)
read_table(" Eqn  Add_0  Sub_0 Mul_0 Add_1 Sub_1 Mul_1 Add_2 Sub_2 Mul_2
            Eqn1      5      2     1     6     4     2     1     8     2
            Eqn2      4      0     1     3     2     9     7     2     2
            Eqn3      7      0     6     3     2     2     3     8     1
            Eqn4      1      3     5     0     1     7     1     6     5") %>%
  pivot_longer(-Eqn, names_to = c("type", "num"), names_sep = "_") %>%
  ggplot(aes(type, value, fill = num)) +
  geom_col() +
  facet_wrap(~Eqn)

Answer 2

Similar to JonSpring's, but a slightly different aesthetic layout. (I was most of the way through this when Jon's answer posted, thought I'd continue it out to be thorough.)

library(dplyr)
library(tidyr) # pivot_longer
library(ggplot2)
pivot_longer(dat, -eqn, names_to=c("key", "index"), names_sep="_", values_to="val") %>%
  mutate(key = factor(key, c("Add", "Sub", "Mul"))) %>%
  ggplot(aes(eqn, val, fill = key, color = key)) +
  geom_col(position="dodge") +
  labs(x = "Equation")