CODEWITHSUNDEEP
rdatabasedataframemultiplication
Twan Stoffers
Twan Stoffers

Reputation: 17

Manipulate rows of a data frame with specific variables per row

I'm trying to standardize my fish numbers in different columns by an unique sampled area.

I've got the following simplified data set:

Year <- c(1990:2019) 
Location_nr <- c(1:30)
df <- data.frame(Year,Location_nr)
df$Sample_surface <- sample(10, size = nrow(df), replace = TRUE)
df$Fish1 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish2 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish3 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish4 <- sample(0:500, size = nrow(df), replace = TRUE)

I would like to standardize fish numbers (column: Fish1, Fish2, Fish3 and Fish4) per row by: (/"Sample_surface")/100

I've been stuck on this issue for more than a day now. I sincerely hope somebody can help me with this. Many thanks in advance!

Upvotes: 1

Views: 49

Answers (3)

akrun
akrun

Reputation: 886938

We can do this in a vectorized way

cols<- grep('Fish', names(df))
df[cols] <- (df[cols]/df$Sample_surface)/100
head(df)
#  Year Location_nr Sample_surface      Fish1     Fish2     Fish3     Fish4
#1 1990           1              3 1.09666667 0.0500000 1.4100000 1.5300000
#2 1991           2              3 0.07333333 0.3833333 1.2233333 0.7033333
#3 1992           3             10 0.41000000 0.0930000 0.2850000 0.1730000
#4 1993           4              2 2.21500000 1.3050000 1.9750000 1.3600000
#5 1994           5              6 0.51333333 0.3900000 0.2633333 0.7416667
#6 1995           6              5 0.26800000 0.9100000 0.2400000 0.6020000

data

set.seed(123)
Year <- c(1990:2019) 
Location_nr <- c(1:30)
df <- data.frame(Year,Location_nr)
df$Sample_surface <- sample(10, size = nrow(df), replace = TRUE)
df$Fish1 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish2 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish3 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish4 <- sample(0:500, size = nrow(df), replace = TRUE)

Upvotes: 0

AlexB
AlexB

Reputation: 3269

Another option would be to use dplyr and tidyr. In most cases, it is recommended to manipulate data in a tidy format.

df %>%
  pivot_longer(-c(Year:Sample_surface), names_to = 'Fish', values_to = 'Value') %>%
  mutate(Value = Value / Sample_surface / 100) %>%
  pivot_wider(names_from = Fish, values_from = Value)

or

cbind(df %>% select(-starts_with('Fish')),
      df %>% select(starts_with('Fish')) / df$Sample_surface / 100)

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388817

We can use sweep to apply all the "Fish" column in row by corresponding Sample_surface. 

cols<- grep('Fish', names(df))
sweep(df[cols], 1, df$Sample_surface, `/`)/100

head(df)
#  Year Location_nr Sample_surface  Fish1 Fish2 Fish3 Fish4
#1 1990           1              3 1.0967 0.050 1.410 1.530
#2 1991           2              3 0.0733 0.383 1.223 0.703
#3 1992           3             10 0.4100 0.093 0.285 0.173
#4 1993           4              2 2.2150 1.305 1.975 1.360
#5 1994           5              6 0.5133 0.390 0.263 0.742
#6 1995           6              5 0.2680 0.910 0.240 0.602

Or we can use apply row-wise

df[-c(1:3)] <- t(apply(df[-c(1:2)], 1, function(x) x[-1]/x[1]/100))

data

set.seed(123)
Year <- c(1990:2019) 
Location_nr <- c(1:30)
df <- data.frame(Year,Location_nr)
df$Sample_surface <- sample(10, size = nrow(df), replace = TRUE)
df$Fish1 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish2 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish3 <- sample(0:500, size = nrow(df), replace = TRUE)
df$Fish4 <- sample(0:500, size = nrow(df), replace = TRUE)

Upvotes: 2

Related Questions