Create bubble chart using two data sets

Question

I have two data sets that contain the same rows but diffrent columns except one column that is commun between them (Order column)

I want to create bubble chart like the one in the picture

example of the two data sets i have data 1

   SFA14  SFB78  SFC88   
W  15     88       4
X  100    15       4
Y    8    10      10
Z   12     0      15


data2
   LA128  LBHY    LCPP
W 100     10       17
X  10     1000    540
Y  00     10       78
Z  87     154     895

In my code , i have different columns of the two data represented by the color

what i want is to create the same chart as the one ine the picture so the two data sets will be separated and not represented by the colors

The code i used to obtain chart where columns are represented by colors :

# Combine the datasets based on the common Taxon column
combined_data <- merge(data1, data2, by = "Order", all = TRUE)

# Reshape the data for plotting
data_long <- combined_data %>%
  pivot_longer(cols = starts_with("SF") | starts_with("L"), names_to = "Variable", values_to = "Value")

# Create the bubble chart with different colors for each dataset
ggplot(data_long, aes(x = Order, y = Value, size = Value, color = Variable)) +
  geom_point(alpha = 0.7) +
  scale_size_continuous(range = c(5, 20)) +
  labs(title = "Combined Bubble Chart Example", x = "Taxonomic Groups", y = "Values", size = "Value", color = "Variable") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

The result is

Answer 1

Based on your raw datasets you can achieve your desired result by first renaming your columns, i.e. get rid of the prefix identifying the dataset. Afterwards put your datasets in a named list and bind them by rows using dplyr::bind_rows adding an identifier for the datasets to your data. This identifier can then be used to facet your plot by dataset:

library(tidyverse)

# Rename columns. Get rid of prefix
names(data1) <- gsub("^SF", "", names(data1))
names(data2) <- gsub("^L", "", names(data2))

data_long <- list(SF = data1, L = data2) |>
  bind_rows(.id = "dataset") |>
  pivot_longer(-c(dataset, Order),
    names_to = "Variable",
    values_to = "Value"
  )

ggplot(data_long, aes(
  x = Value, y = Order,
  size = Value, color = Variable
)) +
  geom_point(alpha = 0.7) +
  #scale_size_continuous(range = c(5, 20)) +
  labs(
    title = "Combined Bubble Chart Example",
    x = "Taxonomic Groups", y = "Values",
    size = "Value", color = "Variable"
  ) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  facet_wrap(~dataset)