R语言导入跨3行表头的Excel数据：自动填充缺失表头需求

Hey there! I totally get the pain of manually renaming columns after skipping header rows—let's fix that with code that automatically handles those 3-line headers, fills in the implicit parent labels, and merges everything into clean, descriptive column names like USA_Dollars_CY2016.

Step 1: Load Required Packages #

We'll use readxl to read the Excel file and tidyverse for easy data manipulation (especially filling in those implicit headers):

library(readxl)
library(tidyverse)

Step 2: Extract and Clean the Header Rows #

First, we'll read just the first 3 rows (your header rows) to process them:

# Replace "your_data.xlsx" with your actual file path
header_raw <- read_excel("your_data.xlsx", n_max = 3, col_names = FALSE)

Next, we need to fill in the implicit headers (like how "USA" applies to the first 4 columns, even though the first row only has "USA" once). We'll transpose the header data so we can fill down the parent labels, then merge all three lines into one column name:

# Transpose headers to work with row-wise filling, then fill empty parent labels
header_processed <- header_raw %>% 
  t() %>% 
  as_tibble() %>% 
  fill(V1, .direction = "down")  # Fills NA values in the first header row with the last non-NA value

# Merge the three header lines into a single string with underscores
clean_col_names <- header_processed %>% 
  unite("combined_header", V1:V3, sep = "_") %>% 
  pull(combined_header)

Step 3: Import the Actual Data with Clean Column Names #

Now we'll read the main data (starting from row 4) and use our cleaned column names:

# Skip the first 3 header rows, use our generated column names
final_df <- read_excel("your_data.xlsx", skip = 3, col_names = clean_col_names)

What the Final Output Looks Like #

For your sample data, the column names will be:
USA_Dollars_CY2016, USA_Dollars_CY2017, USA_Volume_CY2016, USA_Volume_CY2017, China_Dollars_CY2016, China_Dollars_CY_2017, China_Volume_CY2016, China_Volume_CY2017

And the data will load in correctly with those labels—no manual renaming needed!

Notes #

• If your Excel file has empty cells that aren't recognized as NA, you might need to adjust the fill step, but readxl usually handles empty cells as NA by default.
• Make sure to replace "your_data.xlsx" with your actual file path (or use a full path like "C:/Documents/your_data.xlsx" on Windows).

内容的提问来源于stack exchange，提问作者Derek Lilley