sql - 如何根据 r 中的另一个键/ID 列将列切换为行？

Question

我有以下数据集:

> head(Dataset)

    IDCLIENT NUMDOSS PROV_200112 PROV_200212 PROV_200312  ....

      H44879 0104957           0           0           0  ....
     Z353025 0105289           0           0           0  ....
     B416771 0105309           0           0           0  ....  
       B5043 0105366           0           0           0  ....
     A725471 0105370           0           0           0  ....
    BE406627 0105371           0           0           0  ....

这是一个包含 125 列和 250 000 obs 的表

我们有一个表，有两个ID列ID1，ID2，其他列记录了一个名为Prov的变量在2003年到2017年的某个时间段内的值.

我想做的是转换此表，以便我只有 4 列(ID1、ID2、日期(或句点)和 Prov)，而不是 125 列。像这样的东西:

>head(DF)
    IDClient      IDDossier     Date             Prov     
    B416771        0104957     PROV_200110         5    
    B416771        0104957     PROV_200111         0          
    B416771        0104957     PROV_200112        99  
    B416771        0104957     PROV_200212        1,23          
    H44879         0105289     PROV_200212       36,1
    Z353025        0105309     PROV_200312      10436,175
    BE4410         0105366     PROV_200406      10438,9    

为此，我尝试了以下代码，但这真的很耗时，而且我还收到了一些来自 R 的错误/警告消息:

# N= dim(ProvTab)[1]*dim(ProvTab)[2]
# DF=data.frame(IDClient=rep("",N),IDDossier=rep("",N),
          # Date=rep("",N), Prov=rep("",N), stringsAsFactors = F)
DF=NULL
periodd=as.data.frame(colnames(ProvTab))
start.time= Sys.time() # to count running time !
for (k in 3:ncol(ProvTab)) 
     {
  for (j in 1:nrow(ProvTab))
       {
  DF=rbind(DF,
           data.frame(IDClient=ProvTab[j,1], IDDossier=ProvTab[j,2],
                         Date = periodd[k,1] , Prov=ProvTab[j,k]))
       }
     }

end.time= Sys.time()
end.time- start.time

我真的尝试寻找其他解决方案但失败了。

PS:欢迎使用 R 或 SQL 代码!

有什么建议吗？

Answer 1

我认为这是一个非常简单的从宽到长的转换。 reshape2 包非常适合这个。

require(reshape2)

DF <- melt(Dataset, id.vars= c("IDCLIENT", "NUMDOSS"))

names(DF) <- c("IDClient", "IDDossier", "Date", "Prov")

如果速度很慢，您可以尝试使用 data.table，这可能更快。

require(data.table) 
setDT(Dataset)

DF <- melt(Dataset, id.vars= c("IDCLIENT", "NUMDOSS"))