How do I order the x-axis categories of a graph according to y-axis values while maintaining the x-axis category names?

Question

Thanks in advance to anyone who can help me with this. I am making a geom_point graph of chlorophyll concentrations in several lakes (these lakes occur in two different watersheds, which is why I have split them into two facets). I would like these points to be ordered by chlorophyll concentration, from lowest to highest concentration (note: I have flipped the y axis). I have done this, but the problem is, when I order the lakes by chlorophyll concentration, I have to graph "site_order" on the x-axis rather than "site_name." You can see from the hashed-out lines of code that I have tried two different methods for graphing the site_names on the x-axis:

1) I have tried assigning site_name levels to site_order, but this does not work. It produces a graph with lake names for the x-axis categories, but the lakes are not associated with the correct data! I'm not sure how levels() assigns site_name to site_order, but it doesn't do it correctly... (see fig. 1).

2)I have tried labeling the x axis with site_name using scale_x_discrete, but this results in a graph without any labels for x-axis categories (see fig. 2).

My code and a sample dataset are below. If anyone can troubleshoot either of my approaches, or suggest a different approach, I would GREATLY appreciate it!

Here's my sample dataset:

                      Watershed site_order         site_name Morph Chla_percent
1  Six Mile Creek & Schutz Lake         19        Wassermann  Deep   248.571429
2  Six Mile Creek & Schutz Lake         12            Church  Deep    21.428571
3  Six Mile Creek & Schutz Lake          1     Kelser's Pond  Deep  -100.000000
4  Six Mile Creek & Schutz Lake          7           Steiger  Deep    -6.428571
5  Six Mile Creek & Schutz Lake          5            Zumbra  Deep   -40.000000
6  Six Mile Creek & Schutz Lake          4             Stone  Deep   -57.142857
7  Six Mile Creek & Schutz Lake         16       East Auburn  Deep   107.857143
8  Six Mile Creek & Schutz Lake          9       West Auburn  Deep     0.000000
9  Six Mile Creek & Schutz Lake         18            Turbid  Deep   185.714286
10 Six Mile Creek & Schutz Lake         15            Schutz  Deep    84.285714
11              Minnehaha Creek         11           Brownie  Deep    20.285714
12              Minnehaha Creek          2           Calhoun  Deep   -70.714286
13              Minnehaha Creek          6             Cedar  Deep   -33.571429
14              Minnehaha Creek          3           Harriet  Deep   -67.142857
15              Minnehaha Creek          8          Hiawatha  Deep    -1.428571
16              Minnehaha Creek         13 Lake of the Isles  Deep    74.285714
17              Minnehaha Creek         10           Nokomis  Deep    13.571429
18              Minnehaha Creek         17       Powderhorn   Deep   160.714286
19              Minnehaha Creek         14              Taft  Deep    83.571429

Here's my code:

#Import, fix up, and subset data
Data = read.csv("Lakes_data_for_R.csv", 
            stringsAsFactors=FALSE)
colnames(Data)[1] <- "Watershed"
Data <- Data[grep("Deep", Data$Morph),]

#Create site_order column (and if possible, assign levels of site_name to site_order)
Data$site_order <- rank(Data$Chla_percent)
#Data$site_order <- as.factor(Data$site_order)
#levels(Data$site_order) <- Data$site_name

#Load ggplot
library(ggplot2)

#Make plot
ggplot() +
  geom_point(data = Data, aes(x = site_order, y = Chla_percent), size = 5, color = "blue") +
  ylab ("Chlorophyll a (% difference from Poor threshold)") + xlab("") +
  facet_grid(~Watershed, scale = "free", space = "free") +
  scale_y_reverse() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1, vjust = 1, color = "black"),
    axis.text.y = element_text(color = "black"),
    panel.grid.major.x = element_blank(),
    panel.grid.major.y = element_blank(),
    panel.grid.minor = element_blank(), 
    axis.line.x = element_line(color = "black"),
    axis.line.y = element_line(color = "black"),
    panel.background = element_rect(fill = "white"),
    legend.key=element_blank(),
    legend.title = element_blank(),
    text = element_text(size=18),
    panel.spacing = unit(2, "lines")) 
#+scale_x_discrete(labels = Data$site_name)

Here's Figure 1:

Here's Figure 2:

Answer 1

The easiest way to reorder these categories is by reordering the factor site_name. Here is a minimum working example followed by code that should work for your example:

library(ggplot2)
library(dplyr)

myData <- iris %>% group_by(Species) %>% summarise(MeanSL =mean(Sepal.Length))
typeof(myData$Species)
ggplot(data=myData,aes(x=Species, y=MeanSL)) + geom_point()
myData$Species<- factor(myData$Species, levels=myData$Species[order(- myData$MeanSL)], ordered=TRUE)
ggplot(data=myData,aes(x=Species, y=MeanSL)) + geom_point()

In your case this should do the trick:

Data$site_name <-factor(Data$site_name, levels=Data$site_name[order(- Data$Chla_percent)], ordered=TRUE) 

ggplot() +
  geom_point(data = Data, aes(x = site_name, y = Chla_percent), size = 5, color = "blue") +
  ylab ("Chlorophyll a (% difference from Poor threshold)") + xlab("") +
  facet_grid(~Watershed, scale = "free", space = "free") +
  scale_y_reverse() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1, vjust = 1, color = "black"),
        axis.text.y = element_text(color = "black"),
        panel.grid.major.x = element_blank(),
        panel.grid.major.y = element_blank(),
        panel.grid.minor = element_blank(), 
        axis.line.x = element_line(color = "black"),
        axis.line.y = element_line(color = "black"),
        panel.background = element_rect(fill = "white"),
        legend.key=element_blank(),
        legend.title = element_blank(),
        text = element_text(size=18),
        panel.spacing = unit(2, "lines"))