Writing to Excel with to_excel in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output file content after running this code?

Consider the following Python code using pandas. What will be the content of the Excel file output.xlsx after running this?

Pandas

import pandas as pd

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

A Excel file with columns 'Name' and 'Age' and two rows: Alice 25, Bob 30, including row numbers 0 and 1

B Excel file with columns 'Name' and 'Age' and two rows: Alice 25, Bob 30, without row numbers

C Excel file with columns 'Name' and 'Age' but empty rows

D No Excel file is created due to error

💡 Hint

Check the index parameter in to_excel.

Setting index=False means the row numbers are not saved in the Excel file. So only the data columns and rows appear.

❓ data_output

intermediate

How many sheets are created in the Excel file?

Given this code, how many sheets will the Excel file contain?

Pandas

import pandas as pd

with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    pd.DataFrame({'A': [1, 2]}).to_excel(writer, sheet_name='Sheet1')
    pd.DataFrame({'B': [3, 4]}).to_excel(writer, sheet_name='Sheet2')

A 1

B 3

C 0

D 2

💡 Hint

Each to_excel call with a different sheet_name adds a sheet.

Two to_excel calls with different sheet names create two sheets in the Excel file.

🔧 Debug

advanced

What error does this code raise?

What error will this code raise when trying to write a DataFrame to Excel?

Pandas

import pandas as pd

df = pd.DataFrame({'X': [1, 2]})
df.to_excel('file.xlsx', sheet_name='Data', engine='nonexistent_engine')

A ValueError: Unknown engine: nonexistent_engine

B FileNotFoundError: file.xlsx not found

C TypeError: to_excel() got an unexpected keyword argument 'engine'

D No error, file is created successfully

💡 Hint

Check if the engine name is valid for pandas Excel writing.

The engine parameter must be a valid Excel writer engine. 'nonexistent_engine' is invalid, so pandas raises a ValueError.

🚀 Application

advanced

Which option correctly appends a DataFrame to an existing Excel sheet?

You want to add new rows to an existing Excel sheet without deleting the old data. Which code snippet achieves this?

A Use <code>mode='a'</code> in <code>ExcelWriter</code> and <code>if_sheet_exists='overlay'</code> to append rows.

B Use <code>mode='a'</code> and <code>if_sheet_exists='replace'</code> to append rows.

C Use <code>mode='a'</code> and <code>if_sheet_exists='overlay'</code> to replace the sheet completely.

D Use <code>mode='w'</code> in <code>ExcelWriter</code> to overwrite the file and add new rows.

💡 Hint

Appending means adding data without deleting existing content.

Using mode='a' opens the file in append mode, and if_sheet_exists='overlay' allows adding rows without deleting existing ones.

🧠 Conceptual

expert

What happens if you write a DataFrame with a MultiIndex to Excel without specifying index=False?

Consider a DataFrame with a MultiIndex. What will the Excel output look like if you call to_excel without index=False?

A The MultiIndex is ignored and only data columns are written.

B The MultiIndex is flattened automatically and written as a single column.

C The MultiIndex levels are written as multiple columns on the left, showing the index hierarchy.

D The code raises a TypeError because MultiIndex is not supported.

💡 Hint

Think about how pandas represents MultiIndex in Excel output.

By default, pandas writes each level of the MultiIndex as separate columns on the left side in Excel, preserving the index structure.

Practice

1. What does the to_excel function in pandas do?

easy

A. Saves a DataFrame to an Excel file

B. Reads data from an Excel file into a DataFrame

C. Deletes an Excel file from disk

D. Converts Excel formulas into Python code

Solution

1. Step 1: Understand the purpose of to_excel

   The to_excel function is used to save or export a pandas DataFrame into an Excel file format.

2. Step 2: Differentiate from other Excel functions

   Functions like read_excel are for reading Excel files, not saving. Deleting files or converting formulas are unrelated.

3. Final Answer:

   Saves a DataFrame to an Excel file -> Option A

4. Quick Check:

   to_excel = Save DataFrame [OK]

Hint: Remember: 'to' means saving out, 'read' means loading in [OK]

Common Mistakes:

• Confusing to_excel with read_excel
• Thinking it deletes files
• Assuming it converts formulas

2. Which of the following is the correct syntax to save a DataFrame df to an Excel file named data.xlsx?

easy

A. df.to_excel('data.xlsx')

B. df.save_excel('data.xlsx')

C. to_excel(df, 'data.xlsx')

D. df.write_excel('data.xlsx')

