Plotting ternary graph with imported excel sheet

I have a number of excel sheets (example attached) which plot simple ternary plots.[![basic excel sheet layout][![1]][1]][1]

This currently is used to plot a simple ternary plot of shape [![generated simple ternary plot in excel][![2]][2]][2]

left scale is c:a ratio, right is b:a ratio at base is (a-b)/(a-c) ratio

What I would LIKE to do is to be able to plot the shape of particles with their average size and lithology colour coded using ggtern.

I am NOT having a lot of fun with this to say the least

> library("readxl")
library("ggtern")

#data <- read_excel("my_file.xlsx")
Tdata <- read_excel("C:\\Users\\sedeal\\Desktop\\R_studio\\1770.xlsx", range = "Data entry!a1:F54", na = "")


Tdata = Tdata[-1,]
Tdata = Tdata[-1,]
Tdata = Tdata[-1,]
Tdata = Tdata[,-1]

colnames(Tdata)
names(Tdata)[3] <- "a.axis"
names(Tdata)[4] <- "b.axis"
names(Tdata)[5] <- "c.axis"
names(Tdata)[5] <- "lithology"
names(Tdata)[5] <- "roundness"

View(Tdata)

"seems" to get the data into R studio.. [![R data table][5]][5][![enter image description here][6]][6]

PROBLEM 1: sapply(Tdata, typeof)

a.axis b.axis c.axis lithology roundness "character" "character" "character" "character" "character"

I can't work out how to change a.axis b-axis c.axis to numeric values

PROBLEM 2: Do the "NA" fields mess things up? They seem to in scatterplots

Warning message: Removed 34 rows containing missing values (geom_point).

PROBLEM 3: I need to work out the ratios c:a; b:a; (a-b)/(a-c) and then plot these in such a way that I have a chance to mess with the formatting.

PROBLEM 4: The biggest problem - I'm really pants at R-studio (so please accept apologies for what are probably really simple issues)

TABULATED DATA in the hope that it is helpful??

EDIT:

