Exporting to Excel in Data Analysis Python - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this code when exporting a DataFrame to Excel?

Consider the following Python code that exports a DataFrame to an Excel file. What will be the content of the Excel file sheet named 'Data'?

Data Analysis Python

import pandas as pd

df = pd.DataFrame({'Name': ['Alice', 'Bob'], 'Age': [25, 30]})
df.to_excel('output.xlsx', sheet_name='Data', index=False)

A An Excel file with a sheet named 'Data' containing columns 'Name' and 'Age' with two rows of data, without row indices.

B An Excel file with a sheet named 'Sheet1' containing the data including row indices.

C An Excel file with a sheet named 'Data' but the data will include row indices as a separate column.

D The code will raise a FileNotFoundError because the file path is not specified.

💡 Hint

Check the parameters used in the to_excel function, especially sheet_name and index.

The code exports the DataFrame to an Excel file named 'output.xlsx' with the sheet named 'Data'. The parameter index=False means row indices are not saved as a separate column.

❓ data_output

intermediate

How many sheets will the Excel file contain after running this code?

This code exports two DataFrames to the same Excel file using ExcelWriter. How many sheets will the file have?

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2]})
df2 = pd.DataFrame({'B': [3, 4]})

with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    df1.to_excel(writer, sheet_name='First')
    df2.to_excel(writer, sheet_name='Second')

A 1

B 0

C Raises an error because ExcelWriter is not closed explicitly.

D 2

💡 Hint

Each to_excel call with a different sheet_name adds a new sheet.

Using ExcelWriter with context manager writes both DataFrames to separate sheets named 'First' and 'Second', resulting in 2 sheets.

🔧 Debug

advanced

What error does this code raise when exporting to Excel?

Identify the error raised by this code snippet:

Data Analysis Python

import pandas as pd

df = pd.DataFrame({'X': [1, 2]})
df.to_excel('test.xlsx', sheet_name='Sheet1', index='False')

A TypeError: to_excel() got an unexpected keyword argument 'index'

B ValueError: index parameter must be a boolean, not a string

C No error, file is saved with row indices included

D FileNotFoundError: No such file or directory

💡 Hint

Check the type of the index parameter value.

The index parameter expects a boolean value True or False, but a string 'False' is passed, causing a ValueError.

🚀 Application

advanced

Which option correctly exports a DataFrame with a custom header and without the default index?

You want to export this DataFrame to Excel with column headers renamed to ['ID', 'Score'] and no row indices saved. Which code does this correctly?

Data Analysis Python

import pandas as pd
df = pd.DataFrame({'A': [101, 102], 'B': [88, 92]})

A df.rename(columns={'A': 'ID', 'B': 'Score'}).to_excel('custom.xlsx', index=False)

B df.to_excel('custom.xlsx', columns=['ID', 'Score'], index=False)

C df.to_excel('custom.xlsx', header=['ID', 'Score'], index=False)

D df.to_excel('custom.xlsx', header=True, index=False)

💡 Hint

To rename headers, you must rename columns before exporting.

The header parameter does not rename columns; renaming must be done on the DataFrame before export. Option A renames columns correctly and exports without index.

🧠 Conceptual

expert

Why might you use the ExcelWriter engine 'openpyxl' explicitly when exporting to Excel?

Select the best reason to specify the engine='openpyxl' parameter in pd.ExcelWriter when exporting DataFrames to Excel files.

A To speed up the export process by using a compiled engine instead of Python code.

B Because openpyxl is the only engine that can write CSV files.

C To enable writing to .xlsx files with support for advanced Excel features like cell styling and multiple sheets.

D To automatically convert all numeric columns to text format in Excel.

💡 Hint

Think about Excel file formats and features supported by different engines.

The openpyxl engine supports writing .xlsx files and advanced features like styling and multiple sheets. It is commonly used for Excel exports.