Geek Answers Handbook

Replacing rows and arranging in a data frame

It was a little difficult for me to sort some rows in my data frame in R. The data has the following structure.

df <- 
ID   TIME   DVID    AMT   DV    PERIOD
 1    0      0      50    NA    1 
 1    0.5    1      0     10    1
 1    0.5    2      0     15    1
 2    600    0      100   NA    2
 2    600    1      0     2.5   2
 2    600    2      0     4     2
 2    602    1      0     20    2
 2    602    2      0     35    2
 3    800    0      50    NA    3
 3    800    1      0     5     3
 3    800    2      0     10    3

For PERIOD >= 2I want to change the lines for DVID to DVID 1, 2, then 0, as shown in the output frame below dfout. Data must be ordered by identifier, TIME, PERIOD.

I tried to use orderBy, but I could achieve my goal. An example of use orderByis this.

library(doBy)
dfout <- orderBy(~ID+TIME+PERIOD, data=df)

The output should be as follows:

dfout <- 
ID   TIME   DVID    AMT   DV    PERIOD
 1    0      0      50    NA    1 
 1    0.5    1      0     10    1
 1    0.5    2      0     15    1
 2    600    1      0     2.5   2
 2    600    2      0     4     2
 2    600    0      100   NA    2
 2    602    1      0     20    2
 2    602    2      0     35    2
 3    800    1      0     5     3
 3    800    2      0     10    3
 3    800    0      50    NA    3

I would appreciate if anyone would share a trick on how to do this in R.

+4
source share
2 answers

, 0 3, Period 1, DVID Period, ID TIME, data.table :

library(data.table)
setDT(df)
df[,.SD[order(ifelse(PERIOD > 1 & DVID == 0, 3, DVID))], .(PERIOD, ID, TIME)]

#    PERIOD ID  TIME DVID AMT   DV
# 1:      1  1   0.0    0  50   NA
# 2:      1  1   0.5    1   0 10.0
# 3:      1  1   0.5    2   0 15.0
# 4:      2  2 600.0    1   0  2.5
# 5:      2  2 600.0    2   0  4.0
# 6:      2  2 600.0    0 100   NA
# 7:      2  2 602.0    1   0 20.0
# 8:      2  2 602.0    2   0 35.0
# 9:      3  3 800.0    1   0  5.0
#10:      3  3 800.0    2   0 10.0
#11:      3  3 800.0    0  50   NA

ifelse:

df[,.SD[order(((PERIOD > 1)*2 + DVID)%%3)], .(PERIOD, ID, TIME)]

 #    PERIOD ID  TIME DVID AMT   DV
 # 1:      1  1   0.0    0  50   NA
 # 2:      1  1   0.5    1   0 10.0
 # 3:      1  1   0.5    2   0 15.0
 # 4:      2  2 600.0    1   0  2.5
 # 5:      2  2 600.0    2   0  4.0
 # 6:      2  2 600.0    0 100   NA
 # 7:      2  2 602.0    1   0 20.0
 # 8:      2  2 602.0    2   0 35.0
 # 9:      3  3 800.0    1   0  5.0
# 10:      3  3 800.0    2   0 10.0
# 11:      3  3 800.0    0  50   NA
+3

- .

library(dplyr)
df %>%
    arrange(ID, PERIOD, TIME, replace(DVID, PERIOD > 1 & DVID==0, 3))
#     ID  TIME DVID AMT   DV PERIOD
#1   1   0.0    0  50   NA      1
#2   1   0.5    1   0 10.0      1
#3   1   0.5    2   0 15.0      1
#4   2 600.0    1   0  2.5      2
#5   2 600.0    2   0  4.0      2
#6   2 600.0    0 100   NA      2
#7   2 602.0    1   0 20.0      2
#8   2 602.0    2   0 35.0      2
#9   3 800.0    1   0  5.0      3
#10  3 800.0    2   0 10.0      3
#11  3 800.0    0  50   NA      3

data.table

library(data.table)
setDT(df)[PERIOD > 1 & DVID==0, DVID1:= 3][order(ID, TIME, PERIOD, 
                      pmax(DVID, DVID1, na.rm=TRUE))][, DVID1 := NULL][]

base R

df[order(df$ID, df$PERIOD, df$TIME, with(df, replace(DVID,PERIOD>1 & DVID ==0, 3))),]
#   ID  TIME DVID AMT   DV PERIOD
#1   1   0.0    0  50   NA      1
#2   1   0.5    1   0 10.0      1
#3   1   0.5    2   0 15.0      1
#5   2 600.0    1   0  2.5      2
#6   2 600.0    2   0  4.0      2
#4   2 600.0    0 100   NA      2
#7   2 602.0    1   0 20.0      2
#8   2 602.0    2   0 35.0      2
#10  3 800.0    1   0  5.0      3
#11  3 800.0    2   0 10.0      3
#9   3 800.0    0  50   NA      3
+4

More articles:


All Articles