Use %in$% operator when column name contains an apostrope

Hi - I feel sure that I have seen this done, but the syntax escapes me.

I have data on a number of sites, one of which is called Booth's. I want to select the data based on the name of the site (or location) using SELECT LOCATION %in% c("site1", "site2", "Booth's")

I can't seem to "escape" the apostrophe. Please remind me of the syntax - I have not managed to find it in the help system.

Many thanks

Richard Bacon

Can you use the backtick?


Can you provide a reproducible example then?

FAQ: What's a reproducible example (`reprex`) and how do I do one?

William, thank you so much for your help! May I ask you for a little delay to answering your query as I have assigned the site names to a variable and the SELECT %IN% query acts as expected - that is "Booth's" is selected.

That allows me to finish the little piece of work, which is my main objective.

However, it does not identify the reason for the original failure (that Booth's was not identified in SELECT %IN% c("Booth's", "Site1", "Site2"....etc).

If I may, I will get back to addressing the causative issues when I have finished the work that I must complete.

Again, many thanks


Are you confusing SQL with R/Tidyverse code ?
dplyr::select() is lowercase, and the base %in% operator is spelt with lowercase

Very astute comment! I suspect you heard me swearing when pwd "suddenly" stopped working yesterday and it took me 30 minutes to remember getwd()!

However, I'm afraid that's not the answer to the issue.
this code:

SiteNames <- c("Booth's", "Otaki Estuary", "Kuku Estuary", "Te Hakari", "Otaki Ponds")
followed by:

BBGullHold9 <- filter(AnalysisData, Species %in% SpeciesList,Location %in% SiteNames)

works, but this code:

BBGullHold9 <- filter(AnalysisData, Species %in% SpeciesList,Location %in% c("Booth's", "Otaki Estuary", "Kuku Estuary", "Te Hakari", "Otaki Ponds"))

fails to find Booth's, but finds the others.

I would say that it is probably more elegant to create the variable SiteNames and so I have a solution (and I can also exclude the 1 site that I do not want, so I have 2 solutions), but I don't have a definitive answer as to why the first approach failed.

Many thanks for your assistance.


Can you make your issue reproducible ?

I'll RTFM and get back to you.

OK, followed the documentation and decided to attempt to recreate the issue from scratch. It all worked as expected!

So then I opened one of the old files which exhibited the odd behaviour and the anomaly was still there. Decided to edit the file - removed the original "" characters and replaced them.......and the problem disappeared.

From this I infer that there is an issue on my machine - I have had keyboard issues since I installed ubuntu in a vm (VirtualBox) so I wonder if this is yet another manifestation of those issues.

So folks, please consider this closed and accept many thanks for your efforts.
If, of course, others find the same problem, then I'm happy to get involved with further testing, reprex etc.



1 Like

I'm glad you had a favourable resolution.
In the past, I have had issues where I copied and pasted information from the web, and though on the web it seemed to use (to the eye), standard quote marks; it was revealed when run as code that the quote marks were idiosyncratic, so I feel your pain.

Thanks.....this sort of issue has been a problem for decades. Making things "easy" for users often has unexpected consequences in unexpected places!

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

Thanks William - excellent suggestion but didn't work so I am suspicious that the character isn't what it seems to be so I'll examine that line.