How to Split given list and insert in excel file using Python?

In this article, we will show you how to split a Python list and insert those items into an Excel file using pandas. This technique is useful for organizing data into structured columns for analysis and reporting.

Assume we have a list containing player names and countries. We'll split this list and create an Excel file with separate columns for each data type.

Expected Output

Our program will create an Excel file outputExcelFile.xlsx with the following structure ?

Algorithm

Following are the steps to split a list and insert data into an Excel file ?

• Import the pandas module for data manipulation and Excel operations
• Create a list containing both player names and countries
• Create an empty pandas DataFrame
• Split the list using slicing ? first half for player names, second half for countries
• Use to_excel() to convert DataFrame to Excel file
• Read and display the created Excel file to verify results

Splitting List Without Index Values

Here's how to split a list and create an Excel file without displaying row indices ?

# importing pandas module
import pandas as pd

# input list containing player names followed by countries
inputList = ['Virat Kohli', 'Bhuvaneshwar Kumar', 'Mahendra Singh Dhoni', 'Rashid Khan', 'Hardik Pandya',
             'David Warner', 'Kieron Pollard', 'Rohit Sharma', 'Kane Williamson',
             'India', 'India', 'India', 'Afghanistan', 'India', 
             'Australia', 'West Indies', 'New Zealand', 'South Africa']

# Creating a new data frame
newDataframe = pd.DataFrame()

# Splitting the list into two pandas columns using slicing
# First 9 items are player names, next 9 items are countries
newDataframe['Player Name'] = inputList[0:9]
newDataframe['Country'] = inputList[9:18]

# Converting the data frame to an excel file without index
newDataframe.to_excel('outputExcelFile.xlsx', index=False)

# Reading the data from the created Excel file to verify
excelData = pd.read_excel('outputExcelFile.xlsx')

# Printing the data frame
print(excelData)

Output

      Player Name          Country
0     Virat Kohli            India
1  Bhuvaneshwar Kumar       India
2  Mahendra Singh Dhoni     India
3        Rashid Khan  Afghanistan
4      Hardik Pandya         India
5       David Warner     Australia
6     Kieron Pollard   West Indies
7       Rohit Sharma   New Zealand
8    Kane Williamson  South Africa

Including Index Values

To include row indices in the Excel file, set index=True in the to_excel() function ?

# Convert DataFrame to Excel with index values
newDataframe.to_excel('outputExcelFile.xlsx', index=True)

This creates an additional column with row numbers ?

   Unnamed: 0           Player Name          Country
0           0          Virat Kohli            India
1           1    Bhuvaneshwar Kumar         India
2           2  Mahendra Singh Dhoni         India
3           3           Rashid Khan   Afghanistan
4           4         Hardik Pandya         India
5           5          David Warner     Australia
6           6        Kieron Pollard   West Indies
7           7          Rohit Sharma   New Zealand
8           8       Kane Williamson  South Africa

Key Points

• List Slicing: Use list[start:end] to split data into logical groups
• DataFrame Creation: Pandas DataFrame organizes data in rows and columns
• Excel Export: to_excel() method converts DataFrame to Excel format
• Index Control: Set index=False to hide row numbers, index=True to show them

Conclusion

Using pandas, you can easily split Python lists and organize data into Excel files with proper column structure. The to_excel() method provides flexibility to include or exclude index values based on your requirements.