Python can take you as far as analyzing the truckloads of data fed into it, but when it comes to presenting the data to a large audience, it ain’t its cup of tea. There are other dedicated tools which might serve the purpose better. One such tool is MS Excel!
This article sets out to explore how the data from Python can be exported into a spreadsheet of MS Excel.
Steps to Export to Excel Using Pandas
To do that we shall be trying to create a sample tabulation within Python & then export it to the desired location within the required sheet of MS Excel. So, let us get on with it!
Importing the Pandas Library
First off would be importing the Pandas library by typing the following code,
import pandas as pd
Creating Sample Data to Export
Now let us create some data using the indexing option. What indexing does is provide the provisions of including a header for each column of data during the time of their creation. In this example, we shall list the names of some internet service providers, their data speeds along with their monthly prices & data limits.
This is how it looks like if we shall translate all the above stipulations into a Python code.
data = {'Operator':['Vodafone', 'Jio', 'BSNL'],'Data (GB)':[3, 5, 2],'Speed (GB/s)':[1,2,0.5],'Price/month':[300,250,320]}
Adding the Data to a Dataframe
Now we’ll assign this data to the data frame by typing,
df=pd.DataFrame(data)
Once done, let us now try to view the data fed within Python using the print() command as shown below.
print (df)
In the above image, one can observe that the very first column has numbers starting from ‘0’ & runs till ‘2’. It’s a bit odd, ain’t it? How about replacing these numbers with some data meaningful?
Setting the Indexes Manually
One can do just that by summoning the Index command which is used to allot the row headers for each row. Let’s say these (imaginary!) data are from the plan from each service provider for the month of May 2022 & we would like to have just that. This can be done by using the following syntax.
df=pd.DataFrame(data,[pd.Index(['row_1_header',' row_1_header ',…])])
Applying the above syntax to introduce May 2022 in the place of numbers as row headers,
df=pd.DataFrame(data,[pd.Index(['May 2022','May 2022','May 2022'])])
Let us verify whether our efforts bear fruit by viewing the data again using the print() command.
Great going!
Exporting Pandas Dataframe to Excel
Now, it is time to export this data into an Excel file. For this, one shall need to create an Excel file first & then copy the location within which the file is created. In this demonstration, an Excel file titled Data.xlsx is created for exporting the data from Python.
Once done, the to_excel() command whose syntax is given below is used to transfer the data.
df.to_excel(“file_address”, sheet_name=’Sheet #’, startrow=0, startcol=0)
Bonus Steps – Customizing The Shee and the Starting Row and Column to Export
There are numerous other customizations that Python provides while exporting data to MS Excel, but the above-listed features shall be demonstrated in this article.
One can also simply stop by giving the file address within the above command and it will execute the exporting of data with the default value for all customisations.
The following is the code to export the data to start at column 9 & row 9 in Sheet 1 of Data.xlsx.
df.to_excel("Data.xlsx",sheet_name='Sheet1',startrow=9, startcol=9)
Summary
Now that we have reached the end of this article, hope it has elaborated on how to export data to MS Excel using Pandas in Python. Here’s another article that details replacing multiple values using Pandas. There are numerous other enjoyable & equally informative articles in AskPython that might be of great help to those who are in looking to level up in Python. Cheers!