> dput(Tdata)```
structure(list(a.axis = c("11.11", "8.57", "9.25", "8.2100000000000009", 
"8.34", "7.36", "7.29", "7.25", "7.95", "6.03", "6.65", "6.12", 
"7.43", "4.92", "6.39", "5.12", NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), b.axis = c("10.98", 
"8.27", "6.25", "7.23", "6.18", "5.69", "7.19", "5.29", "5.16", 
"4.76", "5.24", "4.58", "4.1100000000000003", "3.41", "4.6399999999999997", 
"4.99", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA), c.axis = c("5.92", "6.2", "4.1399999999999997", 
"5.03", "3.28", "4.43", "2.93", "4.8899999999999997", "4.46", 
"4.57", "4.54", "4.29", "3.29", "3.17", "2.52", "3.31", NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
), lithology = c("a-ig", "met", "ss", "b-ig", "a-ig", "ss", "ss", 
"a-ig", "met", "a-ig", "qtz", "b-ig", "b-ig", "ss", "a-ig", "b-ig", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA), roundness = c("sa", "sa", "Roundness", "sa", "Roundness", 
"sr", "a", "sr", "sa", "sa", "va", "Roundness", "a", "sa", "a", 
"a", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA)), row.names = c(NA, -50L), class = c("tbl_df", 
"tbl", "data.frame"))


@SamR was really helpful in solving what I THOUGHT was the problem - but using a ternary plot assumption where the values of a, b, c plot to 1 (100%) was clearly not happening when I looked deeper into the issue


**EDIT**
OK :) so I have now unpicked WHY the plot above does not look like the one generated by the excel sheet shared by here - 
Graphical representation of particle shape using triangular diagrams: An Excel spreadsheet method
December 2000
25(13):1473-1477
DOI: 10.1002/1096-9837(200012)25:13<1473::AID-ESP158>3.0.CO;2-C
David John Graham & Nicholas G. Midgley

The TLDR is that the plot LOOKS like a ternary plot, but it isnt.

They use a formula to convert the data to X-Y co-ordinates. 
1) calcuating c/a and b/a ratios
x co-ordinate is given by 1-[b/a ratio]+(0.5*[c/a ratio])
y co-ordinate is  given by [c/a ratio]*0.866)* [printer correction, set at 1.1]

in the Graham and Midgely sheet, null values are given a -1 value to plot away from the diagram and the triangle shape is superimposed over the top of the x/y graph.

**PROBLEM 5:**
Need to revisit the code - remove any null values and  then work out how to put a triangle over the top :)



NEW EDIT:
after much faffing I worked out that the SNEED AND FOLK ternary plot does not actually use the formulas presented.  This on seems to work better and I wanted to update the record so no-one else goes through the same learning curve!!

#R studio
# Load required packages
library(readxl)
library(dplyr)
library(ggplot2)

# Prompt user to select file
file <- file.choose()

# Read in data from selected file and sheet
data <- read_excel(file, sheet = "Data entry", range = "B4:F54")

# Rename columns for consistency
colnames(data) <- c("a.axis", "b.axis", "c.axis", "lithology", "roundness")

# Make numeric and calculate ratios
data <- data %>%
  mutate(
    across(a.axis:c.axis, as.numeric),
    c_to_a = c.axis / a.axis,
    b_to_a = b.axis / a.axis,
 )

# equation for newY value "*1" is a printer correction
# Re calculate X Y
data <- data %>%
  mutate(
    across(c_to_a:b_to_a, as.numeric),
    newX = (1 - b_to_a)+(0.5 * c_to_a),
    newY = (c_to_a * 0.866) * 1  )
      
#DRAW TRIANGLE AND TICKS AND LABELS
# Basic scatter plot
p <- ggplot(data = data, aes(x = newX, newY)) +
  geom_point(aes(colour = lithology, size = a.axis)) + xlim(0, 1) + ylim(0,1) + theme_void() +
theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
panel.background = element_blank(), axis.line = element_blank())+ geom_label(label="(a-b)/(a-c)", x = 0.5, y = 0.04, colour = "black")  + geom_label(label="c:a", x = 0.15, y = 0.5, colour = "blue") + geom_label(label="b:a", x = 0.85, y = 0.5, colour = "red") + theme(axis.title = element_blank()) + theme(axis.title.y=element_text(colour="white"))  + scale_size_continuous(limits = c(1, 40), breaks = c(1, 2, 4, 8, 16, 32)) +
  scale_color_manual(values = c(
    "a" = "#F4C0F0",
    "b" = "#4D4C4C",
    "o" = "#BB5D49",
    "q" = "#CAC8CA",
    "s" = "#E79E44",
    "c" = "#FD2D45"
  ))
#breaks to force plotting at certain point sizes related to sieve sizes
#colours need to be matched to log contents

# Plot
p + annotate("segment", x = 0, xend = 0.5, y = 0, yend = 1,
  colour = "blue") + annotate("segment", x = 1, xend = 0.5, y = 0, yend = 1,
  colour = "red") + annotate("segment", x = 0, xend = 1, y = 0, yend = 0,
  colour = "black") + ggtitle(basename(file))

p1 <- p + annotate("segment", x = 0, xend = 0.5, y = 0, yend = 1,
  colour = "blue") + annotate("segment", x = 1, xend = 0.5, y = 0, yend = 1,
  colour = "red") + annotate("segment", x = 0, xend = 1, y = 0, yend = 0,
  colour = "black") + ggtitle(basename(file))

#I need to find a way to edit the title to remove ".xls"


# Save plot as .jpg file
#change path if needed
ggsave(file.path("C:\\Users\\sedeal\\Downloads", paste0(basename(file), ".jpg")), plot = p1)
#title and save file contain "[original excel file name].xls" and "[original excel file name].xls.jpg"

[![THE FINAL VERSION][7]][7]


  [1]: https://i.sstatic.net/mkFjy.jpg
  [2]: https://i.sstatic.net/rBobu.jpg
  [3]: https://i.sstatic.net/lvAmQ.jpg
  [4]: https://i.sstatic.net/hYFdJ.jpg
  [5]: https://i.sstatic.net/eornO.jpg
  [6]: https://i.sstatic.net/Rh6du.jpg
  [7]: https://i.sstatic.net/iVmFI62j.jpg

Using the data you posted in the table.

# Read in the data
Tdata <- read.table(text = "a-axis  b-axis  c-axis  lithology   roundness
11.11   10.98   5.92    a-ig    sa
8.57    8.27    6.2 met sa
9.25    6.25    4.14    ss  r
8.21    7.23    5.03    b-ig    sa
8.34    6.18    3.28    a-ig    r
7.36    5.69    4.43    ss  sr
7.29    7.19    2.93    ss  a
7.25    5.29    4.89    a-ig    sr
7.95    5.16    4.46    met sa
6.03    4.76    4.57    a-ig    sa
6.65    5.24    4.54    qtz va
6.12    4.58    4.29    b-ig    r
7.43    4.11    3.29    b-ig    a
4.92    3.41    3.17    ss  sa
6.39    4.64    2.52    a-ig    a
5.12    4.99    3.31    b-ig    a", header = T)

# Make numeric and calculate ratios

library(dplyr)
library(ggtern)


Tdata <- Tdata |>
    mutate(
        across(a.axis:c.axis, as.numeric),
        c_to_a = c.axis / a.axis,
        b_to_a = b.axis / a.axis,
        ab_ac = (a.axis - b.axis) / (a.axis - c.axis)
    )

Then it's just a question of drawing the plot:

ggtern(data = Tdata, aes(x = c_to_a, y = ab_ac, z = b_to_a)) +
    geom_point() +
    xlab("c:a") + # replace default axis labels
    ylab("b:a") +
    zlab("(a-b)/(a-c)") +
    theme_bw() +
    theme_showarrows() +
    theme_anticlockwise()

EDIT: Added arrows to plot axes and switch the ratios (initially had a:c rather than c:a). I realise the plot does not look like the one in the question - I am not exactly sure why as your original one does not have axis labels so it is hard to see what the differences are.