Solution

1. Step 1: Recall the method call format

   In pandas, saving a DataFrame uses the method to_excel called on the DataFrame object.

2. Step 2: Check the syntax correctness

   The correct syntax is df.to_excel('filename.xlsx'). Other options use incorrect method names or function calls.

3. Final Answer:

   df.to_excel('data.xlsx') -> Option A

4. Quick Check:

   Method on DataFrame = to_excel [OK]

Hint: Use df.to_excel('filename.xlsx') to save [OK]

Common Mistakes:

• Using non-existent methods like save_excel
• Calling to_excel as a standalone function
• Using wrong method names like write_excel

3. Given the code below, what will be the content of the Excel file output.xlsx?

import pandas as pd
df = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df.to_excel('output.xlsx', index=False, sheet_name='Sheet1')

medium

A. Excel file with columns A and B, including row numbers, default sheet name

B. Excel file with columns A and B, no row numbers, sheet named 'Sheet1'

C. Excel file with only column A saved

D. Excel file with columns A and B, but sheet name is 'Sheet2'

Solution

1. Step 1: Analyze parameters in to_excel

   The argument index=False means row numbers are not saved. sheet_name='Sheet1' sets the sheet name explicitly.

2. Step 2: Understand DataFrame content saved

   All columns 'A' and 'B' are saved because no column filtering is done.

3. Final Answer:

   Excel file with columns A and B, no row numbers, sheet named 'Sheet1' -> Option B

4. Quick Check:

   index=False removes row numbers [OK]

Hint: index=False means no row numbers saved [OK]

Common Mistakes:

• Assuming row numbers are saved by default
• Ignoring the sheet_name parameter
• Thinking only one column is saved

4. What is wrong with this code snippet?

import pandas as pd
df = pd.DataFrame({'X': [10, 20]})
df.to_excel('file.xlsx', sheet='Data')

medium

A. File extension should be '.xls' not '.xlsx'

B. DataFrame is empty and cannot be saved

C. Missing import for Excel writer

D. The parameter name 'sheet' is incorrect; it should be 'sheet_name'

Solution

1. Step 1: Check parameter names for to_excel

   The correct parameter to name the sheet is sheet_name, not sheet.

2. Step 2: Validate other parts of the code

   The DataFrame is valid and not empty. The file extension '.xlsx' is correct. No extra import is needed for basic usage.

3. Final Answer:

   The parameter name 'sheet' is incorrect; it should be 'sheet_name' -> Option D

4. Quick Check:

   Use sheet_name to name sheets [OK]

Hint: Use exact parameter names like sheet_name [OK]

Common Mistakes:

• Using wrong parameter names like 'sheet'
• Thinking file extension must be '.xls'
• Assuming extra imports are needed

5. You want to save two DataFrames df1 and df2 into the same Excel file report.xlsx, each on a different sheet named 'Sales' and 'Expenses'. Which code correctly does this?

hard

A. df1.to_excel('report.xlsx')
df2.to_excel('report.xlsx')

B. df1.to_excel('report.xlsx', sheet_name='Sales')
df2.to_excel('report.xlsx', sheet_name='Expenses')

C. with pd.ExcelWriter('report.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sales')
df2.to_excel(writer, sheet_name='Expenses')

D. pd.to_excel([df1, df2], 'report.xlsx', sheets=['Sales', 'Expenses'])

Solution

1. Step 1: Understand saving multiple sheets

   To save multiple DataFrames in one Excel file with different sheets, use pd.ExcelWriter as a context manager.

2. Step 2: Check code correctness

   with pd.ExcelWriter('report.xlsx') as writer: df1.to_excel(writer, sheet_name='Sales') df2.to_excel(writer, sheet_name='Expenses') uses ExcelWriter and calls to_excel with the writer and sheet names correctly. Directly calling to_excel on the same filename multiple times overwrites the file, and pd.to_excel([df1, df2], ...) is not a valid function.

3. Final Answer:

   with pd.ExcelWriter('report.xlsx') as writer: df1.to_excel(writer, sheet_name='Sales') df2.to_excel(writer, sheet_name='Expenses') -> Option C

4. Quick Check:

   Use ExcelWriter for multiple sheets [OK]

Hint: Use ExcelWriter to save multiple sheets [OK]

Common Mistakes:

• Saving multiple DataFrames separately overwrites the file
• Trying to pass multiple DataFrames directly to to_excel
• Using non-existent functions like pd.to_excel with lists