How to prevent ifelse statement from clearing out previous data?

Question

I'm trying to import some accounting data to a new software and need to add a debit column journalItemLine_debitAmount and a credit column journalItemLine_creditAmount that are filled if there is a debit or credit in the source data. When I run my script, I end with only the data marked Credit when positive and none of the Debit when positive data.

data = within(data, {
journalItemLine_debitAmount = ifelse(If.Positive. == "Debit" & Amount>=0, Amount, "")
journalItemLine_creditAmount = ifelse(If.Positive. == "Debit" & Amount<0, -Amount, "")
journalItemLine_debitAmount = ifelse(If.Positive. == "Credit" & Amount<0, -Amount, "")
journalItemLine_creditAmount = ifelse(If.Positive. == "Credit" & Amount>=0, Amount, "")
 })

Here's the source data:

Amount  If.Positive.
0.00    Debit
-546    Debit
789     Credit
45789   Debit
-34657  Credit

Here's what I would like:

Amount     If.Positive.     journalItemLine_debitAmount     journalItemLine_creditAmount
0.00       Debit            0
-546       Debit            546
789        Credit                                           789
45789      Debit            45789
-34657     Credit                                           34657

I also tried this if statement but nothing seemed to happen.

journalItemLine_debitAmount = if((If.Positive. == "Debit") && (Amount>=0)){Amount}
journalItemLine_creditAmount = if((If.Positive. == "Debit") && (Amount<0)){-Amount}
journalItemLine_debitAmount = if((If.Positive. == "Credit") && (Amount<0)){-Amount}
journalItemLine_creditAmount = if((If.Positive. == "Credit") && (Amount>=0)){Amount}
})

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. It probably has more to do with your data which we can't see than your code. — MrFlick, May 17 '19 at 18:24

MrFlick · Answer 1 · 2019-05-17T19:06:48.990

0

I think you just want

data = within(data, {
  journalItemLine_debitAmount = ifelse(
    (If.Positive. == "Debit" & Amount>=0) | (If.Positive. == "Credit" & Amount<0),
     abs(Amount), NA)
  journalItemLine_creditAmount = ifelse(
     (If.Positive. == "Credit" & Amount>=0) | (If.Positive. == "Debit" & Amount<0),
     abs(Amount), NA)
})

This avoids reassigning to the same value twice and just uses the absolute value to keep the number positive.

edited May 17 '19 at 19:06

answered May 17 '19 at 18:57

MrFlick

195,160
17
277
295

It worked most of the time but filled several lines with NA even though they had a value in amount. – JNietert May 17 '19 at 19:06
@JNietert I had a typo in first comparison of the creditAmount ifelse. I've fixed it now. – MrFlick May 17 '19 at 19:07
Ahh, the sixth line of code needs to be Amount>=0. Thanks! This worked! – JNietert May 17 '19 at 19:10

score 0 · Accepted Answer · answered May 17 '19 at 19:23

you could also try as:

data$journalItemLine_debitAmount[If.Positive. == "Debit" & Amount>=0]<- Amount
data$journalItemLine_debitAmount[If.Positive. == "Debit" & Amount<0] <- -Amount
data$journalItemLine_creditAmount[If.Positive. == "Credit" & Amount<0] <- -Amount
data$journalItemLine_creditAmount[If.Positive. == "Credit" & Amount>=0] <- Amount

or simpler:

data$journalItemLine_debitAmount[If.Positive. == "Debit"]<- abs(Amount)
data$journalItemLine_creditAmount[If.Positive. == "Credit"] <- abs(Amount)

Hope it helps!

How to prevent ifelse statement from clearing out previous data?

2 Answers2