CODEWITHSUNDEEP
rtidyverse
Josh_PL
Josh_PL

Reputation: 25

Add two Rows then divide by a row

So I have a data set. I need to add every other row and divided by the second row. Row 2 divided by (Row 1 + Row 2)

1   10779
2   911
3   13897
4   2780
5   13552
6   1112
7   15352
8   2036
9   11779
10  790

So for this data, I would need [Row 2 / (Row 1 + Row 2)] so (911/(10779+911)) and [Row 4 / (Row 3 + Row 4] etc. I am close to using tidyverse but can't quite get it.

Upvotes: 1

Views: 560

Answers (5)

b_takhel
b_takhel

Reputation: 55

Check this out.

library(dplyr)
a=seq(1:10)
b=c(10779,911,13897,2780,13552,1112,15352,2036,11779,790)
d=c(0,b[1:9])
c=b+d
df=data.frame(a,b,ratio)
df <- data.frame(col1= a, col2 = b,ratio=ratio)
df %>% dplyr::filter(row_number() %% 2 == 0)

#Output
# col1 col2      ratio
# 1    2  911 0.07792985
# 2    4 2780 0.16669665
# 3    6 1112 0.07583197
# 4    8 2036 0.11709225
# 5   10  790 0.06285305

Upvotes: 0

r2evans
r2evans

Reputation: 160437

I think your data can be re-organized by column. This code takes every other row and puts them into their own column:

ind <- seq(1, nrow(dat), by = 2)
dat2 <- data.frame(V1 = dat[ind,], V2=dat[ind+1,])
dat2
#      V1   V2
# 1 10779  911
# 2 13897 2780
# 3 13552 1112
# 4 15352 2036
# 5 11779  790

From here, your math is straight-forward data.frame operations, whether base, tidy, or datatable:

Base

with(dat2, V2 / (V1 + V2))
# [1] 0.07792985 0.16669665 0.07583197 0.11709225 0.06285305

or as a new column:

within(dat2, { newcol = V2 / (V1 + V2) })
#      V1   V2     newcol
# 1 10779  911 0.07792985
# 2 13897 2780 0.16669665
# 3 13552 1112 0.07583197
# 4 15352 2036 0.11709225
# 5 11779  790 0.06285305

tidyverse

library(dplyr)
dat2 %>%
  mutate(newcol = V2 / (V1 + V2))
#      V1   V2     newcol
# 1 10779  911 0.07792985
# 2 13897 2780 0.16669665
# 3 13552 1112 0.07583197
# 4 15352 2036 0.11709225
# 5 11779  790 0.06285305

data.table

library(data.table)
as.data.table(dat2)[, newcol := V2 / (V1 + V2) ][]
#       V1   V2     newcol
# 1: 10779  911 0.07792985
# 2: 13897 2780 0.16669665
# 3: 13552 1112 0.07583197
# 4: 15352 2036 0.11709225
# 5: 11779  790 0.06285305

Upvotes: 2

RyanFrost
RyanFrost

Reputation: 1428

Is this what you're looking for?

library(readr)
library(dplyr)
df <- read_table("v1 v2
1   10779
2   911
3   13897
4   2780
5   13552
6   1112
7   15352
8   2036
9   11779
10  790")


df %>%
  mutate(grp = (v1 - 1) %/% 2) %>%
  group_by(grp) %>%
  summarize(v = v2[2] / (sum(v2)))
#> # A tibble: 5 x 2
#>     grp      v
#>   <dbl>  <dbl>
#> 1     0 0.0779
#> 2     1 0.167 
#> 3     2 0.0758
#> 4     3 0.117 
#> 5     4 0.0629

Though I would second @r2evans's comment that this data may be better organized by spreading the rows into separate columns. This will let you be a bit more expressive about the ratio you're trying to create. Here's an example:

library(tidyr)

df %>%
  mutate(grp = (v1 - 1) %/% 2,
         rank = (v1 - 1) %% 2 + 1) %>%
  select(-v1) %>%
  pivot_wider(names_from = rank, values_from = v2, names_prefix = "Row") %>%
  mutate(val = Row2 / (Row1 + Row2))
#> # A tibble: 5 x 4
#>     grp  Row1  Row2    val
#>   <dbl> <dbl> <dbl>  <dbl>
#> 1     0 10779   911 0.0779
#> 2     1 13897  2780 0.167 
#> 3     2 13552  1112 0.0758
#> 4     3 15352  2036 0.117 
#> 5     4 11779   790 0.0629

Upvotes: 2

hamagust
hamagust

Reputation: 856

A possible solution (but not tidyverse):

vector<-c(10779,911,13897,2780,13552,1112,15352,2036,11779,790)    
sapply(c(2:length(vector)),function(x){
      vector[x]/(vector[x-1]+vector[x])
    },simplify=TRUE)

Upvotes: 0

Duck
Duck

Reputation: 39595

Do you mean this:

library(dplyr)

df %>% mutate(Lag=lag(V2),Val=V2/(V2+Lag))

   V1    V2   Lag        Val
1   1 10779    NA         NA
2   2   911 10779 0.07792985
3   3 13897   911 0.93847920
4   4  2780 13897 0.16669665
5   5 13552  2780 0.82978202
6   6  1112 13552 0.07583197
7   7 15352  1112 0.93245870
8   8  2036 15352 0.11709225
9   9 11779  2036 0.85262396
10 10   790 11779 0.06285305

Upvotes: 1

Related Questions