How to create a column with ratio of two columns based on a condition in R?

To create a new column with ratio of two columns based on a condition in an R data frame, we can use division sign with ifelse function.

For example, if we have a data frame called df that contains two columns say X and Y and we want to create a new column with ratio of X and Y based on a condition that X is greater than 5 then we can use the below given command −

df$Ratio_X_Y<-with(df,ifelse(X>5,X/Y,NA))

Example 1

Following snippet creates a sample data frame −

x1<-rpois(20,2)
x2<-rpois(20,2)
df1<-data.frame(x1,x2)
df1

Output

The following dataframe is created −

   x1 x2
1  0  0
2  2  6
3  3  1
4  3  0
5  4  2
6  2  1
7  3  2
8  3  3
9  2  0
10 3  3
11 0  5
12 2  1
13 4  1
14 2  1
15 4  0
16 1  2
17 1  2
18 3  2
19 6  4
20 1  0

In order to find the ratio of x1 and x2 if x1 is less than 4 returning NA otherwise, add the following code to the above snippet −

df1$Ratio_x1_x2<-with(df1,ifelse(x1<4,x1/x2,NA))
df1

Output

If you execute all the above given snippets as a single program, it generates the following Output −

  x1  x2  Ratio_x1_x2
1  0  0         NaN
2  2  6   0.3333333
3  3  1   3.0000000
4  3  0         Inf
5  4  2          NA
6  2  1   2.0000000
7  3  2   1.5000000
8  3  3   1.0000000
9  2  0         Inf
10 3  3   1.0000000
11 0  5   0.0000000
12 2  1   2.0000000
13 4  1          NA
14 2  1   2.0000000
15 4  0          NA
16 1  2   0.5000000
17 1  2   0.5000000
18 3  2   1.5000000
19 6  4          NA
20 1  0         Inf

Example 2

Following snippet creates a sample data frame −

y1<-sample(0:9,20,replace=TRUE)
y2<-sample(0:9,20,replace=TRUE)
df2<-data.frame(y1,y2)
df2

Output

The following dataframe is created −

   y1 y2
1  6  7
2  2  2
3  9  1
4  2  2
5  0  0
6  6  1
7  5  1
8  3  7
9  4  9
10 6  3
11 2  8
12 1  6
13 7  7
14 3  2
15 5  5
16 9  5
17 6  7
18 5  0
19 0  1
20 5  4

In order to find the ratio of y1 and y2 if y1 is less than 5 returning NA otherwise, add the following code to the above snippet −

df2$Ratio_y1_y2<-with(df2,ifelse(y1<5,y1/y2,NA))
df2

Output

If you execute all the above given snippets as a single program, it generates the following Output −

   y1 y2 Ratio_y1_y2
1  6  7         NA
2  2  2  1.0000000
3  9  1         NA
4  2  2  1.0000000
5  0  0        NaN
6  6  1         NA
7  5  1         NA
8  3  7  0.4285714
9  4  9  0.4444444
10 6  3         NA
11 2  8  0.2500000
12 1  6  0.1666667
13 7  7         NA
14 3  2  1.5000000
15 5  5         NA
16 9  5         NA
17 6  7         NA
18 5  0         NA
19 0  1  0.0000000
20 5  4         NA

Nizamuddin Siddiqui

Updated on: 02-Nov-2021

529 Views

Kickstart Your Career

Get certified by completing the course

Get Started