Day 10 | Data Analytics Bootcamp for Microbiologist & Other Health Professionals

Added: 2026-06-01

[00:00:00]Okay. Uh good evening everyone. Today is our day 10 of this uh three month data analytics boot camp for microbiologists and other household patient organized by technologies and consultancy limited. Uh as I said this boot camp is going to last for the period of 90 days and here we are today we are in the 10 of uh 90.

[00:00:27]Uh as always we used to start by showing our outlines. That's what we are going to cover for the day and uh our day 10 outlines as follows. We are going to have a recap on day 9. That's what we have covered uh yesterday.

[00:00:42]Then we are going to uh talk about uh if and which is the business of the day and then we'll have Q&A session and then we have conclusion and then uh we call it a day.

[00:00:57]So uh recap on the nine. Yesterday we talked about if nested and uh while talking about if nested I said uh whenever we said nested in uh anything where anything that has to do with computer or programming whenever you said nested we mean something inside something. For instance I said nested if I meant if inside a. So you will give uh a condition if the condition fails or did not meet the criteria then you give another condition which I said someone asked day before yesterday about it. Can we have more than two conditions in here? You can have more than 10 have more than 200 condition in one single cell. So that's what if nested is all about. You put inside another if and put another if inside another if as much as as much as uh you want.

[00:01:48]That's what we have covered yesterday and we gave example with uh what do you call it? We gave example with glucose level where we see you can have normally glucose level and you can have low glucose level and we went further and give more example with nested where we have low glucose level uh normal glucose level and we gave example with high glucose level where we apply if nested to calculate that or to compute that. We also give example with much more if nested where we said uh we give example with very low uh low and uh normal and then we have high and the last one which is uh very high. That's what uh the examples we have discussed yesterday.

[00:02:41]You can apply it in any kind of tests uh under your field that has um uh that has uh this type of uh uh scenario.

[00:02:55]So uh I believe that's what we have covered yesterday.

[00:02:59]So uh let's get down to today's business. So today's business is uh if and let me go back to the um let me go back to the Excel interface.

[00:03:15]I'll share the screen with you.

[00:03:19]Yeah, here we are. Let me share my screen with you again.

[00:03:25]Yeah.

[00:03:28]Okay. So this is my data set and this is what uh we have discussed yesterday.

[00:03:34]Um today we are going to talk about if and so for the if and you are treating more than one column. You are treating more than one column. So I will give simple example because I realize there are some of some that are together with us and they are not hospital. So I will have to give maybe the example in general context then later on I can give example with uh whatever I want to give example with in health uh aspect aspect um let's say we have this let's say you I want to admit someone into the university so you know before you be admitted into the university you have to pass this criteria first you have to pass jam second you have to have at least five candidates in Y.

[00:04:30]So let's say five points and above in so SSC in general. So I will say Jam for instance then I will say SSC maybe or to be precise. So you know for Y you need to have at least 180 then for SSCE you need to have at least uh five credits like this. So you must pass all these two criteria before you can be admitted into uh what do you call it into the university. If you have uh JAM but you don't have SSCE you will not be admitted. And uh if you have uh sce you don't have jam you will not be admitted into the university. So let's see how it boosts. Uh first you will say is equals to if and excuse me like this then you select this one which is what E3 if it is true. If you tested this condition and it's true. If E3 is um let's say greater than or equals to which we gave example with greater than or equals to yesterday. So I'll just if it is greater than or equals to um 180 then we give another condition before we test. You understand? Please understand this thing. we will give another condition before we give uh the what we want to give if the value is true or false. So now another condition the next condition is for SSC I will test for SSC also which is what f3. So I will say if f3 is greater than or equals to what do you call it if it is greater than or equals to 5. So this is what you have and then you close this your and bracket and then you up you just put comma and then you said um if it test if you tested all these two conditions and it pass it's perfectly okay then it will say admitted.

[00:06:47]review then you close your double quotes and then you put comma and then otherwise that is if it is false you just say not admitted like this and then you close your bracket and then you close your if statement and then you just hit enter.

[00:07:07]So you see it's admitted. Let me just uh drag the remaining let me just drag this. So you see when once your jump score is less than 180 you will it will just show not admitted.

