Reading Excel files (read_excel) in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this code reading an Excel file?

Consider an Excel file named data.xlsx with a sheet named Sheet1 containing a column Age with values [25, 30, 35]. What will be the output of the following code?

Data Analysis Python

import pandas as pd

df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df['Age'].tolist())

A ['25', '30', '35']

B [25, 30, 35, None]

C [25, 30, 35]

D KeyError: 'Age'

💡 Hint

Think about how pandas reads numeric columns from Excel.

The code reads the Excel sheet correctly and extracts the 'Age' column as integers, so the list is [25, 30, 35].

❓ data_output

intermediate

What data is loaded with this read_excel call?

Given an Excel file sales.xlsx with sheets Q1 and Q2, each having columns Product and Sales. What will be the shape of the DataFrame loaded by this code?

Data Analysis Python

import pandas as pd

df = pd.read_excel('sales.xlsx', sheet_name='Q2')
print(df.shape)

A (10, 2)

B (2, 10)

C (0, 0)

D FileNotFoundError

💡 Hint

Assume Q2 sheet has 10 rows and 2 columns.

The DataFrame shape is (10, 2) because Q2 sheet has 10 rows and 2 columns.

🔧 Debug

advanced

What error does this code raise when reading an Excel file?

What error will this code produce if the file missing.xlsx does not exist in the folder?

Data Analysis Python

import pandas as pd

df = pd.read_excel('missing.xlsx')

A FileNotFoundError

B ValueError

C KeyError

D TypeError

💡 Hint

Think about what happens when a file is missing.

If the file is missing, Python raises FileNotFoundError.

🧠 Conceptual

advanced

Which option correctly reads only columns 'Name' and 'Age' from an Excel file?

You want to read only the columns 'Name' and 'Age' from people.xlsx. Which code does this correctly?

A pd.read_excel('people.xlsx', cols=['Name', 'Age'])

B pd.read_excel('people.xlsx', columns=['Name', 'Age'])

C pd.read_excel('people.xlsx', select=['Name', 'Age'])

D pd.read_excel('people.xlsx', usecols=['Name', 'Age'])

💡 Hint

Check the correct parameter name for selecting columns.

The parameter to select columns is 'usecols'. Other options are invalid.

🚀 Application

expert

What is the output of this code reading multiple sheets into a dictionary?

Given an Excel file multi_sheets.xlsx with sheets Jan and Feb, each with 3 rows and 2 columns, what will be the output of this code?

Data Analysis Python

import pandas as pd

sheets = pd.read_excel('multi_sheets.xlsx', sheet_name=['Jan', 'Feb'])
print(len(sheets), sheets['Jan'].shape, sheets['Feb'].shape)

A 2 (2, 3) (2, 3)

B 2 (3, 2) (3, 2)

C 1 (3, 2) (3, 2)

D KeyError: 'Jan'

💡 Hint

Reading multiple sheets returns a dictionary with sheet names as keys.

The variable sheets is a dict with 2 keys. Each DataFrame has shape (3, 2).

Practice

1. What does the pandas.read_excel() function do in Python?

easy

A. It reads data from an Excel file into a DataFrame.

B. It writes data from a DataFrame to an Excel file.

C. It converts a DataFrame to a CSV file.

D. It creates a new Excel file from scratch.

Solution

1. Step 1: Understand the purpose of read_excel

   The function pandas.read_excel() is designed to load data from Excel files into Python as a DataFrame.

2. Step 2: Differentiate from other file operations

   Writing data to Excel or converting to CSV are done by other functions like to_excel() or to_csv(), not read_excel().

3. Final Answer:

   It reads data from an Excel file into a DataFrame. -> Option A

4. Quick Check:

   read_excel loads Excel data [OK]

Hint: Remember: read_excel loads data, to_excel saves data [OK]

Common Mistakes:

• Confusing read_excel with to_excel
• Thinking it writes data instead of reading
• Mixing up CSV and Excel functions

2. Which of the following is the correct syntax to read the sheet named 'Sales' from an Excel file 'data.xlsx' using pandas?

easy

A. pd.read_excel('data.xlsx', sheet='Sales')

B. pd.read_excel('data.xlsx', sheet_name='Sales')

C. pd.read_excel('data.xlsx', sheetname='Sales')

D. pd.read_excel('data.xlsx', sheetname='sales')

Solution

1. Step 1: Identify the correct parameter name

   The correct parameter to specify the sheet name in read_excel is sheet_name, not sheet or sheetname.

2. Step 2: Check case sensitivity and spelling

   Parameter names are case-sensitive and must be spelled exactly as documented. Also, sheet names are case-sensitive, so 'Sales' is correct if that is the sheet name.

