Articles

Convert pandas DataFrame to excel

Convert pandas DataFrame to excel


In this article, we will discuss how to convert pandas dataframe to excel.

 

Introduction

DataFrame in pandas is an two dimensional data structure that will store data in two dimensional format. One dimension refers to a row and second dimension refers to a column, So It will store the data in rows and columns.

We can able to create this DataFrame using DataFrame() method. But this is available in pandas module, so we have to import pandas module.

Syntax:

pandas.DataFrame(data)

Where, data is the input dataframe, The data can be a dictionary that stores list of values with specified key

 

ExampleCreate Dataframe

In this example, we will create a dataframe with 4 rows and 4 columns with college data and assign indices through index parameter.

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])

#display the dataframe
print(data)

Output: Dataframe is created below

      college_id       college_name college_address Total Staff
one        c-001  vignan university          guntur        1200
two        c-021               vvit          guntur        3422
three      c-002           RVR - JC          guntur        5644
four       c-004  Andhra University          guntur         670

We can pandas dataframe to excel by using to_excel() function.

Syntax:

dataframe_input.to_excel('file_name.xlsx')

where, dataframe_input is the input dataframe and file_name.xlsx is the name of excel file to be converted.

There are different parameters that this method will accept. Lets see one by one as an example.

Example 1:

In this example, we are converting dataframe to excel without specifying  any parameters.

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to excel
data.to_excel('converted.xlsx')

Output:

 

Example 2:

In this example, we are converting dataframe to excel with index parameter.If it is True, it will set the indices, otherwise it will not set the indices.

This is used to set or remove the index in the excel file.

Syntax:

dataframe_input.to_excel('file_name.xlsx',index)

Code: Set index to False

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to excel
data.to_excel('converted.xlsx',index=False)

Output:

In the output, we will see that index will not present.

Example 3:

In this example, we are converting dataframe to excel with header parameter.

This is used to set or remove the columns in the excel file. If it is True, it will set the columns, otherwise it will not set the columns.

Syntax:

dataframe_input.to_excel('file_name.xlsx',header)

Code: Set header to False

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to excel
data.to_excel('converted.xlsx',header=False)

Output:

In the output, we will see that header will not present.

Example 4:

In this example, we are converting dataframe to excel with columns parameter.

This is used to set the particular  columns in the excel  file. We will provide the columns to be displayed in excel through a list

Syntax:

dataframe_input.to_excel('file_name.xlsx',columns=[columns])

Code: Set only 2 columns - 'college_id','Total Staff'

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to excel
data.to_excel('converted.xlsx',columns=['college_id','Total Staff'])

Output:

In the output, we will see that only two columns are present.

Example 5:

In this example, we are converting dataframe to excel with header parameter.

This is used to change  the columns names  in the excel file. We have to specify the new column names throuhg a list

Syntax:

dataframe_input.to_excel('file_name.xlsx',header=[new_column_names])

Code: Set columns to - ['col1','col2','col3','col3']

import pandas as pd

#create dataframe from the college data
data= pd.DataFrame({'college_id':['c-001','c-021','c-002','c-004'],

                    'college_name':["vignan university","vvit","RVR - JC","Andhra University"],

                   "college_address":["guntur","guntur","guntur","guntur"],

                    "Total Staff":[1200,3422,5644,670]

                   },index=['one','two','three','four'])


#convert to excel
data.to_excel('converted.xlsx',header=['col1','col2','col3','col3'])

Output:

In the output, we will see that column names are modified.

 


Pandas

Would you like to see your article here on tutorialsinhand. Join Write4Us program by tutorialsinhand.com

About the Author
Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University. Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning
Page Views :    Published Date : Jun 14,2024  
Please Share this page

Related Articles

Like every other website we use cookies. By using our site you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service. Learn more Got it!