How to Convert CSV to Excel using Pandas in Python?

In this article, we will show you how to convert a CSV File (Comma Separated Values) to an excel file using the pandas module in python.

Assume we have taken an excel file with the name ExampleCsvFile.csv containing some random text. We will return a CSV File after converting the given excel file into a CSV file.

ExampleCsvFile.csv

Method 1: Converting CSV to Excel without Displaying Index values

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Import the pandas module (Pandas is a Python open-source data manipulation and analysis package.This module can read, filter, and rearrange small and large datasets in Excel, JSON, and CSV formats.)

• Create a variable to store the path of the CSV file after reading a file using the pandas read_csv() function (loads a CSV file as a pandas dataframe).

• Create an output excel file with the pandas ExcelWriter() class (To save a DataFrame to an Excel sheet, use the pandas ExcelWriter() class. This class is typically used to save multiple sheets and append data to an existing Excel sheet.

  Pandas ExcelWriter Highlights If xlsxwriter is installed, it is used by default; otherwise, openpyxl is used).

• Convert the CSV file to an excel file using the to_excel() function (To export the DataFrame to an excel file, use the to_excel() function. The target file name must be specified when writing a single object to an excel file) without displaying index values by passing the index as False as an argument. Here index=False indicates no index values are displayed.

• Use the save() function (saves the file) to save the result/output excel file.

Example

The following program converts the CSV file into an excel file without displaying index values −

# importing pandas module
import pandas as pd

# reading the csv file
cvsDataframe = pd.read_csv('ExampleCsvFile.csv')

# creating an output excel file
resultExcelFile = pd.ExcelWriter('ResultExcelFile.xlsx')

# converting the csv file to an excel file
cvsDataframe.to_excel(resultExcelFile, index=False)

# saving the excel file
resultExcelFile.save()

Output

On executing, the above program a new Excel file (resultExcelFile.csv) will be created with data from the CSV file.

In this program, we read a CSV file containing some random dummy data as a data frame using the pandas read_csv() function, and then we created a new excel file and converted the above CSV data frame to excel using the to excel() function. If we pass the index as a false argument, the output excel file removes the index row at the start. If no index parameter is given, it adds an index row at the beginning of the excel sheet and then we save the resultant excel file using the save() function to apply the changes.

Method 2: Converting CSV to Excel With Displaying Index values

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• load the CSV as a pandas data frame.

• Convert the CSV file to an excel file by passing the index as True as an argument to the excel() function and displaying index values. index=True means that index values are shown here.

• Use the save() function (saves the file) to save the result/output excel file.

• Read the output Excel file with the read_excel() function (loads an Excel file as a pandas data frame) and convert it to a data frame object with the pandas module's DataFrame() function.

• Show/display the data frame object.

Example

The following program converts the CSV file into an excel file with displaying index values −

# importing pandas module
import pandas as pd

# reading the csv file
cvsDataframe = pd.read_csv('ExampleCsvFile.csv')

# creating an output excel file
resultExcelFile = pd.ExcelWriter('ResultExcelFile.xlsx')

# converting the csv file to an excel file
cvsDataframe.to_excel(resultExcelFile, index=True)

# saving the excel file
resultExcelFile.save()

# Reading and Converting the output/result excel file into a dataframe object
excelDataframe=pd.read_excel('ResultExcelFile.xlsx')

# Displaying the dataframe object
print(excelDataframe)

Output

   Unnamed: 0   Player Name              Age       Type        Country \
0   0            Virat Kohli             33       Batsman       India
1   1            Bhuvneshwar Kumar      34       Batsman       India
2   2            Mahendra Singh Dhoni    39       Batsman       India
3   3            Rashid Khan             28       Bowler        Afghanistan
4   4            Hardik Pandya           29       All rounder   India
5   5            David Warner            34       Batsman       Australia
6   6            Kieron Pollard          35       All rounder   West Indies
7   7            Rohit Sharma            33       Batsman        India
8   8            Kane Williamson         33       Batsman       New Zealand
9   9            Kagiso Rabada           29       Bowler        South Africa
        Team                         Runs               Wickets
0      Royal Challengers Bengaluru   6300                 20
1      Sun Risers Hyderabad          333                  140
2      Chennai Super Kings           4500                  0
3      Gujarat Titans                500                  130
4      Gujarat Titans                2400                 85
5      Delhi Capitals                5500                 12
6      Mumbai Indians                3000                 67
7      Mumbai Indians                5456                 20
8      Sun Risers Hyderabad          3222                  5
9      Lucknow Capitals              335                  111

Here we passed the index as a true as an argument, the result excel file adds the index row at the start and then we save the resultant excel file using the save() function to apply the changes. Then we converted the excel file to a data frame to see if the values from the CSV file were copied into the Excel file.

Conclusion

In this tutorial, we learned how to read a CSV file, then how to convert it to an Excel file and remove the index or add indices at the start of the excel file, and finally how to convert the Excel file to a pandas data frame.

Vikram Chiluka

Updated on: 18-Aug-2022

17K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started