3. Final Answer:

   pd.read_excel('data.xlsx', sheet_name='Sales') -> Option B

4. Quick Check:

   Use sheet_name to select sheet [OK]

Hint: Use exact parameter name 'sheet_name' to select sheets [OK]

Common Mistakes:

• Using 'sheet' instead of 'sheet_name'
• Misspelling 'sheet_name' as 'sheetname'
• Ignoring case sensitivity in sheet names

3. What will be the output of this code snippet?

import pandas as pd
df = pd.read_excel('data.xlsx', usecols='A:C')
print(df.columns.tolist())

Assuming columns A, B, C in the Excel file are named 'Name', 'Age', 'City'.

medium

A. ['A', 'B', 'C']

B. Error: Invalid usecols parameter

C. ['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 2']

D. ['Name', 'Age', 'City']

Solution

1. Step 1: Understand usecols='A:C'

   The parameter usecols='A:C' selects columns A to C from the Excel file, which correspond to the columns named 'Name', 'Age', and 'City'.

2. Step 2: Check what df.columns contains

   After reading, df.columns contains the actual column names from the Excel file, so printing df.columns.tolist() outputs ['Name', 'Age', 'City'].

3. Final Answer:

   ['Name', 'Age', 'City'] -> Option D

4. Quick Check:

   usecols selects columns by letter, output shows column names [OK]

Hint: usecols='A:C' picks columns A to C by letter [OK]

Common Mistakes:

• Expecting column letters instead of names in output
• Confusing usecols syntax causing errors
• Assuming default numeric column indexing

4. You run this code but get an error:

import pandas as pd
df = pd.read_excel('data.xlsx', sheet_name=1, usecols=[0, 2, 5])

What is the most likely cause of the error?

medium

A. The file path 'data.xlsx' is incorrect or file missing.

B. The usecols list contains an invalid column index.

C. The sheet index 1 does not exist in the Excel file.

D. The parameter 'sheet_name' must be a string, not an integer.

Solution

1. Step 1: Check sheet_name parameter

   Using sheet_name=1 is valid to select the second sheet by index (0-based), so this is not an error cause.

2. Step 2: Validate usecols parameter

   The list [0, 2, 5] is valid to select columns by zero-based index, so this is not an error cause.

3. Step 3: Consider file path and existence

   If the file 'data.xlsx' does not exist or path is wrong, pandas raises a FileNotFoundError, which is a common cause of errors when reading Excel files.

4. Final Answer:

   The file path 'data.xlsx' is incorrect or file missing. -> Option A

5. Quick Check:

   FileNotFoundError usually means wrong file path [OK]

Hint: Check file path first if read_excel errors [OK]

Common Mistakes:

• Assuming sheet_name must be string only
• Thinking usecols list indexes are invalid
• Ignoring file existence or path errors

5. You want to read only the columns 'Date', 'Sales', and 'Profit' from the sheet named '2023' in an Excel file 'report.xlsx'. Which code snippet correctly does this?

hard

A. pd.read_excel('report.xlsx', sheet_name='2023', usecols='Date,Sales,Profit')

B. pd.read_excel('report.xlsx', sheet='2023', columns=['Date', 'Sales', 'Profit'])

C. pd.read_excel('report.xlsx', sheet_name='2023', usecols=['Date', 'Sales', 'Profit'])

D. pd.read_excel('report.xlsx', sheet_name='2023', usecols=[0,1,2])

Solution

1. Step 1: Use correct parameter names

   The parameter to select the sheet is sheet_name, and to select columns by name is usecols with a list of column names.

2. Step 2: Check option correctness

   pd.read_excel('report.xlsx', sheet_name='2023', usecols=['Date', 'Sales', 'Profit']) uses sheet_name='2023' and usecols=['Date', 'Sales', 'Profit'], which is the correct syntax to read only those columns from the specified sheet.

3. Step 3: Identify errors in other options

   pd.read_excel('report.xlsx', sheet='2023', columns=['Date', 'Sales', 'Profit']) uses wrong parameter names sheet and columns. pd.read_excel('report.xlsx', sheet_name='2023', usecols='Date,Sales,Profit') uses a string for usecols which is invalid for column names. pd.read_excel('report.xlsx', sheet_name='2023', usecols=[0,1,2]) selects columns by index, not by name.

4. Final Answer:

   pd.read_excel('report.xlsx', sheet_name='2023', usecols=['Date', 'Sales', 'Profit']) -> Option C

5. Quick Check:

   Use sheet_name and list in usecols for named columns [OK]

Hint: Use list of column names in usecols to select columns [OK]

Common Mistakes:

• Using wrong parameter names like 'sheet' or 'columns'
• Passing column names as a comma string instead of list
• Selecting columns by index when names are needed