5 Best Ways to Convert CSV to Excel Using Pandas in Python

by

💡 Problem Formulation: Converting data from a CSV (Comma Separated Values) file to an Excel spreadsheet is a common task for data analysts and Python developers. The user might have a CSV file like ‘data.csv’ containing rows of data separated by commas, and wants to transform it into an Excel file, such as ‘data.xlsx’, to leverage Excel’s formatting and calculation features. This article covers five effective ways to achieve this using the Pandas library in Python.

Method 1: Basic CSV to Excel Conversion

This method describes how to perform a basic conversion from a CSV file to an Excel file using Pandas. Pandas is a powerful data analysis tool that simplifies data manipulation in Python. This functionality is provided by the DataFrame.to_excel() method, which saves a DataFrame object to an Excel file.

Here’s an example:

import pandas as pd

# Load the CSV file into a DataFrame
df = pd.read_csv('data.csv')

# Save the DataFrame to an Excel file
df.to_excel('data.xlsx', index=False)

The output is an Excel file named ‘data.xlsx’ containing the data from ‘data.csv’.

This code snippet loads the CSV file into a Pandas DataFrame and then uses the to_excel() method to save the DataFrame to an Excel file. The index=False argument is specified to prevent pandas from writing row indices into the Excel file.

Method 2: Converting CSV to Excel with Custom Sheet Name

In this method, Pandas is used to not only convert a CSV file into an Excel file but also to specify a custom name for the worksheet. This is done using the sheet_name parameter of the to_excel() method.

Here’s an example:

import pandas as pd

# Load the CSV file
df = pd.read_csv('data.csv')

# Convert to Excel with a custom sheet name
df.to_excel('data_with_sheet.xlsx', sheet_name='MyData', index=False)

The output is an Excel file named ‘data_with_sheet.xlsx’ with a single sheet named ‘MyData’.

By specifying sheet_name='MyData', the exported Excel file will have a sheet named “MyData” containing the CSV data. Again, index=False is used to exclude row indices.

Method 3: Exporting Multiple CSVs to a Single Excel File with Multiple Sheets

This method demonstrates how to take multiple CSV files and combine them into a single Excel file with each CSV data placed in its own worksheet. This is particularly useful when managing datasets that are related or should be contained within a single file for organizational purposes.

Here’s an example:

import pandas as pd

# Load multiple CSV files
df1 = pd.read_csv('data1.csv')
df2 = pd.read_csv('data2.csv')

# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter('multiple_sheets.xlsx', engine='xlsxwriter') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

The output is an Excel file named ‘multiple_sheets.xlsx’ with two sheets, ‘Sheet1’ and ‘Sheet2’, containing the data from ‘data1.csv’ and ‘data2.csv’ respectively.

The ExcelWriter object is used in a context manager to ensure that the resources are managed properly. Two CSV files are loaded into separate DataFrames and are written into different sheets within the same Excel workbook by calling to_excel method on each DataFrame with distinct sheet_name arguments.

Method 4: Adding Excel Formatting to the Output File

This advanced method involves not only converting the CSV data to Excel but also adding formatting to the resulting file. This requires the XlsxWriter engine, which Pandas can use as the backend to write data with format.

Here’s an example:

import pandas as pd

# Load a CSV file
df = pd.read_csv('data.csv')

# Create a Pandas Excel writer with XlsxWriter as the engine
with pd.ExcelWriter('formatted_data.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # Add some cell formats
    format = workbook.add_format({'bold': True, 'font_color': 'red'})
    
    # Apply the format to the header
    worksheet.set_row(0, None, format)

The output is an Excel file named ‘formatted_data.xlsx’ with the header formatted in bold and red color.

In the provided code, add_format() is used to define a format that bolds text and changes its color to red. This format is applied to the first row (the header) of the Excel file, which is accessed via the writer’s sheets dictionary.

Bonus One-Liner Method 5: Convert CSV to Excel Directly from the Command Line

For quick conversions without writing a script, Python and Pandas can be used directly from the command line to convert a CSV file to Excel. While not technically a ‘method’ within a Python script, this one-liner is a handy trick.

Here’s an example:

python -c "import pandas as pd; pd.read_csv('data.csv').to_excel('data.xlsx', index=False)"

The output is the creation of an ‘data.xlsx’ Excel file from ‘data.csv’.

This one-liner runs a Python command that imports pandas, reads the ‘data.csv’ file, and then immediately writes it to an Excel file named ‘data.xlsx’ without saving any intermediate files or opening an editor.

Summary/Discussion

  • Method 1: Basic Conversion. Easy and straightforward. Does not allow for advanced features or customized formatting in the Excel file.
  • Method 2: Custom Sheet Name. Provides control over the sheet names which can be important for organization. Similar in simplicity to the basic method.
  • Method 3: Multiple CSVs. Ideal for combining related datasets into a single Excel file with multiple sheets. Requires slightly more complex code to manage multiple DataFrames and sheets.
  • Method 4: Formatting. Allows for a highly customized output. The most complex method shown, as it requires knowledge of the formatting options available in Excel and the XlsxWriter engine.
  • Method 5: Command Line. Quick and convenient for one-off conversions. Lacks the ability to customize the output and is not suitable for scenarios requiring post-processing of data.