I have a dataset as follows:
Source Rev_Diff Cost_Diff Price_Diff Sales_Diff
A -10 10 11 12
B 11 -10 -10 11
C -12 10 10 -11
D -11 -11 -10 -12
How do I add a column where:
"If Rev_Diff is less than 0, print "Cost" if "Cost_Diff" with amount is less than 0, print "Price" if "Price_Diff" with amount is less than 0, print "Sales" if "Sales_Diff" with amount is less than 0, else print "We're up".
Final Output:
Source Rev_Diff Cost_Diff Price_Diff Sales_Diff Reason Down
A -10 -10 11 12 Cost (-10)
B 11 -10 -10 11 We're Up
C -12 10 10 -11 Sales (-11)
D -11 -11 -10 -12 Cost (-11), Price (-11), Sales (-12)
I couldn't quite get your how you want to structure your if statements but the following can add a new column based on the info in a previous column.
Source <- c("A", "B", "C", "D", "E")
Rev_Diff <- c(-10, 11, 12, 11, 10)
Cost_Diff <- c(10, -10, 10, -11, 11)
Price_Diff <- c(-11, 10, -10, 10, 10)
Sales_Diff <- c(12, 11, 11, -12, 11)
df <- data.frame(Source, Rev_Diff, Cost_Diff, Price_Diff, Sales_Diff)
df %>%
mutate(ReasonDown = ifelse(Rev_Diff < 0, paste("Rev", Rev_Diff),
ifelse(Cost_Diff < 0, paste("Cost", Cost_Diff),
ifelse(Price_Diff < 0, paste("Price", Price_Diff),
ifelse(Sales_Diff < 0, paste("Sales", Sales_Diff), "We're up")))))
Source Rev_Diff Cost_Diff Price_Diff Sales_Diff ReasonDown
1 A -10 10 -11 12 Rev -10
2 B 11 -10 10 11 Cost -10
3 C 12 10 -10 11 Price -10
4 D 11 -11 10 -12 Cost -11
5 E 10 11 10 11 We're up
However, ifelse will stop once it finds a TRUE statement, so this will not be able to print out multiple "Down Reasons" like you had in Source D. If you really want to print everything, I think you should be able to add 4 new columns to check on each of Rev, Cost, Price, Sales, and add a 5th column to sum up everything.
df %>%
mutate(RRev = ifelse(Rev_Diff < 0, paste("Rev", Rev_Diff), "")) %>%
mutate(RCost = ifelse(Cost_Diff < 0, paste("Cost", Cost_Diff), "")) %>%
mutate(RPrice = ifelse(Price_Diff < 0, paste("Price", Rev_Diff), "")) %>%
mutate(RSales = ifelse(Sales_Diff < 0, paste("Sales", Rev_Diff), "")) %>%
mutate(DownReason = ifelse(nchar(paste(RRev, RCost, RPrice, RSales)) > 3, paste(RRev, RCost, RPrice, RSales), "We're UP"))
Source Rev_Diff Cost_Diff Price_Diff Sales_Diff RRev RCost RPrice RSales DownReason
1 A -10 10 -11 12 Rev -10 Price -10 Rev -10 Price -10
2 B 11 -10 10 11 Cost -10 Cost -10
3 C 12 10 -10 11 Price 12 Price 12
4 D 11 -11 10 -12 Cost -11 Sales 11 Cost -11 Sales 11
5 E 10 11 10 11 We're UP