代码之家 › 专栏 › 技术社区 › Hercules Apergis

如何在R或Excel中对齐不同列中的日期

date excel r

Hercules Apergis · 技术社区 · 6 年前

DatesV1     DatesV2
29/12/1995  19/07/2001
02/01/1996  20/07/2001
03/01/1996  23/07/2001
04/01/1996  24/07/2001
05/01/1996  25/07/2001
08/01/1996  26/07/2001
09/01/1996  27/07/2001
10/01/1996  30/07/2001
11/01/1996  31/07/2001
12/01/1996  01/08/2001
15/01/1996  02/08/2001
16/01/1996  03/08/2001
17/01/1996  06/08/2001

我想发生的是 DatesV2 与中的日期对齐 DatesV1 . 这意味着 日期v2 从几个开始 NA 直到日期对齐的行。这样地:

DatesV1     DatesV2
 ...         ...
17/07/2001  NA
18/07/2001  NA
19/07/2001  19/07/2001
20/07/2001  20/07/2001
 ...         ...

Example Set 我试过这样的方法:

nhat<-which(Example$DatesV2[1]==Example$DatesV1)
nend<-which(Example$DatesV1[length(Example$DatesV1)-1]==Example$DatesV2)
Example$Apotelesma<- c(rep(NA,nhat-1),Example$DatesV2[1:nend],NA)

这是两个日期的初始测试。唯一的问题是日期以数字的形式出现。

2 回复 | 直到 6 年前

AntoniosK 6 年前

这里有一个可能的解决方案,使用一些重新塑造。我用一个简单的例子:

df = data.frame(DatesV1 = c("24/07/2001","25/07/2001","26/07/2001"),
                DatesV2 = c("25/07/2001","26/07/2001","27/07/2001"),
                DatesV3 = c("26/07/2001","27/07/2001","28/07/2001"),
                stringsAsFactors = F)

library(tidyverse)
library(lubridate)

# update to date columns (only if needed)
df = df %>% mutate_all(dmy)

df %>%
  gather() %>%             # reshape dataset
  mutate(id = value) %>%   # use date values as row ids
  spread(key, value) %>%   # reshape again
  select(-id)              # remove ids

#      DatesV1    DatesV2    DatesV3
# 1 2001-07-24       <NA>       <NA>
# 2 2001-07-25 2001-07-25       <NA>
# 3 2001-07-26 2001-07-26 2001-07-26
# 4       <NA> 2001-07-27 2001-07-27
# 5       <NA>       <NA> 2001-07-28

Hercules Apergis 6 年前

如果你喜欢的话,这是一个丑陋/混乱的方法,但它能完成任务。任何更快、更整洁的方法都会更好。

n<-nrow(DataAlignment)
Newdata<-matrix(0,5148,ncol(DataAlignment))
loops<-ncol(DataAlignment)-1
for(i in 1:loops){
  nhat<-which(DataAlignment[1,i+1]==DataAlignment[,1]) #finds the position of the first date in column 2 according to the first column
  nend<-which(DataAlignment[n,1]==DataAlignment[,i+1]) #finds the position of last date in col 2 according to the first column

  if(nhat==1 | nend != 5148){ #takes into account when they start at the same time but end in different dates
  Newdata[,i+1]<-c(DataAlignment[c(1:nend),i+1],rep(NA,n-nend))  
  }
  else{if(nhat==1| nend==5148){Newdata[,i+1]<-c(DataAlignment[,i+1])} #this takes account when they start and end at the same time
  else{if(nhat!=1){
  Newdata[,i+1]<-c(rep(NA,nhat-1),DataAlignment[c(1:nend),i+1])}}} #creates the new data
}