[00:07:22]Let me just put 179. See you see it just gave you not admitted and uh let me just change it to 200 or maybe let's say 250.

[00:07:32]So you know even if you have 250 in jam if your jams if your SSE has less than two credit less than five credits you will not be admitted into the invest. So you see this one is having uh 250 in jam but he's having four credits uh he's having four credits in his uh uh O level that's why he is not admitted you must pass all the two criterias you must pass all the two criteria the first criteria is jam and the second criteria is what do you call it SSE so once you pass all the two criterias you are ready to go so this is Let's let me just quickly fill this once. Let me just say 170 here. Here I will say four or I will say seven for instance. Let me just say something here. So you see he's not admitted. Here I will say 200. Here I will say six. You see he's admitted. So this is it. Um um you will have your list. For instance, you'll be given a very large data set with uh the total number of credits in y and total number of credits in jam or total number of units in jam. And uh if you pass all the two criterias uh you are good to otherwise you failed. So this is the clear example I'm going to give which is very basic example for everyone to understand the concept. Now let's just give example with um let me give example with chromosomes.

[00:09:10]Let me delete this.

[00:09:13]Oh let me get rid of this entire I'll get rid of this.

[00:09:19]I'll clear this.

[00:09:22]Yeah.

[00:09:24]Excuse me. I will have to clear this.

[00:09:28]Yeah. clear the comment. So um let's say I am having chromosome one, chromosome 2.

[00:09:48]Let me put them like this.

[00:10:00]Okay. So I have chromosome 1, chromosome 2 like this. Now I want to know the gender. Um if this chromosome one is X and this one is Y for instance the gender is what?

[00:10:19]Male. I believe you all know that.

[00:10:22]While if this is X and this is X, the gender is uh female. Okay. So now what I will say is if is equals to if this. So I will put the and I will put the and I will say if this and then I will say this which is chromosome one is equals to X I'll close the two parentheses two uh this double quotation then I will say comma and then um I will select this one also Because um if you you must have you must have the chromosome one chromosome from uh what do you call it from the mother and other chromosome from the father. So if this is uh what do you call it? give it this this Y comma then you close this and then you said mail like this otherwise you see uh email okay otherwise you said um female. Let me just leave it like this because I'll come back to it again. So, you close your bracket and then you just uh hit enter. Excuse me.

[00:12:12]I did not put the comma here.

[00:12:14]So, you just hit enter. So, you see I'm having mail.

[00:12:20]You see, excuse me.

[00:12:28]Excuse me please. Let me rewrite the formula again.

[00:12:33]I will say is equals to if then I'll put the and then I'll test this one. If this is equals to x, you understand? If this is equals to x, comma, then the second condition, if this is equals to y. This is the second condition. So if it satisfy all these two condition then I'll close my brackets like this and then I will say comma then I will say m if both side are what do you call it if both side are m sorry the first one is x and the first one the second one is y then I will say comma otherwise that's if it is not like that I will say is equals to uh female then I will just close my bracket and then I'll hit enter. So now let me drag it.

[00:13:32]So you see if it is X and Y it's what?

[00:13:35]Male. If it is X and X is what? Female.

[00:13:40]That's how you you only have two options. If it is X and Y is male. If it is X and X is what? Female.

[00:13:50]So this you see as uh this we are just given as conditions but there might be a condition whereby maybe he the user wrongly input something maybe that is wrong or the uh all the for instance this ones all of them are empty so you don't want to see anything if both the values are empty so how do we do that so if For instance, you want to accept, you want it to accept if the other values are empty, you will just say like this.

[00:14:28]I tested this condition. Then I will add another if again I will say if and then I will open brackets. Then I will say h this is equals to x. Excuse me. It should be in double quotes. Then I will test this one again. F3. Then I will say is equals to X again. So you see F E3 is equals to X and F3D also is equals to X. Then you close your bracket and say it's what a female.

