How to convert pandas data to excel file

Export data to Excel Files are usually the most preferred and convenient way for any user to read and interpret a set of data . By using Pandas library , It can be used Python The code exports your network search or other collected data to Excel In file , And the steps are very simple .

take Pandas DataFrame Convert to Excel Steps for

Follow these steps to learn how to Pandas Data frame write Excel file .

First step ： install pandas and openpyxl

Because you need to export pandas Data frame , Obviously you must have installed pandas package . without , Please run the following pip command , Install... On your computer Pandas python package .

pip install openpyxl

 Copy code 

Now? , In order to be in Python Use in Excel File functions , You need to use the following pip Command to install openpyxl modular .

pip install openpyxl

 Copy code 

You can add... Without mentioning any worksheet names DataFrame write in Excel file . Here is a step-by-step process .

The first 2 Step ： To make a DataFrame

• In your python Code / Import... From script file Pandas package .
• Create a data framework for the data you want to output , And initialize the data frame with the values of rows and columns .

Python Code .

#import pandas package
import pandas as pd

# creating pandas dataframe
df_cars = pd.DataFrame({'Company': ['BMW', 'Mercedes', 'Range Rover', 'Audi'],
     'Model': ['X7', 'GLS', 'Velar', 'Q7'],
     'Power(BHP)': [394.26, 549.81, 201.15, 241.4],
     'Engine': ['3.0 L 6-cylinder', '4.0 L V8', '2.0 L 4-cylinder', '4.0 L V-8']})

 Copy code 

The first 3 Step ： Create a writer object and export it to Excel file

• Use Python Of Excel Writer() Method to create a Excel Writer object . Use pandas Bag ExcelWriter() Method to create a Excel Writing object .
• Input and output Excel The name of the document , You want to take our DataFrame Write to the extension of the file .( In our case , We will output excel The file is named " Convert to excel.xlsx")

# creating excel writer object

writer = pd.ExcelWriter('converted-to-excel.xlsx')

 Copy code 

• stay DataFrame On the call to_excel() function , take Excel Writer Pass as a parameter , Export your data to a with a given name and extension Excel file .
• Save the writer object to save Excel file

# write dataframe to excel

df_cars.to_excel(writer)

# save the excel
writer.save()
print("DataFrame is exported successfully to 'converted-to-excel.xlsx' Excel File.")

 Copy code 

Alternative methods -- Direct method

A direct method is to export the data framework directly to Excel file , Instead of using Excel Writer object , As the following code example shows .

import pandas as pd
 
# creating pandas dataframe from dictionary of data
df_cars = pd.DataFrame({'Company': ['BMW', 'Mercedes', 'Range Rover', 'Audi'],
     'Model': ['X7', 'GLS', 'Velar', 'Q7'],
     'Power(BHP)': [394.26, 549.81, 201.15, 241.4],
     'Engine': ['3.0 L 6-cylinder', '4.0 L V8', '2.0 L 4-cylinder', '4.0 L V-8']})
 
#Exporting dataframe to Excel file
df_cars.to_excel("converted-to-excel.xlsx")

 Copy code 

Output Excel file

open Excel file , You'll see the index 、 Column labels and row data are written to the file .

Tips

You're not limited to controlling excel The name of the document , It's going to be python Export data frame to Excel In file , And in pandas There are also many customizable functions in the package .

You can change excel The name of the worksheet of the file

df.to_excel("output.xlsx", sheet_name='Sheet_name_1')

 Copy code 

Use Excel writer Append to an existing Excel In the document

pd.ExcelWriter('output.xlsx', mode='a')

 Copy code 

Other options include rendering engine 、 Start line 、 header 、 Indexes 、 merge cell 、 Coding and many other options .