CODEWITHSUNDEEP
r
Ali
Ali

Reputation: 83

Divide every value in the rows by the corresponding value in the column head in r

I need to divide values in the rows by the corresponding values in the column head and then get the sum of each row

I have this data as a csv file:

df <-
  read.table(text =
"Year    2    3   4   5   6   7   8
 1985    0   4   0   4   0   0   0
 1986    1   3   3   0   9   7   6
 1987    5   0   0   0   0   0   8
 1988    7   2   0   8   0   3   0
 1989    0   0   0   1   0   2   0"
             , check.names = FALSE)

I am looking for this results 

Year    2   3   4   5   6   7   8     SUM
1985    0   1.3 0   0.8 0   0   0     2.13
1986    0.5 1  0.75 0  1.5  1  0.75   5.5
1987    2.5 0   0   0   0   0   1     3.5
1988    3.5 0.6 0   1.6 0   0.4 0     6.19
1989    0   0   0   0.2 0   0.2 0     0.49

Then I need to save the results as csv.

Upvotes: 0

Views: 682

Answers (2)

Mark Peterson
Mark Peterson

Reputation: 9570

One approach, using dplyr just to clean up the code, is to use apply and convert the column names to numeric. Note that, because you changed the row.names to a column ("YEAR") we need to handle those separately. Here, I do it by removing that column with df[ ,-1] then adding it back at the end (here, with mutate, but you could do it separately as well).

apply(df[,-1], 1, function(x){
  x / as.numeric(colnames(df)[-1])
}) %>%
  t %>%
  addmargins(2) %>%
  data.frame(check.names = FALSE) %>%
  mutate(YEAR = df$YEAR) %>%
  select(YEAR, everything())

Alternatively, and probably overkill for this example (but perhaps useful more broadly) is to use tidyr to convert to long first, which gives some potentially more flexible options for both control and summarizing:

df %>%
  gather(head, val, -YEAR) %>%
  mutate(divided = val / as.numeric(head)) %>%
  select(-val) %>%
  spread(head, divided) %>%
  mutate(Sum = rowSums(.[ , -1]))

Both give the same values, in roughly similar formats.

Upvotes: 1

989
989

Reputation: 12937

You can achieve this efficiently as follows:

m <- as.matrix(df[,-1])
m <- round(t(t(m)/m[1,]),2)
df[-1,-1] <- m[-1,]
df$SUM <- rowSums(df[,-1])

#    V1  V2   V3   V4  V5  V6   V7   V8   SUM
#1 Year 2.0 3.00 4.00 5.0 6.0 7.00 8.00 35.00
#2 1985 0.0 1.33 0.00 0.8 0.0 0.00 0.00  2.13
#3 1986 0.5 1.00 0.75 0.0 1.5 1.00 0.75  5.50
#4 1987 2.5 0.00 0.00 0.0 0.0 0.00 1.00  3.50
#5 1988 3.5 0.67 0.00 1.6 0.0 0.43 0.00  6.20
#6 1989 0.0 0.00 0.00 0.2 0.0 0.29 0.00  0.49

We convert df to matrix m in order to benefit from vectorized operations that can be done in matrix. Here is what is done:

  1. Convert df to matrix m (exclude the first column)
  2. Divide each row by the first row element-wise
  3. Put the data in m back to df
  4. Compute sums per row in df

So as to save it as csv file:

write.csv(file=fileName, x=df)

Upvotes: 0

Related Questions