CODEWITHSUNDEEP
rif-statementconditional-statements
Vinícius Felizatti
Vinícius Felizatti

Reputation: 61

Calculation with multiple conditions in R DataFrame

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

Upvotes: 3

Views: 518

Answers (3)

Muhammad Rasel
Muhammad Rasel

Reputation: 724

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)

Upvotes: 2

GuedesBF
GuedesBF

Reputation: 9878

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>

Upvotes: 2

coffeinjunky
coffeinjunky

Reputation: 11514

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

Upvotes: 1

Related Questions