2. Filling Missed Data using XLSTAT Monte Carlo Multiple Imputation Technique

hello everyone today I will show you how to feel those missing values there are three types of missing values according to alison 2001 the first one that are missing completely at a random is the second one that I'm missing at a random and the serpent the sword one types of missing the value is when data is not missing at random then the first one that is missing completely at random it's the event that leads to missing data is independent of observable variables and of an observable parameter it should occur entirely at a random when data are complete missing completely at random the analyst performative the data are unbiased data is missing at random when the event that leads to a missing data is related to a particular variable but it is not related to the body of the variable that has a missing data this is the most common case data is not missing at random when the data is missing for a particular reason that's in this my tutorial I want to show you how to feel missing data using excel stats for hydrological in repose and meteorological data according according to excels 2018 user manual difference are available depending on the needs and the data for quantitative data Excel start allows to remove observation with missing I lost to remove observation with missing value the second one allows us to use a minimization method and the circle allows us to use the nearest neighbor approach this the other one allows us to perform my pass algorithm the other and the final one is its access that allows us to use Monte Carlo the multiple imputation having said this I will show you how to miss those quantitative data using HCl stat therefore this is my white Xen their faults is row a row rainfall data then I will show you how to how I feel this rainfall machining the values using SQL stat this is raw data raw data when he gets this raw data it is already assembled in such a way there is here here this is dead and this is nuts individual nuts channel if you body much there is here an e in E stands for not available that is in a missing value that's in a M is equal to missing value this stands for missing the value therefore I used to repress this any value easy number therefore before that or Cicero data I need to arrange this raw data in a monthly basis for January the whole year January the holy earth Febraury the whole year match should be performed or arranged in such a way this is January 4 for January 1980 here is the data for 81 year this is January months for 82 generally once there is 30 31 that is search one rows are available here such one here yeah therefore in LLP you have 31 rows here we serve 61 data it's for few body there is here 29:28 here this is Qi lang tell name that Azara with here that are valid here IATI as you can see okay mostly 2829 data rows are available and the rest are missing values this is not as you can see therefore I did to fill this missing values then for every month and you should do the same for February March April May June July August then you need two people on the main city basis because when explicit performs missing data guess because it's with release the error because mostly this missing data can be filled using the same or correlated data data type therefore that's why we need to have it on dimensions on the monthly buds for already seen in accuracy of predicting as those missing values that's why then you will after installing this excel statute is to before performing this missing data analysis using Excel you need to install Excel start by downloading you can also that there is also a trial version to install this axial stat but for me I did I solve this excellence that therefore I just opted I need to open this Excel stuff up in here this is a steal stuff I can need to click this one to opened therefore I will start by just feeling it was generally man's missing values these are missing values these are missing values is a missing values and I need to field those missing values therefore I will click here preparing data yet missing data is available here click missing data then this is Sita therefore I need to select this data it needs they're back so let's this one selects the whole dot e ZN press this one okay then data type this is quantitative data of course there are two option contacted in Kentucky later this work for me for this kind of situation could click this quadratic data estimation method there are around four or five nipples here algorithm nearest neighbor witless vitamin I remove the Ross with missing data this is AB is to be she misses it by it she'll start therefore for me I need to say that this Monte Carlo you complete multiple imitation algorithms for click this one number of invitation you can increase this one to three hundred then access this option to how to put the result in the distinct his difficult one therefore I need to activate this this belt the top effortless is that ok variable levels acted this option if the first sets of each column an occultist 11 therefore decide the first color call amounts of row is a level therefore I need to activate this one okay then press here ok ok click here continue there are 31 rows and 37 column therefore and stability near continue ok click try these parables and now minimum value for 1980 this maximum value this and mean and the standard deviation for MEMS device will set in such a way then this is the report as you can see there is a new sheet created here missing value for January as you can see this bolts this board numbers are missing value values which are filled using multi Monte Carlo multiple imputation algorithm these are bolts are just missing values and this is these are approximation values therefore yes these are transmission values as you can see now this is the Z data this T value completely fill this dash away now what the problem is solve it with now you can just feel the rest as it is now let's fill the 4t body before that rename its this missing for what generally generally zinc then click here fury then open now missing data then did it clicks use tough it worries favor it up then make sure the tab is active okay before that feel very insolent the type of data in which each yes that data numbers are not the same it is I will feel using this mid-thirties row I click on tattoo data here okay I will leave it like a Monte Carlo click this one variable okay the n plus here okay continue okay you have to be careful while you are filling those few body lenses because gotta take ya number of dates for every you're different therefore you need to drag here and worry here yeah we name it everybody here your body you need to be care this row or if if this row hub had missing values you need to just you use all other meters to fit this row a row therefore I need to copy which copy here list yeah now the observation 29 is this one the other it is are not 29 therefore I will leave the rest as it is this is for everybody and also just uncheck or delete this Patrol yeah no border okay yeah this is our temporary data for epi yeah therefore you can prepare in such a way let me see my data thank you very much for the necklace I will show you how to perform that's a trend unless using the man kinder for today this is enough thank you very much

Channel: Hydraulics and Hydrology Tutorials

Views: 6,602

Keywords: xlstat, data analysis, excel, missing rainfall data, missing rainfall data estimation, statistics, data visualization software, monte carlo simulation excel, imputation methods for missing data, imputation, idw, normal ratio method

Id: 8O1Llk6-3Eg

Length: 12min 47sec (767 seconds)

Published: Sat May 02 2020