CODEWITHSUNDEEP
r
Paolo Lorenzini
Paolo Lorenzini

Reputation: 725

Inserting a blank row every row in data frame

I have many data frames stored in a list (list_df) and one of those data frame has a column (c1) which looks like this :

c1

4

6

1.5

2

3

7.5

1

9

I would like to calculate the sum of every two rows and add the value every alternate row and leaving an empty blank in between:

Output:

c1     c2

4

6      10

1.5

2      3.5

3

7.5    10.5

1

9      10

Right now I have a code to create the sum of every two rows from column c1

for(i in seq_along(list_df)){
  list_df[[i]]$c2<-
    rowsum(list_df[[i]][,1], as.integer(gl(nrow(list_df[[i]]), 2, nrow(list_df[[i]]))))
}

However it throws me an error because the length of c1 is in this case 8 but the length of the newly created column c2 is 4. How to modify this code in a way that the values of the newly created column c2 are inserted in alternate row by leaving a blank?

Thanks

Upvotes: 0

Views: 376

Answers (5)

Retired Data Munger
Retired Data Munger

Reputation: 1445

To handle cases where there may not be an even number of rows, you can try this: library(tidyverse)

df1 <- data.frame(
  c1 = c(4, 6, 1.5, 2, 3, 7.5, 1, 9, 42)
)

# add new column
df1$c2 <- NA_real_

# now split df1 in groups of two and add result
result <- df1 %>%
  group_by(key = cumsum(rep(1:0, length = nrow(df1)))) %>%
  mutate(c2 = if (n() == 2)c(NA, sum(c1)) else sum(c1)) %>%
  ungroup %>%
  select(-key)  # remove grouping variable

> result
# A tibble: 9 x 2
c1    c2
<dbl> <dbl>
  1   4    NA  
2   6    10  
3   1.5  NA  
4   2     3.5
5   3    NA  
6   7.5  10.5
7   1    NA  
8   9    10  
9  42    42  
>

Upvotes: 1

Kris Williams
Kris Williams

Reputation: 122

I don't know how wild I am about this option after seeing others, but it works!

df1 <- data.frame(
  c1 = c(4, 6, 1.5, 2, 3, 7.5, 1, 9)
)

dfList <- list(df1, df1)

## DEFINE HELPER
func <- function(x) {  
  result <- c() # initialize an empty list
  for (i in seq_along(x)) {
    if((i %% 2) == 1) { # if row count is odd, NA
      result <- c(result, NA)
    } else { # else add the current value to the previous value
      result <- c(result, x[i] + x[i-1])
    }
  }
  return(result) # return result
}

## APPLY HELPER TO LIST
res <- lapply(dfList, function(x){
  x$c2 <- func(x$c1)
  return(x)
})

Upvotes: 1

dww
dww

Reputation: 31452

You can use 

df = data.frame(c1 = c(4,6,1.5,2,3,7.5,1,9))
df$c2 = NA
df$c2[c(F,T)] = colSums(matrix(df$c1, 2))
#    c1   c2
# 1 4.0   NA
# 2 6.0 10.0
# 3 1.5   NA
# 4 2.0  3.5
# 5 3.0   NA
# 6 7.5 10.5
# 7 1.0   NA
# 8 9.0 10.0

Upvotes: 2

Ulises Rosas-Puchuri
Ulises Rosas-Puchuri

Reputation: 1970

this is another way:

lapply(list_df, function(x){

  i = 1
  c2 = vector('numeric')

  while(i <= length(x$c1) ){

    c2[i*2 -1] = NA

    c2[i*2]    = sum(x$c1[i*2-1], x$c1[i*2] )

    i = i + 1
    if( i*2 > length(x$c1)) break
  }

  data.frame(c1 = x$c1, c2)
})

Upvotes: 1

aatrujillob
aatrujillob

Reputation: 4826

c1 = c(4,6,1.5,2,3,7.5,1,9)
ID = rep(1:(length(c1)/2), each=2)

library(data.table)

DT = data.table(ID,c1)
DT

DT[, sum2 := Reduce(`+`, shift(c1, 0:1)), by = ID]
DT

Upvotes: 3

Related Questions