基于两列重塑R语言data.frame数据的技术需求

原始数据 #

待处理的原始数据如下：

df <- structure(list(
  id = c(13186082L, 13186082L, 15186082L, 15186082L, 32186082L, 32186082L, 24186085L, 24186085L),
  freq1 = c("socialinteraction2", "who", "socialinteraction2", "who", "socialinteraction2", "who", "socialinteraction2", "who"),
  Response = c("2", "a", "1", "b", "3", "a", "4", "c")
), class = "data.frame", row.names = c(NA, -8L))

数据展示：

id              freq1 Response
1 13186082 socialinteraction2        2
2 13186082                who        a
3 15186082 socialinteraction2        1
4 15186082                who        b
5 32186082 socialinteraction2        3
6 32186082                who        a
7 24186085 socialinteraction2        4
8 24186085                who        c

需求说明 #

需要将数据重塑为指定格式：

• From列固定取值为socialinteraction2
• To列取每个id对应的freq1="who"的Response值，并按照同id下freq1="socialinteraction2"的Response数值重复该值

目标结果：

From To
1  socialinteraction2  a
2  socialinteraction2  a
3  socialinteraction2  b
4  socialinteraction2  a
5  socialinteraction2  a
6  socialinteraction2  a
7  socialinteraction2  c
8  socialinteraction2  c
9  socialinteraction2  c
10 socialinteraction2  c

解决方案 #

方法一：使用tidyverse工具包 #

先将数据转换为宽格式，再利用uncount函数按指定次数重复行：

library(tidyverse)

result_tidy <- df %>%
  pivot_wider(
    id_cols = id,
    names_from = freq1,
    values_from = Response
  ) %>%
  mutate(socialinteraction2 = as.integer(socialinteraction2)) %>%
  uncount(socialinteraction2) %>%
  transmute(
    From = "socialinteraction2",
    To = who
  )

print(result_tidy)

方法二：使用Base R #

先通过reshape转换宽格式，再用rep生成重复序列：

# 转换为宽格式
df_wide <- reshape(df, idvar = "id", timevar = "freq1", direction = "wide")
# 转换次数列为整数
df_wide$Response.socialinteraction2 <- as.integer(df_wide$Response.socialinteraction2)

# 生成结果
result_base <- data.frame(
  From = rep("socialinteraction2", sum(df_wide$Response.socialinteraction2)),
  To = rep(df_wide$Response.who, df_wide$Response.socialinteraction2)
)

print(result_base)

两种方法均可得到符合要求的目标数据。

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