Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Split given list and insert in excel file using Python?
In this article, we will show you how to split the list and insert those into an excel file using python.
Assume we have taken a static list and split that list and insert those list items into an excel file, by returning an excel file say outputExcelFile.xlsx containing the below output.
outputExcelFile.xlsx
| Player Name | Country |
|---|---|
| Virat Kohli | India |
| Bhuvaneshwar Kumar | India |
| Mahendra Singh Dhoni | India |
| Rashid Khan | Afghanistan |
| Hardik Pandya | India |
| David Warner | Australia West Indies |
| Kieron Pollard | India |
| Rohit Sharma | New Zealand |
| Kane Williamson | South Africa |
Method 1: Without Displaying Index Values
Algorithm (Steps)
Following are the Algorithm/steps to be followed to perform the desired task −
Use the import keyword, to import the pandas module (Pandas is a Python open-source data manipulation and analysis package. It has an extensive set of data structures and operations for manipulating numerical tables and time series. This module can read, filter, and rearrange small and large datasets in Excel, JSON, and CSV formats.)
Create a variable to store the static/dynamic list.
Using the pandas Dataframe() function, to create a data frame and create a variable to store it.
Split the input list into two pandas columns, one for the "Player Name" and the second for the “Country” using slicing.
Example
newDataframe['Player Name'] = inputList[0:9] newDataframe['Country'] = inputList[10:19]
Use the to_excel() function to convert the above data frame to an excel file (.xlsx) by setting the index as False (we don’t get the index values if we set index=False).
Example
The following program splits the list items and inserts those into an excel file by returning an excel file without displaying index values −
# importing pandas module import pandas as pd # input list inputList = ['Virat Kohli', 'Bhuvaneshwar Kumar', 'Mahendra Singh Dhoni', 'Rashid Khan', 'Hardik Pandya', 'David Warner', 'Kieron Pollard', 'Rohit Sharma', 'Kane Williamson', 'Kagiso Rabada', 'India', 'India', 'India', 'Afghanistan', 'India', 'Australia' 'West Indies', 'India', 'New Zealand', 'South Africa'] # Creating a new data frame newDataframe = pd.DataFrame() # Splitting the list into two pandas columns, one for the "Player Name" and # the second one for the Country using slicing. newDataframe['Player Name'] = inputList[0:9] newDataframe['Country'] = inputList[10:19] # Converting the data frame to an excel file newDataframe.to_excel('outputExcelFile.xlsx', index = False) # Reading the data from the outputExcelFile excelData=pd.read_excel('outputExcelFile.xlsx') #Printing the data frame print(excelData)
Output
On executing, the above program will generate the following output −
Player Name Country 0 Virat Kohli India 1 Bhuvneshwar Kumar India 2 Mahendra Singh Dhoni India 3 Rashid Khan Afghanistan 4 Hardik Pandya India 5 David Warner Australia West Indies 6 Kieron Pollard India 7 Rohit Sharma New Zealand 8 Kane Williamson South Africa
We gave a list of words as input, then created a data frame to store those values as rows and columns, and then created a column in the data frame and utilized slicing for the list to store those values in that column. We converted this data frame to an excel file using the to excel() function, and to see if the data was present, we read the same output excel file using pandas and displayed the values.
Note − If you want to Displaying Index Values
Use the to_excel() function to convert the above data frame to an excel file (.xlsx) by setting the index as True (we will get the index values if we set index=True) like −
newDataframe.to_excel('outputExcelFile.xlsx', index = True)
You will get the following output
Unnamed: 0 Player Name Country 0 0 Virat Kohli India 1 1 Bhuvneshwar Kumar India 2 2 Mahendra Singh Dhoni India 3 3 Rashid Khan Afghanistan 4 4 Hardik Pandya India 5 5 David Warner Australia West Indies 6 6 Kieron Pollard India 7 7 Rohit Sharma New Zealand 8 8 Kane Williamson South Africa
Because we specified index as true, it will generate a new column to store the indices.
Conclusion
We learned how to create a data frame and split the list contents into the data frame's columns, as well as how to convert the data frame to excel and then how to convert the output excel file back to a data frame to acknowledge the results. We learned how to add/remove indexes in an Excel spreadsheet.
4K+ Views
