Add each column with the last value of the last column of the row to dataframe R
I want to add each column of data with the last column of the row in R. My data file is
L E B1 P B2 M Value
5 5 0 20 2 5 100
10 6 0 40 15 2 150
6 15 0 50 6 10 160
1 10 0 55 5 20 160
0 20 0 80 0 20 200
10 1 20 80 10 10 250
8 2 40 30 5 10 300
5 3 60 30 5 20 350
5 4 30 75 5 20 400
1 0 50 80 0 10 400
2 0 40 60 5 20 500
0 0 60 50 0 30 500
So the first line will be like -
L E B1 P B2 M Value
5*100 5*100 0*100 20*100 2*100 5*100 100
10 6 0 40 15 2 150
6 15 0 50 6 10 160
1 10 0 55 5 20 160
0 20 0 80 0 20 200
10 1 20 80 10 10 250
8 2 40 30 5 10 300
5 3 60 30 5 20 350
5 4 30 75 5 20 400
1 0 50 80 0 10 400
2 0 40 60 5 20 500
0 0 60 50 0 30 500
I tried using lapply
lapply(df1, function(x) x * tail(x,1) )
But this takes the value of the row, so how do I get each value of the last column of the row, or any particular column to add with all other column values in R
source to share
It is not clear if you want to multiply or divide (there seems to be a contradiction between what you are asking and your own attempt), but here is an approach to multiply:
cbind(mydf[-length(mydf)] * mydf[[length(mydf)]], mydf[length(mydf)])
# L E B1 P B2 M Value
# 1 500 500 0 2000 200 500 100
# 2 1500 900 0 6000 2250 300 150
# 3 960 2400 0 8000 960 1600 160
# 4 160 1600 0 8800 800 3200 160
# 5 0 4000 0 16000 0 4000 200
# 6 2500 250 5000 20000 2500 2500 250
# 7 2400 600 12000 9000 1500 3000 300
# 8 1750 1050 21000 10500 1750 7000 350
# 9 2000 1600 12000 30000 2000 8000 400
# 10 400 0 20000 32000 0 4000 400
# 11 1000 0 20000 30000 2500 10000 500
# 12 0 0 30000 25000 0 15000 500
The basic idea is to simply multiply all columns except the last one by the values in the last column. Since this column has been removed, you add it back with cbind
.
source to share
Here's another basic R option:
n <- ncol(df)
df[-n] <- df[-n] * df[[n]]
Note: running this code will modify the existing data.frame file. If you want to create a new data.frame and keep the old one as it is, you'd be better off using the answer by Ananda Mahto or one of the others.
source to share
To complete the image, you can also update the data from the link with the package data.table
library(data.table)
setDT(df)[, names(df)[-length(df)] :=
lapply(.SD, "*", df$Value),
.SDcols = -"Value"]
df
# L E B1 P B2 M Value
# 1: 500 500 0 2000 200 500 100
# 2: 1500 900 0 6000 2250 300 150
# 3: 960 2400 0 8000 960 1600 160
# 4: 160 1600 0 8800 800 3200 160
# 5: 0 4000 0 16000 0 4000 200
# 6: 2500 250 5000 20000 2500 2500 250
# 7: 2400 600 12000 9000 1500 3000 300
# 8: 1750 1050 21000 10500 1750 7000 350
# 9: 2000 1600 12000 30000 2000 8000 400
# 10: 400 0 20000 32000 0 4000 400
# 11: 1000 0 20000 30000 2500 10000 500
# 12: 0 0 30000 25000 0 15000 500
source to share