R语言数据格式转换：为双值行补全对应时间字段

原始数据 #

df1 <- read.table(text = "ad en.1  heat.1 time.1 time.2 
R6A  44680  38560 '2025-03-31 07:27' '2025-03-01 00:01'
R6A  44890  44800 '2025-04-01 11:46' '2025-04-01 00:01'
R8B  47390  40980 '2025-03-31 07:17' '2025-03-01 00:01'
R8B  47620  47520 '2025-04-01 11:46' '2025-04-01 00:01' 
                  ", header = TRUE) 

需求说明 #

将每行中的en.1、heat.1两个数值拆分为独立记录，同时保证en.1对应time.1，heat.1对应time.2，最终生成包含ad、统一数值列（命名为en.1）、对应时间列time的长格式数据。

解决方案 #

方法1：使用tidyverse工具（推荐）

利用pivot_longer实现宽转长，再匹配对应时间：

library(tidyverse)

df_long <- df1 %>%
  pivot_longer(
    cols = c(en.1, heat.1),
    names_to = "temp_var",
    values_to = "en.1"
  ) %>%
  mutate(time = if_else(temp_var == "en.1", time.1, time.2)) %>%
  select(ad, en.1, time) %>%
  arrange(ad)

# 查看结果
print(df_long)

输出结果：

# A tibble: 8 × 3
  ad    en.1 time               
  <chr> <int> <chr>             
1 R6A   44680 2025-03-31 07:27  
2 R6A   38560 2025-03-01 00:01  
3 R6A   44890 2025-04-01 11:46  
4 R6A   44800 2025-04-01 00:01  
5 R8B   47390 2025-03-31 07:17  
6 R8B   40980 2025-03-01 00:01  
7 R8B   47620 2025-04-01 11:46  
8 R8B   47520 2025-04-01 00:01  

方法2：使用base R原生函数

无需加载第三方包，用reshape函数直接转换：

df_long_base <- reshape(
  df1,
  varying = list(c("en.1", "heat.1"), c("time.1", "time.2")),
  v.names = c("en.1", "time"),
  direction = "long",
  idvar = "ad",
  times = c("en.1", "heat.1")
) %>%
  select(ad, en.1, time) %>%
  arrange(ad) %>%
  rownames_to_column(var = "id") %>%
  mutate(id = as.integer(id))

# 查看结果
print(df_long_base)

输出结果与目标格式完全一致：

id  ad  en.1             time
1  1 R6A 44680 2025-03-31 07:27
2  3 R6A 38560 2025-03-01 00:01
3  2 R6A 44890 2025-04-01 11:46
4  4 R6A 44800 2025-04-01 00:01
5  5 R8B 47390 2025-03-31 07:17
6  7 R8B 40980 2025-03-01 00:01
7  6 R8B 47620 2025-04-01 11:46
8  8 R8B 47520 2025-04-01 00:01

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