[00:15:09]Then you close your this and then uh this is your second uh condition. Now how now we want to handle if none of them is true. If maybe it's not x y and it's not xx maybe the field the two cells are empty or one of the cells are empty is empty. So what you do is just you say else then you just leave it as empty so that it will not show anything if the data set is or if the any of the chromosome is uh empty. So you close all the brackets let me drag it like this. So you see I have dragged the formula but still you see it's empty before if I didn't drag the if I drag the formula before um it will not show empty. It will just give the else statement. It will just send you a female.

[00:16:05]Let me do it again so that you see what I mean. You see like for instance here I drag. If I drag you see it just showing me female despite all the values are empty but it's still showing me female female female because it just gave one condition that if it is if the first chromosome is X and the second chromosome is Y it show male. Otherwise, whatever the case may be, it will show female. But the best practice to do that to do it is that you test for X if X is uh if the first chromosome is X and the second chromosome is Y and test another and take another condition and test it.

[00:16:44]If the first chromosome is X and the second chromosome is X, then you handle that and then we supposed to handle invalid data or empty data. So that's why I gave the uh what do you call it the other example which let me do it again a little bit slower.

[00:17:05]uh I will first say is equals to if then I will say and and then I'll open the bracket and then I will say this is equals to x and then I will close the uh double quotation and then I will say comma and then I will put I will take another uh what do you call it f3 which is the second chromosome then I will say is equals to Y like this and then I will say comma. So now you see I'm testing these two condition E3 which is the first chromosome and then F3 which is the second chromosome. So um I'm done with that I'll close my bracket then I'll put comma and say value F2 if it satisfy all the two condition of E3 and F3 I'll just say um uh male Then I'll close the double quotes and then I will say comma. Then I will add another if condition with and again then I will close the bracket and say this is equals to x again. Then I will say comma and then um uh what do you call it? F3 is equals to uh Y sorry = to X then I'll close the bracket I put my condition to say female like this then I'll put the double quotation and then I will say otherwise if none of the uh condition I give is true then I will say it should just return an empty data or you can write something like invalid entry or something like that then you just hit enter. So once we hit enter maybe I supposed to close the two brackets I have open and then I will drag. So you see now that I have drag it just give me empty data where there is nothing like this one but if I put X here and put Y here it will just give me mail. If I put X here and put X here, you see it just give me female. So once you put the uh uh what do you call it X in both side it will give you male. If you put X and Y it will give you male. If you put otherwise it will just return empty. So this is how you do uh how you can just use Excel as a template to automatically uh define whether this is male or this is female.

[00:19:59]You can still use it when it has to do maybe something like genotype. If you are dealing with genotype you can uh use this as an example. So um this is what we are going to cover for today.

[00:20:17]Uh please so please if there is any question you feel free to ask this one is saying is you just uh you will watch the video watch it and rewatch it again I believe you will understand the concept just watch it and rewatch it again please you can watch it and rewatch it again. I believe you will understand it.

[00:20:47]And another thing is that your week two assignment, your week, sorry, I said week two assignment. Excuse me, your uh the your the video uh is not available. We uploaded it but YouTube dropped it out. So uh please um I will share the relevant video with you. I will share the relevant video with you that are in that is in our YouTube channel so that you watch it uh and bless while doing that we are appealing to see if they can allow us to upload it again we just write and appeal so if they open it for you we will notify you that if it's available but for the meantime I will share the relevant video with you that discuss about the uh if in Excel So thank you very much for listening.

[00:21:45]I don't know if there is any question.

[00:21:48]Please uh you ask your question in the comment section so that I can answer.

[00:21:52]Those that are saying they are confused, you are supposed to confuse. You have all right to be confused. But take it slowly. Take it slowly. Watch it and rewatch it again. I believe most of you are health practitioners and you are aware of all this chromosome X and Y.

[00:22:07]those that are not health practitioners also you can understand this and that's why I started example with jam and which I believe everyone is combent with that you know that you must have sce and jam before you could be admitted into the university if you don't have jam you have egg you you will not be admitted if you have egg you don't have jam you will not be admitted also. So you must have both the two.

[00:22:40]That's why I started giving example with that.

[00:22:45]So um from what I can see there is no any question.

[00:22:51]Uh uh here we I think here is where we are going to draw the cutting.

[00:22:59]Yeah the one assignment course was already released. So, thank you very much for listening and have a wonderful night.