All geek questions in one place

Insert rows before each group

I have the following list, and I would like to add a new line before each ID group storing the identifier, and setting A and B to 1.00.

       ID      DATEE       A      B 
   102984 2016-11-23      2.0    2.0
   140349 2016-11-23      1.5    1.5
   167109 2017-04-16      2.0    2.0
   167109 2017-06-21      1.5    1.5

Final result:

  ID      DATEE           A      B     
  102984    NA           1.0    1.0
  102984 2016-11-23      2.0    2.0       
  140349    NA           1.0    1.0      
  140349 2016-11-23      1.5    1.5
  167109    NA           1.0    1.0             
  167109 2017-04-16      2.0    2.0       
  167109 2017-06-21      1.5    1.5

So far I have used the following code, which adds an empty line at the bottom of each do.call group (rbind, by (df, df $ ID, rbind, ")), however I could not enter specific values ​​in their respective columns when I replaced "" with a vector of values.

+4
source share
6 answers

tidyverse. distinct : 'ID', mutate 'A', 'B' 1 'DATEE' NA, bind_rows arrange 'ID'

library(tidyverse)
df1 %>%
  distinct(ID, .keep_all= TRUE) %>%
  mutate_at(vars("A", "B"), funs((1))) %>% 
  mutate(DATEE = NA) %>%
  bind_rows(., df1) %>%
  arrange(ID)
#     ID      DATEE   A   B
#1 102984       <NA> 1.0 1.0
#2 102984 2016-11-23 2.0 2.0
#3 140349       <NA> 1.0 1.0
#4 140349 2016-11-23 1.5 1.5
#5 167109       <NA> 1.0 1.0
#6 167109 2017-04-16 2.0 2.0
#7 167109 2017-06-21 1.5 1.5

( , , , df1$DATEE = as.Date(df1$DATEE).)

R:

new1 = data.frame(ID = unique(df1$ID), DATEE = Sys.Date()[NA_integer_], A = 1, B = 1)
tabs = list(new1, df1)
res  = do.call(rbind, tabs)
res <- res[order(res$ID), ]

#       ID      DATEE   A   B
# 1 102984       <NA> 1.0 1.0
# 4 102984 2016-11-23 2.0 2.0
# 2 140349       <NA> 1.0 1.0
# 5 140349 2016-11-23 1.5 1.5
# 3 167109       <NA> 1.0 1.0
# 6 167109 2017-04-16 2.0 2.0
# 7 167109 2017-06-21 1.5 1.5

data.table:

library(data.table)
new1 = data.table(ID = unique(df1$ID), DATEE = Sys.Date()[NA_integer_], A = 1, B = 1)
tabs = list(new1, df1)
res  = rbindlist(tabs)
setorder(res)

#       ID      DATEE   A   B
#1: 102984       <NA> 1.0 1.0
#2: 102984 2016-11-23 2.0 2.0
#3: 140349       <NA> 1.0 1.0
#4: 140349 2016-11-23 1.5 1.5
#5: 167109       <NA> 1.0 1.0
#6: 167109 2017-04-16 2.0 2.0
#7: 167109 2017-06-21 1.5 1.5

:

# or let DATEE and other cols be filled as NA
library(data.table)
new1 = data.table(ID = unique(df1$ID), A = 1, B = 1)
tabs = list(df1, new1)
res  = rbindlist(tabs, fill = TRUE, idcol = "src")
setorder(res, ID, -src)
res[, src := NULL ]

# or a more compact option (assuming df1$A has no missing values)
library(data.table)
setDT(df1)[, .SD[c(.N+1, seq_len(.N))], ID][is.na(A), c("A", "B") := 1][]
+7

R

1

ID, rbind .

do.call(rbind, lapply(split(df, df$ID), function(a){
    rbind(setNames(c(a$ID[1], NA, 1, 1), names(a)), a)
}))
#             ID      DATEE   A   B
#102984.1 102984       <NA> 1.0 1.0
#102984.2 102984 2016-11-23 2.0 2.0
#140349.1 140349       <NA> 1.0 1.0
#140349.2 140349 2016-11-23 1.5 1.5
#167109.1 167109       <NA> 1.0 1.0
#167109.3 167109 2017-04-16 2.0 2.0
#167109.4 167109 2017-06-21 1.5 1.5

2

( ave), .

df = df[sort(c(1:NROW(df), which(ave(df$A, df$ID, FUN = seq_along) == 1))),]
df$DATEE = replace(df$DATEE, which(ave(df$A, df$ID, FUN = seq_along) == 1), NA)
df$A = replace(df$A, which(ave(df$A, df$ID, FUN = seq_along) == 1), 1)
df$B = replace(df$B, which(ave(df$A, df$ID, FUN = seq_along) == 1), 1)
df
#        ID      DATEE   A   B
#1   102984       <NA> 1.0 1.0
#1.1 102984 2016-11-23 2.0 2.0
#2   140349       <NA> 1.0 1.0
#2.1 140349 2016-11-23 1.5 1.5
#3   167109       <NA> 1.0 1.0
#3.1 167109 2017-04-16 2.0 2.0
#4   167109 2017-06-21 1.5 1.5
+4

, purrr. -, we split() ID, imap ( ) dfr ( , ), add_row() .

library(tidyverse)

df %>%
  split(.$ID) %>%
  # We don't have to specify "DATEE", absent variables get missing values
  imap_dfr(~ add_row(.x, ID = .y, A = 1, B = 1, .before = 1))

:

#      ID      DATEE   A   B
#1 102984       <NA> 1.0 1.0
#2 102984 2016-11-23 2.0 2.0
#3 140349       <NA> 1.0 1.0
#4 140349 2016-11-23 1.5 1.5
#5 167109       <NA> 1.0 1.0
#6 167109 2017-04-16 2.0 2.0
#7 167109 2017-06-21 1.5 1.5

:

imap_xxx(x, ...), , map2(x, names(x), ...), x map2(x, seq_along(x), ...), . , , .

+4

, u, , DF2. , uu, DF2 NA, 1, 1 , . .

u <- !duplicated(DF$ID)
DF2 <- DF[rep(1:nrow(DF), 1 + u), ]
uu <- !duplicated(DF2$ID)
DF2[uu, -1] <- list(NA, 1, 1)

:

> DF2
        ID      DATEE   A   B
1   102984       <NA> 1.0 1.0
1.1 102984 2016-11-23 2.0 2.0
2   140349       <NA> 1.0 1.0
2.1 140349 2016-11-23 1.5 1.5
3   167109       <NA> 1.0 1.0
3.1 167109 2017-04-16 2.0 2.0
4   167109 2017-06-21 1.5 1.5

: :

Lines <- "
     ID      DATEE       A      B 
   102984 2016-11-23      2.0    2.0
   140349 2016-11-23      1.5    1.5
   167109 2017-04-16      2.0    2.0
   167109 2017-06-21      1.5    1.5"
DF <- read.table(text = Lines, header = TRUE)

: ( , ), .

+3

, R. , ,

d1 <- df[rep(rownames(df), (!duplicated(df$ID)) + 1),]
d1$DATEE <- replace(d1$DATEE, !duplicated(d1$ID), NA)
d1[-c(1:2)] <- lapply(d1[-c(1:2)], function(i) replace(i, is.na(d1$DATEE), 1))

,

       ID      DATEE   A   B
1   102984       <NA> 1.0 1.0
1.1 102984 2016-11-23 2.0 2.0
2   140349       <NA> 1.0 1.0
2.1 140349 2016-11-23 1.5 1.5
3   167109       <NA> 1.0 1.0
3.1 167109 2017-04-16 2.0 2.0
4   167109 2017-06-21 1.5 1.5
+2

we can also use the function bythat you wanted to use, or even the function tapplyin the R base to tapplyensure placement INDICESin the list, since this is a data frame. Foe byno need to put it on the list. So, in the code below we can replace by(A,A$ID...with tapply(A,list(A$ID)..., and both of them will give the same results.

`rownames<-`(do.call(rbind,by(A,A$ID,
                  function(i) rbind(data.frame(ID=i$ID[1],DATEE=NA,A=1,B=1),i))),NULL)
      ID      DATEE   A   B
1 102984       <NA> 1.0 1.0
2 102984 2016-11-23 2.0 2.0
3 140349       <NA> 1.0 1.0
4 140349 2016-11-23 1.5 1.5
5 167109       <NA> 1.0 1.0
6 167109 2017-04-16 2.0 2.0
7 167109 2017-06-21 1.5 1.5

This does not require sorting, as this can distort the order in which the data was previously.

+2
source

Source: https://habr.com/ru/post/1683965/

More articles:


All Articles