CODEWITHSUNDEEP
rfunctionfor-loopdplyrdata.table
Anurag Kaushik
Anurag Kaushik

Reputation: 179

Multiply specific row element of a column to a each column in a set of columns within the same dataframe

I've been struggling with this problem for a week and I'm unable to form the logic for this one:

I have this Sort of migration table ( or pivot table): 

Region County1 County2 County 3... County 75 Multiplier
County1  0.23    0.56   0.54   ...   0.75     1200
County2  0.54    0.33   0.66   ...   0.66     1321 
...
County75 0.92    0.21   0.12   ...   0.45     1600

I want to multiply the first row element of the Multiplier to the first County column (for example: 1200*(County1))

I was trying to automate something like this: 

Table1$County1 <- Table1$Multiplier[1] * Table1$County1

I've been trying to think of either using loops/functions, dplyr, etc. but I'm unable to come up with a working code/my logic is severely flawed. Any help/advice is welcome, I've been scouring through Stack Overflow but I don't know what exactly to search for (or what this operation is called). I don't mean to cause any duplication if a similar solution already exists, and if it does I have been unable to find it :(.

(In excel this would be similar to using $ (dollar sign) with the column name and multiplying)

The output should look something like this:

Region County1 County2 ... County 75 Multiplier
County1  276    739.76 ...   1200     1200
County2  648    435.93 ...   1056     1321 
 ...
County75  1104   277.41...   720      1600

Thank you!

Upvotes: 0

Views: 1061

Answers (2)

moodymudskipper
moodymudskipper

Reputation: 47300

Here is a tidyverse solution:

library(tidyverse)
df1 <- select(df,Region1 = Region,Multiplier)
df2 <- select(df,-Multiplier) %>% gather(Region1,value,-Region)
inner_join(df1,df2,by="Region1") %>%
  mutate(value = Multiplier * value) %>%
  select(-Multiplier) %>%
  spread(Region1,value)

#     Region County1 County2 County75
# 1  County1     276  739.76     1200
# 2  County2     648  435.93     1056
# 3 County75    1104  277.41      720

data

df <- read.table(text="Region County1 County2 County75 Multiplier
County1  0.23    0.56   0.75   1200
County2  0.54    0.33   0.66   1321 
County75 0.92    0.21   0.45   1600",strin=F,h=T)

Upvotes: 4

IceCreamToucan
IceCreamToucan

Reputation: 28675

This will Map the function * across elements of df[, 6] and columns of df[, -c(1, 6)] i.e. multiply the first "County" column by the first value of the sixth column, the second "County" column by the second value of the sixth column, etc.

df[, -c(1, 6)] <- Map(`*`, df[, -c(1, 6)], df[, 6])

Map input data:

df <- data.table::fread("
Region County1 County2 County  County75 Multiplier
County1  0.23    0.56   0.54     0.75     1200
County2  0.54    0.33   0.66     0.66     1321 
County2  0.44    0.26   0.46     0.85     1472 
County75 0.92    0.21   0.12     0.45     1600
", data.table = F)

Map output data

#     Region County1 County2 County County75 Multiplier
# 1  County1     276  739.76 794.88     1200       1200
# 2  County2     648  435.93 971.52     1056       1321
# 3  County2     528  343.46 677.12     1360       1472
# 4 County75    1104  277.41 176.64      720       1600

Upvotes: 2

Related Questions