janitor row_to_names() in R: Promote a Row to Column Names
janitor::row_to_names() promotes any row of a data frame to become its column names, then drops that row (and optionally the rows above it). It is the standard fix for messy Excel imports where the real header sits below a title row.
row_to_names(df, 1) # promote row 1, drop it row_to_names(df, 2) # row 2 has headers, drop rows 1-2 row_to_names(df, 1, remove_row = FALSE) # use row 1 as names, keep it row_to_names(df, 3, remove_rows_above = FALSE) # keep rows 1-2 as data df |> row_to_names(1) |> clean_names() # promote, then snake_case read_excel("f.xlsx") |> row_to_names(2) # canonical Excel fix df |> row_to_names(1) |> mutate(across(everything(), as.numeric)) # retype
Need explanation? Read on for examples and pitfalls.
What row_to_names() does
row_to_names() turns a data row into the column header. You pass a data frame and the position of the row that holds the real column names. The function lifts that row up, assigns its values as names(dat), then deletes the row (and by default every row above it). It returns a data frame, never a tibble, so wrap it in as_tibble() if you need one. The result is a frame whose first row is real data and whose names match what a human would write if the file had been clean from the start.
This is the canonical janitor fix for spreadsheet exports. Excel files often carry a title row, a blank row, and only then the actual header. read_excel() reads the title row as the header and pushes the real names into row 2, leaving you with columns named ...1, ...2, Quarterly Report. One call to row_to_names(df, 2) undoes the damage and collapses three manual cleanup steps into a single pipe-friendly call.
Syntax and arguments
The signature has four arguments, two of which you usually keep at default. In day-to-day use you pass the data frame and the row position only. The two boolean flags are reserved for edge cases where you need to preserve rows around the header.
| Argument | Default | What it does |
|---|---|---|
dat |
required | The data frame to modify |
row_number |
required | Position (integer) of the row to promote |
remove_row |
TRUE |
Drop the promoted row from the data |
remove_rows_above |
TRUE |
Drop every row above the promoted row |
row_number is 1-indexed and matches the row position in the data, not any preserved row label. Pick row_number by looking at the printed frame, not at the rownames() attribute, because filter and arrange operations leave the label out of sync with the visible order. Set remove_rows_above = FALSE when rows above the header are real data you need to keep, and remove_row = FALSE when the header row should stay as data.
Examples by use case
Four patterns cover almost every real-world call to row_to_names(). Each pattern below uses a small built-in or hand-built example so you can paste the code into a fresh R session and run it as is.
Use 1: classic Excel import with a title row. A common pattern is a one-cell title in row 1, the header in row 2, and data from row 3 onward.
Row 2 became the header. Row 1 (the title) was dropped by remove_rows_above = TRUE. Row 2 itself was dropped by remove_row = TRUE.
Use 2: keep the rows above as data. Set remove_rows_above = FALSE when the rows above the header are also valid data, such as a subtotal row.
The Subtotal row stays at the top with the new column names applied.
Use 3: chain with clean_names() and retyping. The promoted header often contains spaces, hashes, or units. Pipe through clean_names() to snake_case the names, then retype the columns.
row_to_names() promotes, clean_names() standardises, type.convert() restores numeric types in one pass. This three-step recipe handles the bulk of dirty CSV and Excel imports without writing a single bracket subset. Reach for it whenever a fresh file lands and the column names look wrong on first inspection.
Use 4: deeper in a tidy import pipeline. Many production import scripts read a folder of files, clean each one, and bind the results into a single frame. Drop row_to_names() into the cleaning function and the same pipeline handles every spreadsheet the same way.
Wrapping the cleanup in a named function makes each step explicit and lets you swap out individual stages without rewriting the orchestration code. Pass header_row = 2 for files where a title precedes the header. The same function handles every variant.
Compare with alternatives
Three other patterns rename columns, but only row_to_names() drops the row it used. Picking the right one comes down to two questions: do you control the file read, and do you need to keep the source row in the data after renaming.
| Approach | What it does | When to pick it |
|---|---|---|
row_to_names(df, n) |
Promotes row n, deletes row + rows above | Header lives inside the data |
names(df) <- df[1, ]; df <- df[-1, ] |
Manual base R equivalent | One-off, no janitor dependency |
setNames(df, df[1, ]) |
Renames but does NOT drop the row | You want both name AND keep that row |
read_excel(skip = n) |
Re-read with a skip offset | You control the import call |
For interactive cleanup, prefer row_to_names(). For a fresh pipeline, set skip = at read time so the header is correct from the start. Choose the manual base R approach only when you cannot add a janitor dependency. Choose setNames() when you need the source row to remain in the data after renaming.
read_excel("f.xlsx", skip = 1) reads the second row as the header directly. row_to_names() is the rescue when you cannot change how the file was loaded.Common pitfalls
Three traps recur with row_to_names(), all fixable. Each is a downstream consequence of forcing a row of strings into the header slot. The pattern is the same in every case: run row_to_names(), then run one follow-up call to repair what the promotion changed.
A literal NA ends up as a column name. Fill blanks before promotion: bad[1, is.na(bad[1, ])] <- "col_NA".
Two columns named id will break dplyr::select(id). Chain clean_names() after; it appends a numeric suffix to duplicates, giving id and id_2.
Numeric columns are now strings. Always follow with type.convert(as.is = TRUE) or explicit mutate(across(..., as.numeric)).
as_tibble() if downstream code expects tibble printing or class checks.Try it yourself
Try it: A messy import has a title in row 1, headers in row 2, data from row 3. Use row_to_names() to clean it up, then snake_case the column names.
Click to reveal solution
Explanation: row_to_names(2) promotes row 2 to the header and drops rows 1-2. clean_names() then converts the spaces, the dash, and the percent sign into snake_case.
Related janitor functions
janitor pairs row_to_names() with five other cleanup helpers. Use them together when fixing real-world imports.
clean_names()standardises names after promotion, converting spaces, hyphens, and special characters to underscoresmake_clean_names()cleans a character vector before assigning tonames()remove_empty()drops empty rows and columns left over from the importremove_constant()drops columns that hold a single value across all rowsget_dupes()finds duplicate rows once the headers are sorted out
FAQ
Why is my numeric column suddenly character after row_to_names()?
The promoted row sat inside the column, so the column type was forced to character to hold the header text. After promotion, follow with type.convert(as.is = TRUE) to let R re-infer numeric and integer types, or use dplyr::mutate(across(where(is.character), as.numeric)) for an explicit cast. The function itself does no retyping.
How do I handle two header rows that should be combined?
Concatenate the two rows first, then promote the combined row. One pattern: df[1, ] <- paste(df[1, ], df[2, ], sep = "_") then row_to_names(df, 1). This collapses a two-row header like Q1 / Sales, Q1 / Units into Q1_Sales, Q1_Units in a single step.
Does row_to_names() work on tibbles?
Yes, you can pass a tibble in. The catch is that it returns a base data.frame, not a tibble, even when given one. If your pipeline depends on tibble methods, wrap the call with tibble::as_tibble() immediately after.
Can I use row_to_names() to set columns from a separate vector?
No. The function reads the names from a row that is already inside the data frame. To rename from an external vector, use names(df) <- new_vector or dplyr::rename_with(df, ~ new_vector). Use row_to_names() only when the header is embedded in the data.
What if the row I want has blank cells?
Blank cells become literal NA column names, which break subsequent selection. Either fill them first with df[row_number, is.na(df[row_number, ])] <- "blank" or rely on the clean_names() follow-up, which converts NA to na and appends position suffixes when several blanks collide.