Calculate mean for subset of column such as applicationdate < max(applicationdate) using Data.Table in R

user_15480381 0
Viewed 70

I am trying to create a new column, where I want tocalculate the mean of historical data, then compare it with my most recent data point.

This is a snapshot of my table Table

Say, I have data points from week 15 to 22. I want to calculate the mean of missing data point percentage from weeks 15 to 21 only. Right now I have to slice the table for weeks 15-21, calculating the mean then join the value back. Can I do all of that in 1 line of code?

Here is the dput structure(list(ApplicationDate_Week = c("2021-22", "2021-21", "2021-20", "2021-19", "2021-18", "2021-17", "2021-16", "2021-15", "2021-14", "2021-13"), GNSVIPPWorkPhone_CountTidy = c(-0.233357452966715, -0.209491850431448, -0.162445638270657, -0.144578313253012, -0.127196433254655, -0.172413793103448, -0.0872703412073491, -0.0676691729323308, -0.0922794117647059, -0.045933734939759), GNSVIPPVIPLevelTidy = c(11.6577424023155, 11.3624161073826, 11.4561268866718, 11.1899691790417, 11.5022292158405, 11.5555204049351, 11.3041338582677, 10.8832796276405, 11.1113970588235, 10.7146084337349), GNSVIPPScore = c(0.759135636758321, 0.752135714285714, 0.754619109746738, 0.757030680862987, 0.76484948859166, 0.76503973426131, 0.766873261154856, 0.767475008950949, 0.773912169117647, 0.775240888554217 ), GNSVIPPREQUESTEDAMOUNT = c(1116.56150506512, 1126.86960690316, 1113.65464313124, 1135.49313533203, 1083.04720692368, 1124.04334071496, 1130.33956692913, 1135.08664518439, 1078.76838235294, 1090.27635542169 ), GNSVIPPRatioPaidLeadShoppers6M = c(42.2476360874848, 40.5152291014015, 43.8557045021645, 41.7211306948919, 39.5309453395192, 36.7297035590707, 36.0122896923077, 35.9960371273713, 34.5639904451683, 32.1152020044543 ), GNSVIPPRatioPaidLeadShoppers3Y = c(6.09824186145346, 6.04561388420461, 6.8275217979555, 6.73283313706248, 6.53066157323689, 6.06858887288441, 6.45259969890854, 6.75444378151261, 6.65001064182195, 6.42099029850746 ), GNSVIPPRatioPaidLeadApplied6M = c(0.4577996600085, 0.453706801573918, 0.467274894768491, 0.476851455675499, 0.456196172393008, 0.46552844796543, 0.471550018818216, 0.506900084033613, 0.492096231884058, 0.501193810359965 ), GNSVIPPRatioPaidLeadApplied3Y = c(0.691311602209945, 0.709030354131535, 0.704761455201443, 0.709077886817141, 0.681294424352019, 0.683463917897011, 0.692050056454648, 0.698919369747899, 0.691015403726708, 0.686982396839333 ), GNSVIPPPeriodicity_CountTidy = c(-0.664254703328509, -0.64046021093001, -0.625735482220517, -0.591762398430933, -0.639391555205875, -0.61942423283771, -0.596456692913386, -0.544575725026853, -0.550735294117647, -0.565512048192771 ), GNSVIPPMonthDayOfApp = c(9.16823444283647, 26.7440076701822, 18.9634177539013, 12.0154104791258, 4.88696564384999, 25.3954444795951, 20.9396325459318, 14.0171858216971, 6.97426470588235, 17.3542921686747 ), GNSVIPPNumTimesApplied3Y = c(13.5873353166171, 13.1888701517707, 14.129885748647, 14.1586522734707, 14.6503918022905, 14.2563917897011, 15.2299585999247, 15.5415966386555, 15.2596273291925, 15.8007023705004 ), GNSVIPPNumTimesFunded3Y = c(4.2422439439014, 4.00730747611017, 4.06043295249549, 3.984952567877, 4.41500904159132, 4.3035649981995, 4.4264207753105, 4.45420168067227, 4.47163561076605, 4.71949078138718 ), GNSVIPPNumTimesFundedUnpaid6M = c(0.814279643008925, 0.745362563237774, 0.726097414311485, 0.757278377494275, 0.871609403254973, 0.868563197695355, 0.9028980052691, 0.916806722689076, 0.923809523809524, 1.0531167690957 ), GNSVIPPMarketingCategoryLeadGen = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), GNSVIPPMilitaryBank = c(0.0112156295224313, 0.011025886864813, 0.0176515732924021, 0.011207621182404, 0.013375295043273, 0.012337867763366, 0.0144356955380577, 0.0139634801288937, 0.0143382352941176, 0.0120481927710843 ), GNSVIPPage = c(48.331982633864, 47.8451581975072, 48.4526221540036, 48.3118520594004, 48.9297665879885, 48.0907624169567, 48.6264107611549, 49.1156820622986, 49.0780882352941, 49.1096762048193), GNSVIPPAgeEmpl = c(22.9432344428365, 21.9675934803452, 21.9929649526733, 22.5007565144298, 22.6497246262785, 22.9768744068333, 22.7043635170604, 22.1306838524884, 22.8651102941176, 21.7357680722892), GNSVIPPApplNo_CountTidy = c(2.39182344428365, 2.38830297219559, 2.76745970836531, 2.77416643317456, 2.86388670338316, 2.84024043024359, 3.18208661417323, 3.31220909416398, 3.23970588235294, 3.42018072289157), GNSVIPPAppStore = c(15.9855282199711, 14.7430488974113, 15.3220772576106, 14.9918744746428, 16.4379753474954, 16.6662448592218, 16.5682414698163, 16.2169709989259, 16.6871323529412, 16.8930722891566 ), GNSVIPPCreditUnion = c(0.0734442836468886, 0.0781399808245446, 0.0874904067536454, 0.0795741103950686, 0.0823498557566221, 0.0816197405884214, 0.077755905511811, 0.0930898675259578, 0.0823529411764706, 0.0775602409638554 ), GNSVIPPCUSTEMAIL_FName_LV = c(0.296136685962373, 0.304079002876318, 0.293889741621898, 0.29356758195573, 0.292182323629688, 0.295965042708004, 0.29993562992126, 0.301187218045113, 0.291010992647059, 0.298249774096386 ), GNSVIPPCUSTEMAIL_LName_LV = c(0.358123191027496, 0.355575503355705, 0.357987081094909, 0.351115802745867, 0.354893233674272, 0.359659727934198, 0.36362125984252, 0.368595345506624, 0.364705551470588, 0.362464595103578 ), GNSVIPPDateFromDateRequestedEffectiveDate = c(1.56657018813314, 2.68648130393097, 1.75748273215656, 1.76576071728776, 1.73118279569892, 1.81999367288833, 2.09744094488189, 1.78338703902614, 1.75661764705882, 1.74924698795181), GNSVIPPDaysFromLastPayDay = c(5.3321554770318, 5.95855224751897, 6.79184066816576, 6.24417391304348, 5.42245989304813, 7.01114827201784, 7.34013605442177, 7.12942191544435, 6.48062360801782, 7.36672710788758), GNSVIPPDaysSinceLastWithdrawTidy = c(29.5900868306802, 26.934803451582, 33.6792018419033, 30.5673858223592, 29.7474429583005, 29.3669724770642, 31.4311023622047, 27.3766559255281, 30.5569852941176, 32.0395331325301), GNSVIPPDaysTillPayDay = c(11.3263386396527, 10.3005752636625, 10.1621898183679, 11.1045110675259, 12.0920535011802, 10.5855741853844, 9.99179790026247, 10.8496240601504, 11.7621323529412, 11.5670180722892), GNSVIPPFirstPmtDefaultCountTidy = c(-0.935238784370478, -0.898849472674976, -0.926579687899719, -0.91622303166153, -0.920272751114608, -0.937361594432142, -0.918963254593176, -0.915861081274615, -0.919485294117647, -0.949171686746988), GNSVIPPFundRate3Y = c(0.463055631109222, 0.453344406970208, 0.440381629585087, 0.448397317631665, 0.45974415310428, 0.46495855239467, 0.446069100489274, 0.449124159663866, 0.45170364389234, 0.44867181738367), GNSVIPPIncome_CountTidy = c(0.175470332850941, 0.226270373921381, 0.298030186748529, 0.307369010927431, 0.292682926829268, 0.333755140778235, 0.371719160104987, 0.421768707482993, 0.415073529411765, 0.490963855421687), GNSVIPPIncomeCapped = c(4248.42668567294, 4107.16932253116, 4279.30264548478, 4220.00788517792, 4185.8086748492, 4171.01028380259, 4206.05106975066, 4219.50144343, 4126.59607463235, 4198.93875990211), GNSVIPPLendProtectScore = c(695.453854505972, 694.060402684564, 695.019698132515, 695.013733183857, 695.360535292574, 694.114873417721, 695.047244094488, 695.157894736842, 694.897426470588, 694.640060240964), GNSVIPPLendProtectScorePrior = c(699.770547945206, 698.035512965051, 698.495778045838, 698.518858642178, 698.214328899637, 697.417118093174, 698.01055408971, 698.656276326874, 698.195760598504, 698.092192324658), GNSVIPPLoanToIncomeCapped = c(0.295205390738061, 0.318314765100671, 0.297501432591456, 0.307545278789577, 0.293941987936008, 0.308343530528314, 0.306673064304462, 0.310265127103473, 0.295478308823529, 0.296143335843373), GNSVIPPLPScoreDiff = c(-4.43811563169165, -4.26550169109357, -3.77171290711701, -3.76345144356955, -3.09467634603751, -3.64559248554913, -3.11345646437995, -3.71356360572873, -3.45137157107232, -3.68901632112925), RMLendProtectScorePrior = c(699.770547945206, 698.035512965051, 698.510548523207, 698.518858642178, 698.214328899637, 697.417118093174, 698.01055408971, 698.656276326874, 698.195760598504, 698.092192324658), RMNumTimesFunded6M = c(1.0012749681258, 0.960652051714446, 0.908353365384615, 0.968269545305855, 1.08077154912598, 1.09110550954267, 1.13586751975913, 1.16218487394958, 1.18633540372671, 1.3305531167691 ), RMNumTimesApplied3Y = c(13.5873353166171, 13.1888701517707, 14.1277043269231, 14.1586522734707, 14.6503918022905, 14.2563917897011, 15.2299585999247, 15.5415966386555, 15.2596273291925, 15.8007023705004 ), RMNumTimesApplied6M = c(3.19634509137272, 3.2079820123665, 3.43449519230769, 3.57212953876349, 3.59162145871007, 3.59776737486496, 3.91908167105758, 4.15672268907563, 3.99130434782609, 4.22080772607551 ), RMNumTimesAppliedPaid3Y = c(9.80195495112622, 9.6424957841484, 10.4693509615385, 10.6208701341184, 10.6515973477999, 10.3510983075261, 11.1633421151675, 11.4915966386555, 11.1540372670807, 11.4266900790167 ), RMNumTimesAppliedPaid6M = c(2.2163195920102, 2.26869027543564, 2.51983173076923, 2.63002944062807, 2.52200120554551, 2.53871083903493, 2.78471960858111, 3.03571428571429, 2.80331262939959, 2.95917471466198 ), RMNumTimesFunded3Y = c(4.2422439439014, 4.00730747611017, 4.06009615384615, 3.984952567877, 4.41500904159132, 4.3035649981995, 4.4264207753105, 4.45420168067227, 4.47163561076605, 4.71949078138718 ), RMNumTimesFundedUnpaid6M = c(0.814279643008925, 0.745362563237774, 0.726862980769231, 0.757278377494275, 0.871609403254973, 0.868563197695355, 0.9028980052691, 0.916806722689076, 0.923809523809524, 1.0531167690957 )), row.names = c(NA, -10L), class = c("data.table", "data.frame" ), .internal.selfref = <pointer: 0x00000202e7c21ef0>)

