End To End Cricket Data Analytics Project Using Web Scraping, Python, Pandas and Power BI

Key Takeaways

T20 Cricket World Cup was finished just few weeks back with England claiming victory over Pakistan and today we are beginning a cricket data analytics series using same T20 World Cup data. We will begin this project by scrapping data from ESPN Cricinfo website then doing data cleaning and transformation in pandas and then eventually building dashboards in Power BI. Before we begin any technical work, let's look at the exact problem statement and our special stakeholders that we got in this project. The whole atmosphere is charged with one type. We really like your cricket. Surrender Earth. You need to fight 8 billion people for that. We will destroy. Then you get nothing. Negotiate. You defeat us in cricket, you get Earth. If you lose, join me as an intern. Deal. Tony, we got to save Earth. Give me your best 11. Best 11 what? Data analyst? No, cricket players. As you saw, Planet Sporta has challenged Planet Earth to play cricket and Nick Puri has assembled the secret agents of Field to work on this project to find out the best 11 players based on the T20 World Cup cricket data. Tony Sharma is in charge of this project. He's not only a senior data analyst, he's also a cricket subject matter expert. So in the next video, you will see what kind of algorithm Tony Sharma comes up to pick this best 11 team who can go and defeat the aliens. At the end of the video, we are going to give you a challenge based on this project and by working on that challenge, you will be able to win exciting prizes. So make sure you watch till the end. Nick, this is the requirement you gave me. You said we don't know the strengths and weaknesses of our opponents, but give the best 11 from our planet. So this is what I'm going to do. I'm going to give you a team that will score 180 runs on an average. At the same time, this team will be able to defend 150 runs. So you have a margin of 30 runs to play with. Do you think that's enough for you? Yes, this sounds like a good target because although aliens have not played cricket, they have a technology where they can learn things really fast. Okay, so this is how I've done this. I've I've made different positions for players and I've selected parameters for each of them. I'm going to quickly show this parameters to you and let me know if you know if you want to add some parameters or you know anything you know just this is just a idea I want to give on how I'm going to select the team. The first ones are the openers. They are going to open the innings. These are the power hitters. So they will be hitting the balls out of the park. And they will also score runs. It's just not about hitting. So that's how I've selected the parameters. Batting average, strike rate, boundary percentage very important. They should be scoring 50% of their runs over 50% of their runs by boundaries because in the power play, the fielders are going to be inside and they should be hitting outside the inner circle. That's the plan and they should be giving us at least 50 runs this partnership and within the first five overs. Got it. Uh so we'll basically get players like Sehwag, correct? Uh Nick, Sehwag isn't playing anymore. Oh, okay. I see. Okay. All right, then we have the middle order or the anchors. So here yeah, they won't be hitting ball hitting the ball as hard as openers, but they can shift gears and hit the balls if needed. So they will have a better batting average. They will bat for longer time. That's why I've also included the average balls faced. You know, they also bat you know like aggressively if they want. So those are the kind of players you'll have here. I'm going to select three players for this position. So overall we have five players now. So these five players will give us at least 120 plus runs in the in the 13 to 14 overs. That's the plan. I would like to see Virat Kohli in this file list. Oh, he's playing. I think he's certainly a part of this team. Great. And of course we are we are making this you asked the special requirement is to consider the World Cup T20 2022 for you know for the selection of the team. So I'm select I'm taking only that particular data for that. Okay. All right. And so this is going to be an interesting role. We I'm going to get one player for this role, a finisher role. So if we are chasing this particular person should be able to hit like crazy, go berserk, but if we lose wickets early like if we lose the middle order so badly, so this person will be able to stabilize innings and order the rest of the lower order batsman. So that's the kind of player we're looking for. We're looking for more of a batsman here than a bowler. So uh you know ideally I would need an all-rounder here, but again uh with with more uh a batting all-rounder rather a bowling all-rounder. And this will leave us with five more players and my seventh and eighth player will be all-rounders or the lower order batsman. So mostly these can be spinners and because the the last three bowlers I'm going to keep it for fast bowlers. So these spinners are the ones who can also hit and they can hit uh without thinking like they come to bat, they just start hitting. So that's the kind of players we want because they will be mostly coming under uh around 17th or 18th over and uh so that's why I have selected uh you know I kept the parameter in such a way their batting average should be at least 15 and you can see their strike rate is more than 140. So even if they score 15 runs, they should be scoring that in around eight or nine balls. And you can see their bowling economy is really good. It's less than seven which means if these two players bowl all the 20 overs, they will be giving only 140 runs. Which is good for us because our team target is to keep the defense under 150. And their strike rate is less than 20 which means if they bowl 20 balls, they will get one wicket at least. If they bowl all the 20 overs, they should be getting at least six wickets. Mhm. Nice. Yeah. And here comes the specialist fast bowlers because we need to rattle the team. We need to rattle the the the score downs, you know. I don't think they should be having capability to face these bowlers. This is a wolf pack we have got. They are threatening. They can take wickets at least every 16 balls or even less than that. This is the parameter I've set for them and they can bowl very fast and they can also you know bowl dot balls over 40% which means if they bowl four overs, around 10 of that will be dot balls which is great for a T20 game. Exactly. Dot balls are so precious for T20. Yes. And with these 11 players, Nick I'm pretty sure we'll save the Earth. What do you think? The algorithm looks pretty solid to me. I'm excited to see the final results which our Power BI dashboard can produce. Yeah. So this is already in progress. So I'll be uh you know I'll be showing you the you know the final dashboard and we can do an analysis together to pick the final 11. Looking forward. Thank you. Thank you, Nick. See you then. Bye. Folks, we had our resources on GitHub, but we have moved to this resources section on codebasics.io because the file sizes were getting very big. So go to codebasics.io, click on resources here and you will be able to find all the files here. So here you can click on download and then you can just create an account for the codebasics website and log in. Once you're logged in, you will be able to download all these files. Now you can download whatever files you need. Sometimes people may not need all the files. Although we are going to provide download all button later, but this way you can download just the required files. Here for example, I'm going to download my web scraping code. And once it is downloaded, you can open it, unzip, and here you'll be able to see all those JS file that we need in bright data. Similarly, you can download rest of the files as well. Folks, why so much fuss? Just use something ready-made. For example, BrightData. BrightData uses proxy networks for building web scrapers that work seamlessly. They have various solutions such as residential proxies, web unlocker, and so on. They also provide ready-made data sets. The tool that we are going to use specifically is called data collector. And if you use the link which is given in the video description below, it's a special link for CodeBasics. You can log in. Here it says work email, but don't worry, you can use your personal email ID and still log in. Just select some values from these two drop-downs, and you'll be able to create an account. Once the account is created, if you look at the dashboard, you will find that you will have $15 balance. I want to thank BrightData for giving this $15 free credit for all CodeBasics viewers. For our uh data scrapping work, we need hardly three or four dollars. So, this is more than enough. You can use this credit for your other projects, too. We are going to capture four type of data for our project. Number one is this detailed match results table. So, we will scrap this entire table. When you click on this particular scorecard link, you will get a detailed bowling and batting scorecard. So, we are going to uh grab all these tables, as well. And then, when you click on any player, you will get player specific information. So, we'll grab few fields from this, also. I'm going to click on user dashboard and go to something called a data collector. Now, data collector is a web crawler, basically, which will go to the website and collect the data. I have already created the four collectors. So, this one is for the match results. So, let's say this page, you want to grab that, right? Let's look at that code and understand how that works. So, when you right click and say inspect, it will show you the HTML tags for that particular page. And you can click on this, and let's say you're getting this particular table, right? Or this particular, let's say cell. And you look at this, see, this is one row, this is second row, third row, and so on. And this particular table is inside this tbody, and the table class is engine table. So, now let me show you my collector. So, I will go to my collector here and say edit code. And in this one, you will see the interaction code, as well as the parser code. Now, this is a JavaScript code. JavaScript is better suited for web scraping, because uh JavaScript code runs inside the browser, so it becomes easier for that. So, here, this particular link that you're seeing is nothing but this particular page. So, what I'm saying in my collector is go to that page, and then collect parse. Now, when you say parse, it is going to execute all this code. Now, once again, this is a JavaScript code, where I am locating the engine table table, then I'm going into tbody, and then tr.data1. So, if you look at here, see, there is engine table, then tr.tbody, and then tr.data1. And this one is an array. So, if you go through this array one by one, you'll be able to get all these records. So, and that's exactly what I'm doing here. See, the first element is team one, second is team two, then winner, and so on. So, if you look at any row here, see, the first one is team one, second one is team two. You can see on the left side. Then, the winner, the margin, the ground, and so on. So, that's exactly what we are doing here. And then, it will grab all this data into match summary array. This is JavaScript array, basically, and it will return that, okay? So, let me just run this here. It will take some time, but you'll see that in the browser, the page is loaded. So, it executed this navigate function, went here, and now it is collecting all this information. So, if you look at it in the output, you will see this information is now available as a JSON. And if you download that JSON file, this is how it would look. Now, let me just format it, so that you can see it properly. You can see that now, you know, you have team one, team two, who is the winner, margin, ground, match date, and the scorecard. So, we grab this particular first table in its entirety. See, we have the entire match results for T20 World Cup. Now, the way BrightData collector works is they're going to use a smart proxy network, okay? And using that infrastructure, it will do web scraping, so that you have seamless data collection without having to worry about some website blocking your IP, and so on, because it is sort of like VPN. It is using different IPs, and you will not have any trouble in your data collection process. The The kind of trouble that you have when you are using plain Python script. If you're new to BrightData, what you can do is you can use one of the templates. So, for example, I can click on develop a self-managed uh collector, and you can use one of these templates. See, here you are collecting data from uh Quora, for example. Here, you are collecting data from YouTube, okay? So, you use that template, and you can run that collector, and you will get an understanding of that. I have provided the code for all these four collectors on GitHub. The link is in video description below. So, if you look at the web scraping codes, see, here is the batting summary, here is the bowling summary. So, these are the code that you have. You have interaction code, and you have parser code. Now, let me show you batting uh summary web collector. So, here, I can go to batting summary, click here, and then advance section, edit the code. Here, what we are doing is this first page that you have is this page only, okay? So, you're going through that match summary page, and then you are collecting all the links. All right, which links? So, these links. See, when you right click and say copy link address, you get this particular link, right? So, we are collecting all those links in this particular code, all right? Then, we are calling next stage. So, in the next stage, it will execute this particular code, okay? And this will be the link of that match score. And if you want to see it from the previous run, I have, see, I have all these links, which I collected from the first stage. And in the second stage, what you will do is you will go through this scorecard. So, for example, this is my scorecard. And if you inspect this particular page, and let me click on this element and go here, right? So, if you do this one, you will find that you are collecting data from a table called CI scorecard table. And if I check that in my collector here, you will find that here, I am collecting that particular table and going through my first inning, second inning, etc. This code is not as hard as you think. It is just HTML. You need to have basic knowledge of HTML. You're going through those HTML elements, and you're just trying to grab data from it. So, you're going through entire list all the scorecard one by one, and you're putting uh that summary here, okay? And when you click on this, it's going to take time. What it will do is it will load this page, then it will go to this link, then it will you know, grab all this tables, the batting tables, then it will go back, it will it will go to the second link, collect all the tables, and so on. When you click on this, it will do a sample run for one or two matches. But if you want to run this collector, uh you know, for the entire data collection, what you can do is you you can click here, and you can say initiate manually. So, it will run the whole collector. You can also set it on a schedule, or initiate by API in some Python code, as well. I'm going to show you match results execution, because it doesn't take much time. So, you click on this, and in the delivery preferences, you know, I'm going to type in my email ID here. Uh so, it will deliver the data as a JSON to my email ID. All right. So, just say initiate manually. Start. And see, it's starting. Now, it will run internally. It is using smart proxy framework, and it is going to ESPN Cricinfo website, grabbing that data. And once data is ready, it would have sent me an email. All right, when I check my email, I got the match results. And when you click on download results, it would have downloaded this particular JSON file. And when you look at this JSON file, see, I have the entire match summary. Okay? So, this way I ran all the collectors and grabbed all the data, and I have put that data on GitHub in T20 JSON and CSV file, all right? So, this is JSON files. If you want to directly get the data, you can get it from here, but I highly recommend that you use Bright Data for data collection because data collection is super important part of any data science or data analytics project. When you log in, make sure you are seeing this $15 credit and then you can go to data collection collectors and create your first collector by clicking on this open IDE. So, when you do that, you can just say start from scratch. You are creating a blank collector. You are not using any of these templates and when you see the JavaScript UI, what you will do is you will use the code which I have provided you. Again, check video description carefully for all the download instructions. You will have this file, for example, T20 World Cup match results.js. So, open that file and it has two sections, interaction code and parcel code. So, for interaction code, I can just copy paste this here and you know, use that code here. Whereas for the parcel code, copy paste just that particular portion here in the parcel code and that's it. This is your collector. It is ready, okay? And you can run it, verify it. You can just say finish editing. You can give the name to that collector. So, let me just say finish editing here. So, it takes few seconds and then your new collector is ready. You can edit collector name. You can call it whatever, T20 World Cup match summary. Okay. You saved it. If you just cancel that icon, you will see that collector. Now, you can run that collector manually. If you want to edit the code, you can once again go inside. So, this way you create four different collector. Again, I have provided the code to you. If you need any help, you can click on the help button. You can do learning. You can watch Bright Data tutorials on YouTube as well. We are done with web scraping part and the files that we extracted from ESPN Cricinfo are available in T20 JSON files folder. Now, if you want to use these files ready-made, check video description below. You should be able to download these files. And if you look at these four JSON files, for example, batting summary, we have this batting summary JSON element for each match. For example, Namibia versus Sri Lanka. So, there is one element in this array. Then, the second element in this array is UAE versus Netherland and so on. Now, when we pull this data in Power BI, it would be beneficial if the data is available in CSV file, something like this where you know, it's just a single flat table and you have cricket match and the corresponding batting score, right? And then see here also there is UAE and the batting score and so on. And here is the batting position. So, we need to do transformation, basically. We need to transform this JSON file into this particular CSV format. You will also see an additional column, for example, if the player is out or not out. In the JSON file, we did not have that information, okay? Uh all we had was, let's say if dismissal is blank, that means the player was not out. But if the dismissal had some string, then that means the player was out. So, we have to do this data transformation and Python Pandas is probably the best way of performing this transformation. If you don't know about Python programming language, you can go to codebasic.io super affordable easy Python course for total beginners. You can follow that. Then for Pandas, search code basics Pandas tutorial. You find my playlist, which is very popular. And just watch first, you know, maybe six or seven videos. And that's it. It will take you less than 1 hour. Assuming you have now some Python and Pandas knowledge, now let's start a Jupyter notebook and work on the transformation. I went to my cricket analytics folder and launched Jupyter notebook by running this command. And it looks like this. See, I am at a location where T20 CSV files and JSON file directories are there at at the same location. I am going to create a new Python 3 notebook and import some necessary libraries. We are going to use Pandas and other library called JSON. And then, I will open the first file, which is the match results, okay? So, I will say match results as F and then data is equal to in JSON. You can just say JSON.load file pointer and it will load that data for you. So, let's print what kind of data it loads. So, here I have the match summary. Remember the match summary table? So, let me show you the JSON file so that you get an idea. So, my match summary is this one where basically, it's a match who won the match, right? So, between these two, Namibia won the match by this run and so on. And if you look at match summary element, see, it's it's just one element. And if you look at this array, this array also has one element. Okay? So, let's look at that element. So, first of all, data array has only one element. You can confirm it by see printing just one element. And then data zero and then match summary, if you print that's your main list where you have all the match results. So, what I can do is I can create a data frame out of it and I will just say DF match is equal to PD. dot data frame. In the data frame, you can supply the entire list as an array and if you print that head of that data frame, see, wonderful. So, my data frame is created and I can just quickly check how many elements it has. And there is a method called shape. When you do that, it prints the shape, basically, 45 rows and seven columns. And I'm going to do few processing steps, which is, let's say, I will use this scorecard as kind of a key of this particular data frame. What I mean by that is I want to treat this as a match ID so that I can connect with other tables because I have other tables and when when I import them in Power BI, I need a way to link them. Short of like primary key, foreign key in SQL terms. So, I want to treat this scorecard, which is a unique ID, basically, as primary key for this. So, let's do that. So, I will just rename that column. I will just say DF match.rename. And how do I want to rename the column? So, I want to say that rename scorecard as match ID. Axis is equal to one, which is, you know, you have column axis and rows axis. And when you print head, you will see the column is renamed. If you are not getting this, just hold on. Later on, you will understand why I am calling it a match ID. Now, once I have this particular data frame, I want to export this data into a CSV file, okay? So, that you know, all the data all the data is in single CSV with nice columns. But before I do that, let me process the batting summary. So, the other file that I have is batting summary. Okay, so I will say batting summary. You can add markdown columns, markdown rows in the notebook. And let's see how this one looks like. So, batting summary has this particular format. Let's see in the Notepad++. Where is my batting summary? Huh. So, batting summary, first of all, the outer array has multiple JSON objects. You see, these are all multiple JSON objects. If I open the first JSON object, that has a element called batting summary. See, it has just one element, batting summary. And in that batting summary, there is a score of one match. So, Namibia versus Sri Lanka. See all the players, number one player, number two player. They are in one element. And if you close this and open the second element, okay? You will find UAE versus Netherland match. So, all these matches are presented by one single JSON element inside my array, okay? So, now what I will do is I will go through that those records. So, I will say for record in data and this each record is one match. And one match has multiple records for the player, right? So, if I print, for example, record batting summary what I will get is the batting summary of one match. So, Namibia versus Sri Lanka, okay? So, this this. So, I will get total, I think, 11 records. And if you want to just append everything in in in one array, right? Because our eventual goal, folks, is to get a single list. Basically, I should have a single list where all the matches are present. And if you want to create a single list in pandas, what you can do is I can create a list called all records, and then I can just keep on appending. I can just say all records.extend. And when I say extend, it is just extend is basically you have one array and you're appending another array after that. So, let me just show you. So, let's say you have an array called A. Okay? And if you have another array called B. And if you say A.extend(B) and if you print A, you get this. So, it's just joining those array, okay? So, similarly here, I'm joining all the records and after I join all of them, I can print all records. See? I got all the records in a single flat list. So, Namibia versus Sri Lanka, if I just scroll down, I will see UAE versus Netherlands and so on. And from this, I can create our data frame. Data frame like this. And when you print see? I get the it this is continuous. So, if you print tail of it, you will see the final match, Pakistan versus England. I don't know if you've seen that. It was pretty interesting match, World Cup final 2022. Now that I have a data frame ready, let's let me look at I just want to do some analysis. I'll just print, let's say, first 11 elements or so. And see, I want to do a couple of things here. First of all, here the dismissal column, I want to convert this column into out or not out, okay? So, I want to have a column like this which tells me if the player is out or not out. And the way you can do that is look at the dismissal column and if you don't find anything in it, if it is blank, it means the player was not out. So, let's first do that. And how do you do that? Well, you can create a new column called out or not out in pandas just by doing this based on the dismissal column. So, my dismissal column is this. Okay? And on this, you can use apply method. If you've seen my pandas tutorial, you'll know. So, you can say on dismissal column, apply some transformation and create a new column called out or not out. So, this is the new column. This is how we create a new column. And on the existing column, I want to apply some transformation. What is that transformation? Well, lambda x. Lambda is this is just a short way of writing Python function. So, here I'm going to use ternary operator. So, I'll say player is out if x. When I say x, you you're getting each value from this column, okay? So, if let's say x is this. If it is out if the length of x is greater than zero, else it is not out. Okay? So, I will Let's see. Okay. DF is not defined, obviously. I'm used to writing DF all the time. So, you see, you have out and not out. And if I print few more records, you will see whenever there is a blank, it is not out, otherwise it is out, okay? All right, now that I got out not out, I don't need this dismissal column. So, I can just drop that column, okay? And this is how you drop it. You will just say drop which column you want to drop. And in place is equal to true. If you don't specify in place is equal to true, it will not modify that data frame. It will return a new data frame, okay? So, when you run this, dismissal is gone and all you have is out and not out. And you have another column called batsman name which has some issues, which is see this kind of special characters you have. You want to remove this special character and you can just simply locate all the special characters and then you know, apply some lambda function. For example, you can say DF.batsman.apply this. You can use regular expression. You can use replace function. You can do n number of things. But just to keep things simple, I will just uh you know, I found this as well as there there were other records where I found this character. So, I'm just removing them. And now you see in Kusal Mendis uh Mendis, actually, you don't see Let me see. You don't see that particular character. So, let's see. Yes, see? Here you don't see that extra character. All right. Now, how do you connect this particular data frame with the match? Because as I said, for our visualization purpose later on in Power BI, we need a way to link all these tables. Now, just carefully notice these two tables, okay? So, this is one table that I have. Okay? And this is the second table that I have. I can just use snipping tool and just kind of take a screenshot of that so that you get an idea. So, let's say I have this particular table, right? And then I have this another table. So, now let's try to match these two. So, I want to now connect these two tables. How do I do that? See, I have Namibia and Sri Lanka, right? I have team name and here I have Namibia versus Sri Lanka. I mean, that is the only key I have between these two tables so that I can join them or I can link them basically cuz I have scorecard here, but I don't have a scorecard here in this particular table. So, yeah, the only thing I have is match, right? So, here it says Namibia versus Sri Lanka, here it says team one and team two. Now, I can say maybe take team one and then use vs in the middle and then do team two, right? And that way I can join them. But the problem could be in in this table, the names could be reversed, right? So, here, let's say it's Netherland versus UAE, whereas here, for example, uh let me print this. See, here I have UAE versus Netherlands. See? So, if I just simply say team one versus team two, it's not going to work. I have to use both the combination, team one, then vs, then team two, then team two, vs, and team one. So, that is the way I can connect. And for doing that, I need to go back to this code and create a kind of like a dictionary, okay? So, dictionary like this. So, let me just uh do that here. So, I want to create a match IDs dictionary. Okay? And the dictionary will look something like this. So, let's say there is Na- mibia versus Sri Lanka. Okay? So, let's say it looks something like this. And then I can have maybe a match ID as a value, right? And then I can have same thing but in the reverse order. Why I need this? Because the order is not guaranteed. So, I need to have both. And then I have, let's say, Netherland versus UAE, correct? So, let's say I have this kind of Python dictionary where I have team one versus team two, team two versus team one, and the match ID. Then that is going to be helpful. So, I I can create that dictionary by going through So, let me just remove this. I think you got an idea. Or or let me put it in the second cell. So, I can say for index, row in DF.match.iterrows. So, there is a function called iterrows which will go over each row one by one. And then every row has team one and team two. Okay? And you can use that as your key one. So, this will be your key one, correct? Key one because see, Namibia versus Sri Lanka. So, I'm creating that by doing this. And then for doing Sri Lanka versus Namibia, you can have key two. So, key two is nothing but just you know, you're reversing the order, team two versus team one. And then you want to add that into this dictionary. So, here I will say add this into my dictionary, key one. Okay? So, key one is this, right? And what is the value you're adding? The match ID. So, you will say row match ID. And you will do the same thing for key two. And when you look at now match IDs dict, it looks something like this. See? Every team, the original order, reverse order, and the corresponding match IDs dict. Now, you'll be like, "Okay, so how do I use this dictionary?" Well, see. Okay, let's go back. So, the way you use this dictionary is to bring match ID column in this particular data frame. So, how do I bring match ID column in this particular data frame? Well, this has a match column, right? So, I can look into my match IDs dictionary. So, see, if I for this match, I want to get a match ID. How do I get it? So, I will say match IDs dict and this Okay. in quote So, if you give this, see, you get the match ID. So, I can create a new column in this data frame. Okay. I can create a new column in this data frame call it match ID equal to batting. So, I want to apply transformation on this match column. So, I will say on this column apply transformation. Map is short of like is a function similar to apply and you are going to give this mapping. So, you're just mapping it basically. Okay? And when you do this, you get match ID. See? Now, you found a way to link these two tables. Okay? And then, you will export this particular table as is into a CSV file. And the way you can export this table is by doing this. Okay, I have that file open by the way, that file, but I'll just say temp.csv for example. And when I see temp.csv See, in my CSV files folder, see temp.csv. And looks something like this. It is the same file as I was showing before. Basically, it is this file. Okay? Now, in the interest of time, I'm not going to go over the entire transformation for other files, too, because the code can be bigger and the tutorial will get so long. So, I'm going to share with you the entire notebook. You can take a look later on, but see, here I first process match results, then I process batting summary, and at every point I'm exporting the file. So, I exported match summary first, then I exported the batting summary, and then I exported bowling summary. See, bowling summary and the player information. Now, player information, the name I have given is no images because for player, I need their image as well. So, I have two files like player.csv with no images. So, let me show you that. Let me remove this temp.csv and see, I have dimplayers.csv and no images. So, no image is something that my notebook is giving. Okay? And you can see there is player name, there is team, batting style, bowling style, player role, and the description. Some players don't have description, but some player do. So, and in this file, we have manually added their images. So, we collected their images manually and we added it here in dimplayers.csv. So, if you open that file, you will see an extra column. Okay? So, I have dim players open. Looks like already. So, in that we have an extra column called image. And if you click on this image, you will see the player's picture, basically, right? See, if you click on any any image, you will see that particular player's image. All right? So, I hope you had fun. And now, in the next step, we will be importing this CSV file into Power BI. I'm going to launch Power BI Desktop. Uh I have already installed this application. If you don't have this, you can just YouTube and find the installation instructions for Power BI Desktop. Click on that. Close this and just say file save as. You know, just go to downloads and just give some file name. I will just say T20 Crick. And then, you will go to get data, click on more, and import the entire folder of CSV files. Now, if you look at our downloads folder, so just check video description below. I have given the instruction to download the CSV files. So, these CSV files, either you have used Bright Data to capture it, or you can use ready-made files, which I have given to you. And here, in this folder, you see five CSV files. Now, for dim players, we have two versions, one with images, one without images. So, I'm going to delete the no images.csv file because that's not going to be useful, and I will here click on folder, connect, and go to that folder and grab all those four files. So, this PC, you go to downloads, T20.csv file. Okay. It's going to import all those four files. Now, here you will click on transform data to perform data transformation in Power Query. So, Power Query is a component inside Power BI that allows you to do data transformation. Here, right-click and say duplicate. I'll tell you why I'm doing that, but go to the first step, and then click on this binary. Okay? Now, when you click on that, it's going to expand that file. So, our first file, if you look at the steps on the right-hand side, see, the first file is dim match summary. And when we clicked on it, what it did is it expanded that particular file. So, I will just call this dim match summary. And similarly, I'll just duplicate uh these uh raw steps multiple times. And then here, I will expand dim players. So, once again, click on this and it's going to expand it. I will call it dim players. And do similar things for uh next two files. Now that I have expanded all the files, I will quickly look at the data and perform some transformation. When I'm looking at dim players, I see it did not recognize the column names properly. This can happen. So, what you will do is you will say go to the transform tab and then use first row as headers. So, see, now it is using name, team, image, etc. In the previous step, if you look at it, it did not have that, right? It was having column one, column two, etc. And now, I said use this first row as a header row. So, after that, it looks something like this. And when I glance through the data, I I notice couple of issues. Number one is the player name has this in bracket C. This is captain, basically. So, in the cricket scorecard, if someone is a captain, in bracket you will see C. I don't need that. I just need a player name. So, you are going to apply some transformation where you'll use extract option and you'll say text before delimiter. So, delimiter is bracket, right? So, you want to get text before the bracket. So, in this underscore C, if you get text before this bracket, you'll get only Shakib Al Hasan. So, you'll say Okay. And you see that C is gone. So, in the previous step, you see Shakib Al Hasan in bracket C, but in the next one, that C is gone. It is a usual practice to do data trimming after this delimiter step. So, you will say format and trim, and that will just in case if there are extra spaces, it is going to remove those. One other thing that I do is I can uh sort these values. So, if I say sort ascending, and when you just quickly glance through these values, you will find some duplicates. See, Matthew Wade, the same record, due to some reason, appeared two times. And you want to remove these duplicates. In real life, you will always find these kind of data problems. Okay? So, I'm going to now remove sorted rows here, and then I will say right-click and remove duplicates. So, that will remove the duplicates values. See, right now, the rows is 213, and in the previous step, we had 219 rows. So, it removed six duplicates. Many times, what will happen is you are building dashboard. At that time, you will encounter issues. And then, you have to go back to Power Query and perform this data transformation. Okay? I kind of knew about these steps, so I'm doing it, but in general, you will glance through it, you will sort it, you will do things to figure out all these data issues. And now, we are done with dim players data transformation. We can move to the next one, which is dim match summary. Here, when I glance through this data, it all looks good. The only thing I'm going to do here is create a new column called stage. Now, in T20 cricket, any match in this particular World Cup of 2022, any match that was played before 22nd October was a qualifier match, otherwise it was considered to be the Super 12. So, let me show you that. So, the logic here that we're going to use is if date is less than 22nd October 2022, then the match was qualifier, okay? Qualifier, otherwise it was Super 12. So, I'm going to create a new column called stage, and it will be a conditional column. So, let me just show you so that you get an idea. Click on add column and say conditional column, and in that conditional column, you have a match date, see? Match date. So, the new column name, by the way, is stage. Stage could be qualifier or Super 12. And if match date is before Before which date? Well, 22nd October. So, I'll go and say 22nd October. Okay? I just know it. It's just a one fact that I know, and if that is the case, then this stage was qualifier. You know, in any tournament, there's qualifier, and then there is a main tournament. Otherwise, it is called Super 12. And when you say okay, see? You find something like this. So, this was all of these were qualifier, and then then tournament started from here. Actually, it's not less than equal to, it is less than this. So, I'm just going to modify the formula. And just hit okay. So, see, Scotland Zimbabwe was the last qualifier match, and then uh from next match onwards, uh we had a main tournament, which is Super 12. So, just to summarize, I created this new stage column here, and then I'm going to change the type to text. So, here ABC 123 means text or number, so I'll just change it to only text. Then the next one is fact bowling summary. So, I go here, and then I will just rename few columns. For example, bowling team, I can just say team. So, double click this and just say team, okay? And then you have things like zeros, so I'll just say zeros. Instead of having zero I I feel zeros is a better column name. So, once again, you will see that you might be renaming a lot of columns. So, here I'll just say four, six, six, and so on. Now, see, for calculating different statistics for bowling performance, I'm going to do some transformation with overs. So, many times, you know, you have 2.5 overs, which is two over and five balls. So, it is better if I have a column called balls. And then if I have a run, then I can divide run by balls to get the bowling uh strike rate and so on. Therefore, I will just uh you know, create a balls column from this. And the way you do that is you go to overs, and then you first split the column, okay? By delimiter, and that delimiter is dot. So, you will just say okay, and it created two columns, overs one and overs two. So, for example, see, 2.5. I I previously had 2.5 here, okay? So, let's see. 2.5. So, it will now say two and five in one one and second column, right? Overs.1 Overs.2. And then here, in this column, null values you can replace with zero, right? Four and null is basically four overs, so this can be replaced with zero, and you can just go here, replace values, null, replace that with zero, okay? And now, you can add a new custom column, so you will just say add, create a custom column, call that balls, and balls is nothing but overs one into six, correct? So, two overs will be how many balls? 2 into 6, 12. And then if you have any additional ball, you add that, and that is overs two. So, you insert that, and then you get this particular formula. So, if you look at different overs here, for example, uh let's say this is this first one is four overs, so that will be 24 balls, correct? So, see, 24 balls. But if you look at this particular uh row, let's say this row is 18 balls, but let's check this one. This is 17 balls. So, 17 balls is two over and five balls. So, two over two over is 2 into 6, 12, and then five is 17. See, 17 here. And then let's perform some quick transformation in batting summary as well. Again, I'm going to rename all these columns, fours and sixes. And I don't like that this column is in a text form, so maybe I can have a simple column called out. You know, I can call this column out, and if it is out, then value is one, otherwise it's zero, okay? So, not out is zero. So, how do you do that? So, you can just go to transform and say replace values, and if the value is not out, out is zero, and if the value is out, once again, you can just say replace values. So, replace values, if the value is out, then that means zero. So, now we got nice binary zero one type of column. Not out is zero, and then out is one, actually. Okay? So, see? Something like this. And by the way, this balls column should be number, so I'll just say this is the whole number. And in batting summary also, you need to do similar transformation for captain, where you remove the bracket C text. So, once again, you click this, and you just say extract text before delimiter, bracket, okay, and you will see see, that C thing is gone. See, Grihan, whatever in bracket C, and the next one, it is gone. So, now we are done with our transformation. You can go to home, close and apply. After data transformation, we need to look into data modeling. For that, you can go to this particular tab, and you will see it has already established some relationship based on column names. So, let me just pull fact tables in the middle, okay? So, I'm going to just arrange them nicely. And fact tables are basically the the transactions, and then the dimension tables are basically the attributes. So, you can just Google if you want to read more about fact dimension table and the star schema. So, now, you can see that when you hold your mouse cursor here, based on match {underscore} ID, it established this link between the two, okay? It's like one to many, see? You have one to many relationship. Similarly, here also, based on match ID, it established this relationship. Now, we will link dim player table with this one. So, here the player name is basically bowler name here, and that thing is called name here, okay? So, I will left mouse click, drag and drop on name here, and that will establish a relationship. When you hold your mouse cursor over here, see, bowler name and name are highlighted, you which means these two tables are linked through that particular column. Same thing here, batsman name is linked to name here, and that's it. Our data modeling in this case is pretty simple. Once data modeling is done, the next step would be to create DAX measures. So, DAX measures are something that we'll be using in building the actual visuals, okay? So, for that, here I will create a category or a folder where I can keep all those DAX measures. So, I will click on enter data here, and I will call that key measures, okay? So, key measures. Here it says key measures two, just ignore that. This is just a category where you're going to add all your measures. So, here I will click on this, and I will say new measure. Now, we need couple of measures, and I have given an Excel file. Again, check video description, you can download it from here. I have given a complete list of measures that you need for this project. So, the first one is total runs, okay? So, it's that one is pretty simple, so you will say here, uh you can click on this icon to kind of expand it, and control scroll to make it bigger. So, total total runs is equal to sum of Total runs is equal to sum of all the runs in fact batting summary table, right? So, here fact batting summary runs and that's it. So, that creates a total runs measure. Uh this column one is not needed, so you can right click uh you can click and delete it. So, total runs is one measure that we have already created. Now, let's create the other one. So, the other one that we need is total innings batted. Okay? And that one would be So, once again, click here. And I'm going to just copy paste that formula. So, control C, control V. Okay? So, fact batting summary and you're getting a count of match IDs and that will be the total innings batted. Now, what's the purpose of this measure exactly if if you have no idea on DAX etc. I would suggest that you can check my course on codebasics.io for Power BI where I have explained Power BI pretty much in detail with a lot of practical and fun learning where we completed a real life project with 1 and 1/2 million records. And the course has received amazing reviews. So, go check that out. I'm going to go quickly through DAX measure creation because this is not a Power BI detail course, correct? That's why. All right. So, let's say I have created these two measures and if I want to quickly check it, what I can do is I can pull up a player's name here and then in that table I can add those measures. So, let's say I add total runs, for example. And I can look for a particular player. For example, my favorite one especially in the last series was Suryakumar Yadav and here it says he scored 239 runs, right? You can quickly check if the measure is correct or not by opening fact batting summary CSV file and when that file is open you can create a filter. You can go to data and filter and you know, here in the filter you can type in Suryakumar Yadav. So, let's say let's say you have a file like this, you can say Suryakumar Yadav and if you look at his runs if you if you just highlight this you will see see 239 and that's what I have. 239 All right? Similarly, you can create other measures. So, what other measures do we have here? So, let's let's check that. The other one is total inning dismissed. Okay? And total inning dismissed would be Let's see. So, here I'll say new measure. Total innings dismissed is equal to sum of Once again, you can control scroll to see the whole thing. Uh Okay, batting summary and out. So, how many times the player got out? Okay, total innings dismissed. And when you pull that here So, when I say total inning dismissed here, say I'm I'm getting some error and if I click on this, it says that function sum cannot work with value type string, which means we created this measure based on this out column and out column is not a number, it looks like. So, you can go click here home, click on, you know, transform data to go to the power query and you see the the out column in fact batting summary is ABC, which means it's text. So, you can change it to a whole number. So, just change it to a whole number. Say close and apply. And then it will show up fine. So, Aaron Finch was out two times and and you can see all this statistics. In the similar fashion, you will be creating all the rest of the DAX measure. Okay? So, once again, this file is given to you, you download it and I want you to create all these measures. And once you create all these measures, it's going to look something like this. See here. It will look something like this. So, you will by the way create all these measures and also group them in these kind of folders. So, how do you group them? So, let me show that. So, when you have these measures, let's say these three measures, right? And if you want to group them uh what you can do is this. You can enter a display folder here basically. You can say batting and it will put it in a batting folder and then you just drag and drop. Okay? So, you drag it here, drag it here. Okay? So, this way, see? You have a nice batting folder. So, your goal by the way, this is an exercise for you. Folks, this is not hard, don't worry. You can do it. Your goal is to create all the measures. You know, I just showed you two three measures, but you have to create all these measures on your own and just put them in respective folder. Once you're done creating a measures, you have to also create some calculated columns. Calculated columns is just a jargon. Actually, it's nothing but similar to Excel formula. So, if you have Excel formula, for example, let's say if I go to this CSV file and I remove this filter, right? So, let me remove that filter. I want to know total runs from boundaries. Okay? So, let's say boundary runs. If I want to do that, I can use some formula in Excel, correct? So, whatever let's say if you have whatever number of fours into four plus whatever number sixes you have into six, that will be your boundary runs, correct? And if you drag and drop this formula like this, you will see the boundary runs. For example, this guy scored one four and one six, so that will make it 10 run. Similar to this Excel formula, we are going to create calculated column in our DAX. So, let's go to our file here. And and by the way, this this visual was just for your validation, so you can delete it. So, here in the batting summary, I will create my first calculated column, which is let's say boundary runs, right? So, boundary runs is this. So, how do you do that? So, you can go to this table view and you can say new column. Okay? And this new column will be this. This is the name. Okay? So, let's say boundary runs. And that boundary runs is equal to See that fact batting summary fours into four plus fact batting summary sixes into six. If you have a slight bit of Excel idea, this is pretty straightforward. So, you create a new column here. Once again, for validation, you can look at anything here. See like this one, for example. Uh this person This person hit Pat Cummins two fours, one six. So, two fours is two into four, eight and one six is eight and six is 14, so 14 runs. Pretty straightforward. So, create all these three calculated columns and once again, if you want to look at the final uh PBIX file, which I have given, again, check video description. You you are given all the assets and that final file would have See all those measures. So, anything which has this symbol is a calculated column. So, see? It has boundary runs. Okay? So, it has pretty much everything that you need. The visual here correctly represents how much attention dashboarding gets. And now we are going to start dashboarding for our project. When you work as a data analyst in any company, uh usually business managers will provide you some kind of rough mock-up with their understanding on how they want to see the dashboard. So, here they have given this image, which they can draw on note and pen, where they want to see different tabs for power hitters, anchors, fast bowlers. Remember, previously we covered different criteria for each of these categories. And when you select any category, let's say you select hitters or anchors, you will see the players in that category along with the statistics such as their runs, strike rate, batting average, etc. And on the right hand side, you want to have a criteria filter where based on our criteria, we can see a list of players here. And then at the bottom, you will have some kind of trends for various statistics and bottom right would be the scatter chart between strike rate and batting average. Now, they can provide this mock-up in a rough format like this or sometimes people use PowerPoint or some other tool and they will just draw their rough ideas. And as a data analyst, it is our responsibility to communicate back. You know, communication is a very important skill when it comes to data analyst career. So, we are going to provide you all these mock-ups. The name of this file is mock-up.txt, which you will find in the video description below once again when you download all those files, you will see this file. Now, I'm going to take this stage two.pbix file, which has all the decks measures created. You can also do the same. You can just get that file and I will start building our visual. So, here the first thing that we're building is the page for power hitters. And what I will do is I will go to dim player table and grab the name of the player and just drag and drop here. So, it shows me see all the names of every single possible player. And if you check our mock-ups, we want to have certain columns in this field such as let's say team for example, then the batting style. Okay. Then the innings batted. So, you can go to key measures and you can say, "Okay, innings batted." And then total amount of runs that these players made, total balls that they faced, strike rate, their batting average and so on. And once you have all these columns, you want to look at your criteria for openers, your power hitters. And here it says, "See, their batting average should be greater than 30, strike rate should be greater than 140." So, now you will use this filter tab to filter those players because this list is showing all the players, right? You want your batting average to be greater than 30. So, I can say batting average should be greater than 30. Apply filter and it will see filter all those players. Then you want a strike rate to be greater than 140. So, I will go to strike rate and say is strike rate is greater than 140. So, strike rate greater than 140, innings batted greater than three and boundary percentage greater than 50. So, innings batted Uh where is it? Okay, innings batted should be greater than three. And then boundary percentage greater than 50. So, boundary percentage greater than 50. 50% is 0.5, right? So, that is greater than apply filter. And then the last one is batting position should be less than four. So, in whatever matches they played, their batting position should be in in the opening somewhere. So, it is less than four. And when you apply all these criteria, you see a nice list of players who can be your potential power hitter in your final 11 team. You can also do some, you know, visualization related changes. For example, total runs, I want to see them as a bar chart, like a horizontal bar chart. So, you can now go to visualization tab. You can say total runs, I want to do conditional formatting and I want to display the data bar charts. And the bar charts looks something like this. You can sort these columns as well, by the way. So, if you click on it, you can see the player with highest runs. We all know Joe Butler. I still remember their inning with India. Joe Butler and Alex Hales just killed it in the semi-finals. So, you see they have pretty good runs, strike rate and so on. Now, folks, building the whole dashboard is few hours of process and I don't want to waste your time just going over all of that. So, I'm just going to show you a stage three file. So, once again, whatever files you have downloaded, okay, video description, check it. You will find this file called stage three. And that has all the raw visuals created. So, you know, you will have a page for power hitters, for example. Then you will have a page for anchors, middle order. Okay, and how do you create this page? Well, when you go to here, this will be page one. So, you you say, "Okay, power hitters or openers." And then you create a new page and you create a new page for anchors or middle order, right? So, anchors, middle order. You just You just type double click and type in it. Anchors, middle order. And then you drag and drop and start building the visuals here. And when you build the visuals in the raw format, ultimately, they will look something like this. Okay? Now, if you are once again new to Power BI and you don't know the basics of this visuals, what you can do is you can go to codebasics.io and just take our Power BI course. It's It covers all of those concepts pretty much in detail. But for this video, I'm going to give you this stage three files so that you can check various properties of the visual in case you don't know how to build it. So, when you click on it, uh see here it will show you what kind of visual it is. So, this one is card visual. And if you want to look at the formatting, for example, then you can just go click here and you can look at various, you know, properties. So, we're going to assume that you have built all these raw visuals and now it is time to beautify them, you know, to make them look good and kind of connect different pieces. Once you beautify your dashboard, it's going to look something like this. Now, this is what we have built, but you can build it as per your own preference for colors and different visual behavior. We have provided you all the dashboarding tips here, which you can use to build our dashboard. See, I'm not going to spoon-feed you because when you go work in the industry, it becomes essential that you use your Googling skills to figure things out. If you're trying to change a color of some visual, okay, uh you can just Google it. Googling is an art that can be tremendously helpful and this is a unique opportunity for you to use that art to learn certain things on your own while we are providing you full assistance. So, read dashboarding tips and then try to make the entire dashboard look pretty good. In our Power BI course also, we have an entire chapter on designing an effective dashboard. Now, you will be glad to know that this particular dashboard is designed by one of our students. So, he took our Power BI course. His name is Ashish Babbaria. He also participates in our codebasics.io resume project challenges. So, if you go to the website resume project challenges, which are free for everyone to participate, he has won our first prize in two challenges. And if you click on this LinkedIn icon, you will see his post here where he builds beautiful, beautiful dashboards. Now, Ashish's background, if you check it, he's a trade agro specialist. So, he comes from a non-technical background. He learned Power BI. Look at his background. He's trade agro specialist. So, he doesn't have a formal training on data analytics, etc. He learned Power BI mainly from Code Basics channel and various other resources and by participating in resume age project challenges, we were able to spot his challenge and he's now working with us as a freelancer and he's helping us with all these projects. So, look at the the quality of and professionalism of this dashboard. He learned things on his own at a later part of his career and you can become like Ashish, too. Now, you can click on various player categories. So, in the power hitters, you want to have a few players from this particular list. And if you look at the the filter criteria, see, this is all the filter criteria that we had. Now, look at the filter criteria, for example, for fast bowlers, right? So, in fast bowlers, you want to have bowling economy to be less than seven and they should be taking wicket every 16 balls. So, if you go to our fast bowlers, control click on that. And if you click on this visual, you will see all the filters that we have. Okay? So, see, bowling strike rate is less than 16, dot ball is greater than 40% and so on. So, you see all those details here. And if you want to modify any criteria, it's super easy. You just go here, type in and you can play with different things. And in the next part of this session, what we're going to do is invite Tony Sharma, who is a subject matter expert and in charge of this project. He will help us decide the final 11. So, if you control click on it, see, we have our final 11 almost decided, but you can modify certain criteria, and different players. And if you look at this team, if you know cricket, just look at this team. It looks pretty solid, like unbeatable team. So, see Power BI data analyst can help you uh generate data-driven insights uh that can make a huge difference in the problem that you're trying to solve. We are going to provide you this final file also. So, if you're if you have question on some visual behavior, you can click on it and you can you know, check uh the different visuals that we have. You can look at format and their, you know, various properties that these elements are having. And I will quickly play a time-lapse view so that you you have some idea on how this visual was built. So, Nick, we are just few minutes away from saving the world. How does this dashboard look? Dashboard looks amazing, but can you show me the best 11? Not yet. We are getting there. Just give me few minutes. Let me explain how I have done this. So, in the previous session we had, I explained what are the parameters I'm using, right? And now you can see these parameters coming to action, coming come live. So, this is how I have created the parameters. You can see how I filtered the players for the openers. These are the power hitters. And you know, I also have like a solid graph of them, like to understand their consistency, to understand their playing trend and all of that. And I also have a scatter plot to show how their batting average fares against strike rate. So, you can see these are the players I've got here, correct? So, these players will be able to strike ball at around I have players who can strike ball at 160, 170, even close to 170. At the same time, give me an average of 35. You can see that. And Jos Buttler here gives me the highest average. But he is is good striker as well. He strikes at 140 plus, which is which fits our parameter. So, you can see that these players are there. So, out of these players I'm going to select Jos Buttler because you can see he's consistent all the matches. He has played all the matches pretty decently. And he's a good wicketkeeper as well. So, we need a wicketkeeper. So, he's going to be our wicketkeeper batter. And his partner, Alex Hales, is a good choice. He's a good choice of a second opener. But again, he's not consistent in all the matches. Also, I need a left-hand combination with a better strike rate. So, I'm going to choose Rilee Rossouw from South Africa. He's a better option for me. He has a, you know, he can strike ball like crazy. I can show you the combined uh you know, the performance of these both. I'm just selecting these both. So, if these two players play together Rilee Rossouw and Jos Buttler, if they play together they will give us 40 runs in average at a strike rate of 150 plus. So, if they two bat without losing a wicket so, we'll hit our target of 180. If they bat 120 balls, they will give us 180 runs. And they will stand at least for four overs on an average because the average balls faced, if you can see, it's 23.9. And these scores run at 60% in boundaries, which is so so perfect to what we need. You can see the consistency is is pretty much there. It's it's dropping here and there, but but they are, you know, they together as a package will give us what we need. I love this feature that you can select two players and see their statistics on average basis. Because this way, let's say if these two uh players are playing and if I have questions on their partnership statistics, I can get the view of those numbers easily here. Yeah. It is not exactly like a partnership, but it will give you their combined performance. Got it. I got the point. Yes. And so, I'm also going to select Alex Hales just as my reserve opener. So, I'm going to select three players for this position potentially. But the one the two I'm going to play is Jos Buttler and Rilee Rossouw. All right, let's move to the anchors where we'll be select three players here. Again, you can see the filters I applied on this data, batting average, strike rate, innings batted, exactly like we discussed, right? Here, the interesting part is um we have Virat Kohli on the top with most runs and then follow Suryakumar Yadav. So, let's check into this chart, the scatter plot. Virat Kohli is clearly the winner because he gives us a lot of runs. He's a run machine. We definitely need to pick him. And the second player could be I couldn't think anything better than Suryakumar Yadav. Because you can see his average is 60. He could give us 60 runs on an average and he's striking at 190. This is the best we have in the team. So, even our openers did not score runs at a good strike rate, this guy can come and propel the score. So, we need him in the team for sure. And these two players are solid. They have a good partnership. It's a great idea to play them together. So, for my fifth position I'm going to go with the three options I have is like Lorcan Tucker, Glenn Phillips, and Daryl Mitchell. So, I'm based on the statistics, based on the averages it's easy for me to choose Glenn Phillips. So, even though he has a strike rate of 160 which is which is really high for that position he scores at an average of 40. So, that's the reason I go for this. He's clearly my number five. Okay. Right? And uh let's move on to the next one. So, this is going to get slightly tricky because I have many players in this position uh you know, whom we can pick. So, like I said, I'm looking for a batting all-rounder here. And that batting all-rounder could be uh you know, a batsman who can score runs at a very high strike rate, at the same time anchor the innings. Or you know, that this person can be a fast bowler or a spinner as well. In order to, you know, justify my selection, let me come back to this place again. But let and I will go to my I'll go to select my specialist fast bowlers first. So, that it becomes very easy for me whether I need a fast bowler in the position or whether whether at all I need a bowler at all in the position. If I need a good bowler or a good batsman in the position. So, I need I will select my fast bowlers first. So, here my selection is super super super easy. I'm I'm going to select this guy, Sam Curran. You can see his economy is 6.53. And his bowling average is 11.38, which means he gets a wicket for every 11 runs that he provides. And his bowling strike rate is also staggering. He gets a wicket for every 10 and a half balls, which means if he balls the full four overs, we are definitely getting two wickets. And look at this guy. He has got 11 wickets at an economy crazy economy of 5.37. He gives less than six runs in over. We definitely need him on our team. And he's a fast bowler. And he picks wickets in less than 10 balls. So, these two are definitely, you know, our team. And of course, Shaheen Shah Afridi. How can we ignore him? The kind of player he is, he's a left-arm fast bowler. And he can really rattle the batsman. He's one of my favorite. Yeah. So, we are picking these three. Tim Southee is good, but if I have to pick three, I'm just going to go with these three. So, these are the parameters I've applied here. You can see that uh you know, innings bowled, bowling strike rate, bowling average, exactly like we discussed in the parameter session. So, let's see their combined performance together just to get an idea. Sam Curran Anrich Nortje Shaheen Shah Afridi. You can see these guys will pick a wicket for every 11 runs they give, which means if these three bowl all the 20 overs, they will get the full team all out for 110 runs, 113 runs. And they will get a wicket every 11 balls. If they bowl all the 20 overs the team is all out. And they will they imagine they bowling first three or four overs, they will let's say if these players they they bowl first six overs, how many wickets they will take? If they bowl the first six overs, they will pick a wicket for every 12 balls. Six overs is 36. So, they will pick at least three wickets. That's that's on average. That would be awesome. If you pick three wickets in the power play, that's going to be amazing. They will they will definitely scalp the top three. They will open the middle order for us. And look at the economy. The combined economy is six, which means if they bowl the first six overs, they will be just giving 36 runs. And the dot ball they produce is close to 50%, which means if they bowl all the 20 overs, the batsman is able to score only the half of the overs, like only the 10 overs. The rest of the 10 overs, they are scoring nothing. It's a dot ball. These three are the major strength to our team. All right. So, let me come to the all-rounders now before I go to the finisher role. So, we have three solid fast bowlers. So, in my all-rounder, I'm going to have the spin element. But, at the same time, I want these two players to bat as well. At the highest strike rate. So, that's the reason as I explained earlier, I've kept the strike rate parameter as 140. If I don't consider the strike rate, let's say if I get the strike rate as 100, I will get more players. Like even I get Ben Stokes here. But, I'm I'm considering strike rate 140. Oh, got it. I was expecting to see Ben Stokes. Now, I know why he's not showing up. Yeah. Yeah, I I will explain why I have Ben Stokes is not here in the in the other other page as well because I want him at that I wanted him at that role earlier. So, you can see from this graph, Rashid Khan is really good in terms of uh you know, the bowling strike rate. I mean, I can't say it's really good because bowling strike rate should be lesser, right? It's It's not good. And uh you have Sikandar Raza who has the economy. We kind of finding people in the zone. This This zone is the best zone. Who has the lesser economy and lesser bowling strike rate. So, Shadab Khan is really the winner. He definitely deserves a spot because you can see his performance. He's quite consistent as well. You know, his bowling average is good. Whenever he got a chance to bat, he has batted well as well. This is his bowling and batting performance. It's It's of high consistency. But, Shadab Khan can I play him at number seven? I'm not sure because if my number six did not bat well, I want someone more capable to bat at number seven. So, I would play Shadab Khan at number eight. But, then at number seven, I would play Sikandar Raza. This is the person I'm going to play at number seven because look at his strike rate. It's 147 and he also has a very high average batting average of 27 runs. For a number seven, he normally played at number five or number four. But, for number seven, if you have this kind of an average, it's great with this strike rate. So, that's that's going to be my number seven and number eight. I've got my 9, 10, and 11 as well. So, I just need to select my number six. So, again, choosing this position is slightly difficult. I would show you why uh you know, let me take out the filter from the striker, right? You would see that I also have Ben Stokes here. So, Ben Stokes could not make this list because his strike rate was too low in the series. His strike rate was just 105. If the batsman is scoring just run a ball, we cannot have them in the number six because this position might require to hit like crazy. So, that's the reason Ben Stokes might not find his place. So, I'm going to say it's greater than 130. And again, if I have to say that um whom I'm going to pick from here. So, we have three fast bowlers, two spinners. Glenn Maxwell looks like a good option, but he's a spinner again. I'm not sure whether my sixth bowling option should be a spinner, but his bowling average is good. Uh you can see his bowling average is economy six. Uh he has is bat is bowling strike rate is 6.33, which means he has got a wicket almost every six balls. So, he's one of my option. My other option is Marcus Stoinis. I'm going to play with this guy. His bowling average is not that good. His economy is not that good, but he's a good striker of the ball. And he is someone I can also trust to anchor the game. So, I could I could go for Marcus Stoinis if I'm if I want a better batsman with high striking rate or if I want someone more balanced, I could go with Hardik Pandya. So, this guy uh will give you a good good batting average, but his strike rate is also not not that great and his consistency is also not that good in this World Cup. But, his bowling is good. If I really need the sixth bowling option, I would go for Hardik Pandya. But, the kind of bowlers that I have, Shaheen Shah Afridi, uh Sam Curran, Anrich Nortje, and all those players, I don't think I would need Hardik Pandya in my team. Then, I will go for Marcus Stoinis. So, I will make my decision easier by showing the final 11, right? So, if I go to the final 11, I've already picked the top 11 like I said, right? So, you can see this is our batting 11. Jos Buttler, Rilee Rossouw will be opening the game. Virat Kohli followed by Suryakumar Yadav, Glenn Phillips. And in this number six, I've selected here Hardik Pandya. But, I want to go with let's say uh Marcus Stoinis. You can see the combined performance of the team. Batting average is 37.76. The team on average scores 37 runs. Strike rate is 150. You know, all the facts it's given here. But, if I choose Marcus Stoinis, since my sixth batting position should increase the batting strength. I'm focusing more on the batting strength now because our bowling is already great. I'm selecting Marcus Stoinis and removing Hardik Pandya. You would see my batting average improved from 37 to 39.6 and my strike rate improved from 151 to 154.4. So, this is the reason I would go for Marcus Stoinis. But, I can also go for Maxwell, right? You can see that the you know, the performance of Maxwell and Marcus Stoinis are very much comparable. They have a They have a very similar strike rate, almost very close. And uh Maxwell has bowled well. He has picked up wickets well. But, he is a spin bowler. He's a right-arm off break bowler. So, since I would need my sixth bowling option as someone who can you know, who can bowl pace, I'm going for Marcus Stoinis. But, on a given day, we will still have options. You can still choose Glenn Maxwell on a given day depending upon the pitch. If the sixth bowling option can be a spinner, then my second option is Maxwell. Uh if I need someone more in the bowling side, if if some of our fast bowlers get injured, then I would choose Hardik Pandya for number six. So, I would keep all these three options open. But, right now my team in the final 11, I would have uh Marcus Stoinis. And uh in the opening, I would go for Rilee Rossouw. But, if you want Alex Hales, he's also available for selection. Got it. Just by looking at this list, I'm feeling super excited. Anyone who is following cricket, if they look at this list of players, they will be like this team is unbeatable. This is the best team that we got on our planet and I'm sure we would win this game and save our planet. So, Tony, this team is great, but I know that Mitchell Starc is a pretty good fast bowler from Australia. Can you tell me why he's not in the final 11? Sure. I think he must have not met our standards. Let me check. Um pretty sure he's there, but he didn't meet our standards. So, I think um if I recall it correctly, maybe his bowling strike rate is definitely not under 16. So, let me take this out. And uh maybe he didn't bowl dot balls like we want. And his bowling average might not be less than 20. I should remove that as well. And his economy is definitely not less than seven. Let me take that out. And let me see if we can have him in the M. Don't think we have him yet. So, maybe he didn't even uh play the four innings. Yeah, you could find him here now. Mitchell Starc. You can see his economy is 8.5, which is more than uh you know, seven. His bowling average is 34. His bowling strike rate is 24. So, that's the reason why he's not there. And he has also played ball just three innings. We wanted at least four innings. Yeah, that's the reason. All right, it's clear now. Okay, here's your final 11. Nick, go get us the cup and save the planet. The future of this planet is focused on these 11 players. They are traveling to the wilderness out in the dark to bring us light. And may we all citizens of Data Hope and pray our analysis and insights shall work, defeat these Portents, and bring us glory. Three weeks later. You defeat us in cricket, you get Earth. If you lose, join me as an intern. Join me as an intern. Join me. Now comes the most interesting part of this entire project series, which is an exercise. And by working on this exercise, you will be able to win an exciting prize, which is 20% scholarship on one of our premium courses on codebasics.io. For exercise, what you need to do is number one, in the tooltip for players, you are seeing, let's say if it is Suryakumar Yadav, you are seeing India versus Pakistan, India versus whatever team, you all know he's from India. So, you need to remove that IND and just have versus Pakistan, versus Sri Lanka. So, you need to update that tooltip. That is exercise number one. Second one is you have to update the visual look and feel of the entire dashboard. So, you can change colors, the placement, you know, different visual aspects of the entire dashboard. So, come up with your your own design and colors. Number three is providing some more insights. So, whatever we have covered in dashboard, in addition to that, try to add more insights. And then, once you have that dashboard created, you can write a nice LinkedIn post and tag me Hemant and add following particular hashtags so that you we know that you have submitted this. We are going to provide some sample LinkedIn post in the description below. These are for resume projects, but basically you can write nice post, upload your project on Novi Pro or maybe just short a simple video and make a LinkedIn post on it. I wish you all the best and I hope you learned a lot of different things in this project. This is going to be an excellent project for your resume and make sure you're writing LinkedIn post that way you know, you can draw attention from potential recruiters. If you like this video, give it a thumbs up and please share it with your friends. Any question or comments, post in the comment box below or we have a Discord server for Code Basics. Go there and you can post your question there as well. Thank you.