Home Articles

为纵向数据创建时间轴;用数据变量计算

Asked
Viewed 1903 times
0

我有以下示例数据框 . 数据采用长格式(纵向数据) . col1表示人员ID(对于此示例,我们只有2人) . col2表示生命事件的发生(例如0 =未结婚,1 =结婚) . 状态从0变为1实际上标志着生命事件 . 事件发生后每个测量时刻col3为1,事件前每个测量时刻为0 . 年份表示评估年份 . 月份表示评估月份(02 = 2月) .

col1   col2  col3  year   month

row.name11    A     0     0     2013   02
row.name12    A     0     0     2014   02
row.name13    A     1     1     2015   02
row.name14    A     0     1     2016   02
row.name15    A     0     1     2018   02
row.name16    B     0     0     2014   02
row.name17    B     0     0     2015   02
row.name18    B     1     1     2016   02
row.name19    B     0     1     2017   04

我现在希望创建一个以事件为中心的时间表 . 事件发生时,新变量应为0(col2 == 1) . 它应该在事件之前为负(表示事件发生前的月份),在事件之后为正(表示事件发生后的月份) .

它应该看起来像这样(参见event.time变量):

col1   col2  col3  year   month  event.time

row.name11    A     0     0     2013   02     -24
row.name12    A     0     0     2014   02     -12
row.name13    A     1     1     2015   02     0
row.name14    A     0     1     2016   02     12
row.name15    A     0     1     2018   02     36
row.name16    B     0     0     2014   02     -24
row.name17    B     0     0     2015   02     -12    
row.name18    B     1     1     2016   02     0
row.name19    B     0     1     2017   04     14

我发现我应该首先将年份和月份变量转换为日期变量(使用as.date函数) . 但是,我没有成功 . 之后如何有效地计算event.time变量?也许使用col3,因为这个变量表明它是在事件之前还是之后?

我很高兴收到您的任何建议!提前致谢 :)

2 Answers

  • 0
    #if nchar(month) is 1 then add 0 before month, otherwise use month directly. 
    #1 added to make the transformation to as.Date simple
    df$date<- paste0(df$year,'-',ifelse(nchar(df$month)==1,paste0(0,df$month),df$month),'-1')
    df$date<- as.Date(df$date)
    
    library(dplyr)
    df %>% group_by(col1) %>% 
           #Get the minmume date where col2==1 incase there is more than one 1 in the same ID
           mutate(date_used=min(date[col2==1]), event.time=as.numeric(date - date_used))
    
    
    # A tibble: 9 x 8
    # Groups:   col1 [2]
    col1   col2  col3  year month date       date_used  event.time
    <fct> <int> <int> <int> <int> <date>     <date>          <dbl>
    1 A         0     0  2013     2 2013-02-01 2015-02-01       -730
    2 A         0     0  2014     2 2014-02-01 2015-02-01       -365
    3 A         1     1  2015     2 2015-02-01 2015-02-01          0
    4 A         0     1  2016     2 2016-02-01 2015-02-01        365
    5 A         0     1  2018     2 2018-02-01 2015-02-01       1096
    6 B         0     0  2014     2 2014-02-01 2016-02-01       -730
    7 B         0     0  2015     2 2015-02-01 2016-02-01       -365
    8 B         1     1  2016     2 2016-02-01 2016-02-01          0
    9 B         0     1  2017     4 2017-04-01 2016-02-01        425
    

    数据

    df <- read.table(text="
             col1   col2  col3  year   month
             row.name11    A     0     0     2013   02
             row.name12    A     0     0     2014   02
             row.name13    A     1     1     2015   02
             row.name14    A     0     1     2016   02
             row.name15    A     0     1     2018   02
             row.name16    B     0     0     2014   02
             row.name17    B     0     0     2015   02
             row.name18    B     1     1     2016   02
             row.name19    B     0     1     2017   04
                     ",header=T)
    
  • 0

    这是一个使用 lubridate 的选项

    library(tidyverse)
    library(lubridate)
    ym <- function(y, m) ymd(sprintf("%s-%s-01", y, m))
    df %>%
        group_by(col1) %>%
        mutate(event.time = interval(ym(year, month)[col2 == 1], ym(year, month)) %/% months(1))
    ## A tibble: 9 x 6
    ## Groups:   col1 [2]
    #  col1   col2  col3  year month event.time
    #  <fct> <int> <int> <int> <int>      <dbl>
    #1 A         0     0  2013     2       -24.
    #2 A         0     0  2014     2       -12.
    #3 A         1     1  2015     2         0.
    #4 A         0     1  2016     2        12.
    #5 A         0     1  2018     2        36.
    #6 B         0     0  2014     2       -24.
    #7 B         0     0  2015     2       -12.
    #8 B         1     1  2016     2         0.
    #9 B         0     1  2017     4        14.
    

    样本数据

    df <- read.table(text =
        "             col1   col2  col3  year   month
    row.name11    A     0     0     2013   02
    row.name12    A     0     0     2014   02
    row.name13    A     1     1     2015   02
    row.name14    A     0     1     2016   02
    row.name15    A     0     1     2018   02
    row.name16    B     0     0     2014   02
    row.name17    B     0     0     2015   02
    row.name18    B     1     1     2016   02
    row.name19    B     0     1     2017   04", header = T)
    

Related