Calculation with multiple conditions in R DataFrame

Question

I have the following dataset about an investment returns:

df <- structure(list(Date = structure(c(1620604800, 1620604800, 1620604800, 
1620604800, 1620604800, 1627257600, 1627257600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), Client = c(56203, 56203, 56203, 56203, 
56203, 56203, 56203), FundName = c("SAFRA KEPLER EQUITY HEDGE FI MULTIMERCADO", 
"SAFRA KEPLER FI MULTIMERCADO", "SAFRA S&P SPECIAL FIC MULTIMERCADO", 
"SAFRA AÇÕES LIVRE FIC AÇÕES", "SAFRA CONSUMO AMERICANO FIC AÇÕES BDR NÍVEL I", 
"SAFRA S&P SPECIAL FIC MULTIMERCADO", "SAFRA CONSUMO AMERICANO FIC AÇÕES BDR NÍVEL I"
), Nature = c("Aplicação", "Aplicação", "Aplicação", "Aplicação", 
"Aplicação", "Resgate", "Resgate"), Quantity = c(145.927569, 
62.684383, 16.964545, 57.852278, 14.900635, 0, 0), Value = c(21240, 
12120, 8760, 9120, 8760, -9157.45, -1125), Saldo = c(21240, 12120, 
8760, 9120, 8760, 55.60576570794, 9198.89221970512), FundCode = c(25079578000106, 
21144577000147, 19107923000175, 32666326000149, 19436818000180, 
19107923000175, 19436818000180), NAVInitialDate = c(145.551660000114, 
193.349593000021, 521.980031999759, 157.489282999886, 639.441026999615, 
543.077092000283, 692.849144999869), NAVYesterday = c(147.526190999895, 
196.017907999922, 531.332205999643, 166.330892999889, 675.641246000305, 
531.332205999643, 675.641246000305)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

df$NAVInitialDate = df$Value / df$Quantity

The 'NAVInitialDate' column is result of "Value/Quantity" for each row. What I want to say to R is: "if in a Row we have Quantity = 0, then 'NAVInitialDate' = ABS(Value) divided by the SUM of Quantity for all rows that have the same FundName as the Fundname in the row where the Quantity is equal to 0"

The result should be, for Row 6 > NavInitialDate = 539,793 ; for Row 7 > NavInitialDate = 75,50

How can I do this? I guess it needs 3 conditions maybe

@coffeinjunky I've tried "df$NAVMC <- ifelse(df$Quantity==0,df$NAVMC == aggregate(df$Quantity ~ df$FundName,data=df,sum),df$NAVMC)" but it results in 0 for the Rows that I want — Vinícius Felizatti
– Vinícius Felizatti, Commented Aug 1, 2021 at 17:03
See my attempt below, which uses the ifelse condition in the call to mutate. Here, the column mycol is what you are looking for. — coffeinjunky
– coffeinjunky, Commented Aug 1, 2021 at 17:12

GuedesBF · Accepted Answer · 2021-08-01 17:17:11Z

We can use dplyr, group_by FundName, and mutate NAVInitialDate in all case_when Quantity == 0 within the group.

library(dplyr)

df%>%group_by(FundName)%>%
        mutate(NAVInitialDate=case_when(Quantity==0 ~ abs(Value)/sum(Quantity, na.rm=TRUE)))

# A tibble: 7 x 10
# Groups:   FundName [5]
  Date                Client FundName       Nature Quantity  Value  Saldo FundCode NAVInitialDate
  <dttm>               <dbl> <chr>          <chr>     <dbl>  <dbl>  <dbl>    <dbl>          <dbl>
1 2021-05-10 00:00:00  56203 SAFRA KEPLER … Aplic…    146.  21240  2.12e4  2.51e13           NA  
2 2021-05-10 00:00:00  56203 SAFRA KEPLER … Aplic…     62.7 12120  1.21e4  2.11e13           NA  
3 2021-05-10 00:00:00  56203 SAFRA S&P SPE… Aplic…     17.0  8760  8.76e3  1.91e13           NA  
4 2021-05-10 00:00:00  56203 SAFRA AÇÕES L… Aplic…     57.9  9120  9.12e3  3.27e13           NA  
5 2021-05-10 00:00:00  56203 SAFRA CONSUMO… Aplic…     14.9  8760  8.76e3  1.94e13           NA  
6 2021-07-26 00:00:00  56203 SAFRA S&P SPE… Resga…      0   -9157. 5.56e1  1.91e13          540. 
7 2021-07-26 00:00:00  56203 SAFRA CONSUMO… Resga…      0   -1125  9.20e3  1.94e13           75.5
# … with 1 more variable: NAVYesterday <dbl>

Muhammad Rasel · Accepted Answer · 2021-08-01 17:19:32Z

2

Try:

sum_0 <- sum(df[df$FundName%in%(df[df$Quantity==0,]$FundName),]$Quantity)
df$NAVInitialDate <- ifelse(df$Quantity == 0, abs(df$Value)/sum_0, df$Value / df$Quantity)

answered Aug 1, 2021 at 17:19

Muhammad Rasel

7244 silver badges9 bronze badges

Comments

coffeinjunky · Accepted Answer · 2021-08-01 17:10:39Z

1

Try

library(tidyverse)


df <- df %>% group_by(FundName) %>% 
  mutate(sum_Quantity = sum(Quantity), 
         mycol = ifelse(Quantity==0, abs(Value)/sum_Quantity, Value/Quantity))

Here you group by FundName and apply a simple mutate command.

To sense-check, let's have a look at the rows/columns that are of interest:

df %>% filter(Quantity==0) %>% select(FundName, Quantity, NAVInitialDate, mycol)
# A tibble: 2 x 4
# Groups:   FundName [2]
  FundName                                      Quantity NAVInitialDate mycol
  <chr>                                            <dbl>          <dbl> <dbl>
1 SAFRA S&P SPECIAL FIC MULTIMERCADO                   0           -Inf 540. 
2 SAFRA CONSUMO AMERICANO FIC AÇÕES BDR NÍVEL I        0           -Inf  75.5

edited Aug 1, 2021 at 17:10

answered Aug 1, 2021 at 17:04

coffeinjunky

11.5k42 silver badges59 bronze badges

Collectives™ on Stack Overflow

Calculation with multiple conditions in R DataFrame

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related