HomeChallengesIs RCB enjoying home advantage in IPL?
learnbook poster
Is RCB enjoying home advantage in IPL?Last updated at Mon Nov 01 2021Skills
data-wrangling
Tools
excel

Learning Goal(s)


By the end of this learnbook, you should be able to demonstrate following skills :

  • Connecting two sheets via lookup and fetch additional columns
  • Writing conditional statements in Excel
  • Pivoting data in Excel

Problem Statement


SportsWiz is a sports tech startup which provides data driven analysis and strategic recommendations to IPL franchises to help them answer a number of questions related to team's performance, composition and strategy

You are a Data Analyst hired by SportsWiz and you'll be working with Royal Challengers Bangalore which is struggling at IPL for past few seasons

  • The batting coach of team RCB wants to understand whether their team is enjoying any home advantage in their matches or not? He has asked you the below question :
    • Is the number of matches won by RCB is comparatively higher on home ground(Bengaluru) or away?
  • He is looking for a simple table as shown below :

  • Perform the following tasks :

    • In dataset ipl_matches.xlsx, For each of the columns Team 1,Team 2, toss_winner and Winner, fetch the team abbreviations from the ipl_summary.xlsx and save the new columns as Team_1_Code,Team_2_Code,toss_winner_code and Winner_code
    • Fetch the Home_City column from ipl_teams.xlsx into the dataset ipl_matches.xlsx
    • Create a new column called ground_type. This should have values Home and Away depending upon if the Winner of the match has won on its home ground or not
    • Now, prepare the table as expected by your batting coach
  • Done? Not fully yet!

    • Your batting coach feels showing just the win numbers is not sufficient to assess whether the team is enjoying a home advantage or not. He would like to see percentage of wins under Home and Away. Please note, for this part you should use the dataset : ipl_matches_long.xlsx

      Psst...Think a while how you'll calculate the Win% here

    • Below is the modified table which your batting coach is expecting :

Before you start


Ensure you have the following items ready :

  • Access to Microsoft Excel 2016 or above
  • Access to any screen recording software (Zoom, Bandicam etc.)
  • Access to datasets required for this project. Download here
  • Access to the response sheet where you will be logging the outcomes of the project. Open here

Skills to brush up


Check the following references to quickly brush up the skills needed to solve the above stated problem :

  • Pivoting in Excel : Here
  • Show value in Pivot : Here
  • Conditional statements in Excel : Here

What other questions you would ask the data to better address the problem statement at hand?

Wish to discuss the solution to this problem and solve more interesting problems?

Join us on Slack today!


Created with 💙 by
author avatar
Amit Choudhary
Data Practitioner,Mentorskool
mentorskool logo
Mentee Today, Mentor Tomorrow
No 206, A Block, Sonesta Silver Oak,Varthur, Bangalore 560066,Karnataka
Copyright - 2022 © Mentorskool - All rights reserved.