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

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.