Exporting results to multiple formats in Pandas - Mini Project: Build & Apply

Exporting results to multiple formats

📖 Scenario: You have collected sales data for a small store. You want to save this data in different file formats so you can share it easily with others who may use different software.

🎯 Goal: Create a small sales data table using pandas, then export it to CSV and Excel files.

📋 What You'll Learn

Create a pandas DataFrame with specific sales data

Set a filename variable for saving files

Export the DataFrame to a CSV file

Export the DataFrame to an Excel file

Print confirmation messages after each export

💡 Why This Matters

🌍 Real World

Saving data in multiple formats helps share information with different tools and users easily.

💼 Career

Data scientists often export data to CSV and Excel to communicate results with teams and clients.

1

Create the sales data DataFrame

Import pandas as pd and create a DataFrame called sales_data with these exact columns and values:
'Product': ['Apples', 'Bananas', 'Cherries'],
'Quantity': [10, 20, 15],
'Price': [0.5, 0.3, 0.8]

Pandas

# Import pandas and create the sales_data DataFrame
# Your code here

Hint

Use pd.DataFrame with a dictionary of lists for columns.

2

Set the filename prefix

Create a variable called filename and set it to the string 'store_sales'.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apples', 'Bananas', 'Cherries'],
    'Quantity': [10, 20, 15],
    'Price': [0.5, 0.3, 0.8]
})
# Create the filename variable
# Your code here

Hint

Just assign the string 'store_sales' to filename.

3

Export the DataFrame to CSV and Excel

Use sales_data.to_csv() to save the data to a CSV file named filename + '.csv' without the index. Then use sales_data.to_excel() to save the data to an Excel file named filename + '.xlsx' without the index.

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apples', 'Bananas', 'Cherries'],
    'Quantity': [10, 20, 15],
    'Price': [0.5, 0.3, 0.8]
})
filename = 'store_sales'
# Export sales_data to CSV and Excel files
# Your code here

Hint

Use the to_csv and to_excel methods with index=False.

4

Print confirmation messages

Print the exact messages:
"CSV file saved as store_sales.csv"
and
"Excel file saved as store_sales.xlsx".

Pandas

import pandas as pd
sales_data = pd.DataFrame({
    'Product': ['Apples', 'Bananas', 'Cherries'],
    'Quantity': [10, 20, 15],
    'Price': [0.5, 0.3, 0.8]
})
filename = 'store_sales'
sales_data.to_csv(filename + '.csv', index=False)
sales_data.to_excel(filename + '.xlsx', index=False)
# Print confirmation messages
# Your code here

Hint

Use two print() statements with the exact text.

Practice

1. Which pandas method is used to save a DataFrame as a CSV file?

easy

A. save_csv()

B. write_csv()

C. export_csv()

D. to_csv()

Solution

1. Step 1: Recall pandas export methods

   pandas provides specific methods to export data, such as to_csv() for CSV files.

2. Step 2: Match method to file type

   The method to_csv() is the standard way to save DataFrames as CSV files.

3. Final Answer:

   to_csv() -> Option D

4. Quick Check:

   CSV export method = to_csv() [OK]

Hint: CSV files use to_csv() method in pandas [OK]

Common Mistakes:

• Using non-existent methods like save_csv()
• Confusing to_csv() with to_excel()
• Trying to use write_csv() which is not a pandas method

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. df.export_excel('data.xlsx')

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

Solution

1. Step 1: Identify the correct pandas method for Excel export

   The method to save a DataFrame to Excel is to_excel().

2. Step 2: Check syntax correctness

   The correct syntax is calling df.to_excel('filename.xlsx') with the filename as a string argument.

3. Final Answer:

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

4. Quick Check:

   Excel export syntax = df.to_excel(filename) [OK]

Hint: Use to_excel() with filename string to save Excel files [OK]

Common Mistakes:

• Using non-existent methods like save_excel()
• Missing quotes around filename
• Using write_excel() which is not a pandas method

3. What will be the output of the following code?

import pandas as pd
df = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df.to_json('output.json')
with open('output.json') as f:
    content = f.read()
print(content)

medium

A. {"A":{"0":1,"1":2},"B":{"0":3,"1":4}}

B. [{"A":1,"B":3},{"A":2,"B":4}]

C. Error: to_json() requires extra parameters

D. Empty file content

Solution

1. Step 1: Understand default to_json() output format

   By default, to_json() saves data in 'columns' orientation. The default is 'columns', which creates a nested JSON with columns as keys.

2. Step 2: Check printed JSON content

   The printed content shows keys 'A' and 'B' with nested indices and values, matching the default 'columns' orientation JSON.

3. Final Answer:

   {"A":{"0":1,"1":2},"B":{"0":3,"1":4}} -> Option A

4. Quick Check:

   Default to_json() output = nested JSON by columns [OK]

Hint: Default to_json() saves nested JSON by columns [OK]

Common Mistakes:

• Expecting list of dicts JSON without setting orient
• Thinking to_json() needs extra parameters
• Assuming file is empty after saving

4. You try to save a DataFrame df to Excel using df.to_excel('file.xlsx') but get an error: ModuleNotFoundError: No module named 'openpyxl'. What is the best way to fix this?

medium

A. Change the filename to .csv and use to_csv()

B. Install the openpyxl library using pip

C. Use df.to_excel() without filename

D. Restart Python without any changes

Solution

1. Step 1: Understand the error cause

   The error means pandas needs the 'openpyxl' library to write Excel files but it is not installed.

2. Step 2: Fix by installing the required library

   Installing 'openpyxl' with pip install openpyxl will provide the needed support for Excel export.

3. Final Answer:

   Install the openpyxl library using pip -> Option B

4. Quick Check:

   Missing Excel engine error = install openpyxl [OK]

Hint: Install openpyxl to export Excel files with pandas [OK]

Common Mistakes:

• Ignoring the missing library error
• Trying to save without filename
• Switching to CSV without reason

5. You have a DataFrame df and want to export it to both CSV and HTML formats in one script. Which code snippet correctly does this?

hard

A. df.save(['data.csv', 'data.html'])

B. df.to_csv('data.csv', 'data.html')

C. df.to_csv('data.csv')\ndf.to_html('data.html')

D. df.export(['data.csv', 'data.html'])

Solution

1. Step 1: Understand pandas export methods

   pandas provides separate methods for each format: to_csv() for CSV and to_html() for HTML.

2. Step 2: Use both methods separately

   Calling df.to_csv('data.csv') and then df.to_html('data.html') saves the DataFrame in both formats correctly.

3. Final Answer:

   df.to_csv('data.csv')\ndf.to_html('data.html') -> Option C

4. Quick Check:

   Separate calls for each format = correct export [OK]

Hint: Call to_csv() and to_html() separately to export both [OK]

Common Mistakes:

• Trying to pass multiple filenames to one method
• Using non-existent export() or save() methods
• Expecting one method to handle multiple formats