R: How to fix column names containing spaces

A basic rule of R is to avoid naming data-frame columns using names that contain spaces. R will accept a name containing spaces, but the spaces then make it impossible to reference the object in a function. Neither single or double quotes can work around this problem, and other data structures also share this limitation. (Note that commas also cause similar problems, as do many special characters.)

> select(x, Date, Depth, Total Phosphorus)
Error: unexpected symbol in "select(x, Date, Depth, Total Phosphorus"
> select(x, Date, Depth, "Total Phosphorus")
Error: All select() inputs must resolve to integer column positions.
The following do not:
*  "Total Phosphorus"

When I’m creating my own column names it’s easy to avoid the issue, but often I’m working with data that comes from other sources — and contains spaces in problematic places.

At the moment I’m working with lab data where every water-quality parameter (nitrogen, phosphorus, copper, temperature, etc.) is its own line in the dataset, with one column for the parameter name and another column for the value — often called “long” or “molten” format. The names-with-spaces problem comes when I spread out the data into “wide” format, where each row is a complete sampling date and each parameter becomes its own column.

Many of the parameters have spaces (and sometimes commas) in the name the lab uses, such as “Ammonia Nitrogen” or “Copper, Dissolved”. When I use the spread() function (from the “tidyr” package), these become column names containing spaces and commas. After trying a few approaches, I found a simple, elegant way to do it in a single line using the “stringr” package.

To fix a dataframe (or any other named structure) “x” that already has names containing spaces and/or commas:

names(x)<-str_replace_all(names(x), c(" " = "." , "," = "" ))

This replaces spaces with periods, and commas with nothing (to avoid creating double periods like “Copper..Dissolved”). Spaces and commas are the only two problematic characters I have in my dataset; if you have others, add them to the str_replace_all() call. The result from a names() call is a vector of strings, which the stringr functions can manipulate easily before passing it back through the names() function.

Another approach is to remove the spaces from the long/molten dataset before spreading it out. It’s very similar code, only on a column of parameter names:

y$Parameter<-str_replace_all(y$Parameter, c(" " = "." , "," = "" ))


UPDATE: I’ve discovered that tibbles — the tidyverse version of the dataframe — can handle names with spaces and other special characters. To refer to those non-standard names in functions, surround the name with backticks (on my keyboard, the backtick is on the  ~  key):

`​Copper, Dissolved`


Even though I’m working primarily with tibbles now, I’m going to keep renaming my columns so they’re easier to work with as I go through the rest of the data analysis. I prefer not to have to type the backticks all the time.



Filed under R

4 responses to “R: How to fix column names containing spaces

  1. Thank you – you just saved my weekend with str_replace_all! 🙂

  2. George Savva

    You could use the make.names() function (base R). So:

    names(x) <- make.names(names(x))

    Does what you want

    • Not quite — if there’s a name with two special characters like `Copper, Dissolved` make.names() turns it into `Copper..Dissolved`. It’s a legal name, sure, but I don’t find the double periods to be human-friendly or easy to remember. I’m going to stick with my version that eliminates the double periods.

      But I like that make.names() will take care of any and all special characters without having to call them out individually! I’ll use it if I have a messier set of names in the future, and follow it with an instance or two of str_replace_all(“..”=”.”) to then eliminate the double periods.

  3. I F.

    Nice! I’ve done the same with gsub from baseR:
    names(dt_frame) <- gsub(" ", "_", names(dt_frame))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s