this is my current code

#GNS mean all variable

    GNS_mean = GNS_var[, lapply(.SD, mean,na.rm = TRUE), by = ApplicationDate_Week][order(-ApplicationDate_Week)]
    
    current_week = current_week_missing

# Historical mean and sd of MEAN 

    GNS_mean_historical = GNS_mean[ApplicationDate_Week < current_week,]
    GNS_mean_hist_mean = GNS_mean_historical[,lapply(.SD,mean,na.rm=TRUE)]
    GNS_mean_hist_mean[is.na(GNS_mean_hist_mean )] <- "Historical mean"
    GNS_mean_hist_sd = GNS_mean_historical[,lapply(.SD,sd,na.rm=TRUE)]
    GNS_mean_hist_sd[is.na(GNS_mean_hist_sd )] <- "Historical sd"

#GNS mean most current week

    GNS_mean_current = GNS_mean[ApplicationDate_Week == current_week,]
    GNS_mean_flag = rbind(GNS_mean_current,GNS_mean_hist_mean,GNS_mean_hist_sd)

This is my current code. The mean is not cum mean, it is the mean of all historical data point for each variable

Please let me know if you need anything else

data.table mean r
1 Answers

You can use a combination of cummean (cumulative mean) and possibly shift:

library(data.table)
DT <- data.table(W = c("2016-21","2016-22","2016-23","2016-24"), n_miss = c(0, 1, 1, 0))

If you want the current row to be included in the historic mean, then

DT[, hist_mean := cummean(n_miss)]
#          W n_miss hist_mean
#     <char>  <num>     <num>
# 1: 2016-21      0 0.0000000  # mean(c(0))
# 2: 2016-22      1 0.5000000  # mean(c(0,1))
# 3: 2016-23      1 0.6666667  # mean(c(0,1,1))
# 4: 2016-24      0 0.5000000  # mean(c(0,1,1,0))

If you want to not include the current observation, then

DT[, hist_mean := shift(cummean(n_miss))]
#          W n_miss hist_mean
#     <char>  <num>     <num>
# 1: 2016-21      0        NA  # no history
# 2: 2016-22      1 0.0000000  # mean(c(0))
# 3: 2016-23      1 0.5000000  # mean(c(0,1))
# 4: 2016-24      0 0.6666667  # mean(c(0,1,1))

(The use of cummean presumes that order matters and is controlled externally.)

user_33582720
Related