During the process of data processing and analysis, we often need to export data structures (such as lists) in Python to Excel tables. This process may seem a bit complicated for beginners, but with some simple steps and sample code, we can easily convert a Python list to the first column of an Excel table. This article will introduce this process in detail and provide rich cases and code to help newbies quickly master this skill.
1. Introduction
Python provides a variety of libraries to process Excel files, the two most commonly used libraries are openpyxl and pandas. openpyxl is a library for reading and writing Excel files, while pandas is a library for data analysis and processing. Both can conveniently export Python lists to Excel. This article will explain how to use these two libraries to achieve this goal separately.
2. Use openpyxl to convert Python list to the first column of an Excel table
Install the openpyxl library
First, we need to install the openpyxl library. You can use the following command to install:
pip install openpyxl
Create Excel file and write data
Next, we will create an Excel file and write the Python list to the first column of that file. Here are the detailed steps and code:
import openpyxl
# Create an Excel file objectworkbook = ()
# Get the activity worksheetworksheet =
= "Sheet1"
# Python list to be writtenlist1 = ['McDonald', 'dcpeng', 'Moon God', 'prince', 'Feng Cheng', 'Brother Liang', 'Shen Fu']
# Write the list to the first column of Excelfor i in range(len(list1)):
(i+1, 1, list1[i])
# Save Excel file('output_openpyxl.xlsx')In the above code, we first import the openpyxl library, and then create an Excel file object and an active worksheet. Next, we iterate through the Python list and write each element into the first column of Excel. Finally, we saved the Excel file.
Case expansion
Suppose we have a 2D list with multiple sublists and we just want to write the first sublist to the first column of Excel. The following code can be used:
import openpyxl
# Create an Excel file objectworkbook = ()
# Get the activity worksheetworksheet =
= "Sheet1"
# 2D Listdata = [['Name', 'age', 'gender'], ['Zhang San', 25, 'male'], ['Li Si', 30, 'female'], ['Wang Wu', 28, 'male']]
# Write the first sublist to the first column of Excelfirst_column_data = data[0]
for i in range(len(first_column_data)):
(i+1, 1, first_column_data[i])
# Save Excel file('output_openpyxl_extended.xlsx')In the above code, we define a two-dimensional list data and write only the first sublist data[0] into the first column of Excel.
3. Use pandas to convert Python list to the first column of an Excel table
Install the pandas library
First, we need to install the pandas library. You can use the following command to install:
pip install pandas
Create DataFrame and write to Excel
The pandas library provides a more concise and efficient way to handle Excel files. We can directly convert the Python list to a DataFrame object of pandas and then write it to an Excel file using the to_excel method. Here are the detailed steps and code:
import pandas as pd
# Python list to be writtenlist1 = ['McDonald', 'dcpeng', 'Moon God', 'prince', 'Feng Cheng', 'Brother Liang', 'Shen Fu']
# Convert list to DataFramedf = (list1, columns=['Column 1'])
# Write DataFrame to Excel filedf.to_excel('output_pandas.xlsx', index=False)
In the above code, we first import the pandas library, then convert the Python list to a DataFrame object and specify the column name 'Column 1'. Next, we use the to_excel method to write the DataFrame to the Excel file and set index=False to avoid writing to the row index.
Case expansion
Suppose we have a 2D list with multiple sublists and we just want to write the first sublist to the first column of Excel. The following code can be used:
import pandas as pd
# 2D Listdata = [['Name', 'age', 'gender'], ['Zhang San', 25, 'male'], ['Li Si', 30, 'female'], ['Wang Wu', 28, 'male']]
# Write the first sublist to the first column of Excelfirst_column_data = data[0]
df = (first_column_data, columns=['Column 1'])
# Write DataFrame to Excel filedf.to_excel('output_pandas_extended.xlsx', index=False)
In the above code, we define a two-dimensional list data and write only the first sublist data[0] into the first column of Excel.
4. Comprehensive case: Write multiple Python lists into different columns in Excel
In practical applications, we may need to write multiple Python lists into different columns in Excel. Here is a comprehensive example of how to achieve this:
import pandas as pd
# Multiple Python listslist1 = ['McDonald', 'dcpeng', 'Moon God']
list2 = [25, 30, 28]
list3 = ['male', 'male', 'female']
# Combine multiple lists into a two-dimensional listdata = [list(t) for t in zip(list1, list2, list3)]
# Add column name(0, ['Name', 'age', 'gender'])
# Convert a 2D list to a DataFramedf = (data[1:], columns=data[0])
# Write DataFrame to Excel filedf.to_excel('output_pandas_multiple_columns.xlsx', index=False)In the above code, we first define three Python lists list1, list2, and list3. We then use the zip function to combine these three lists into a two-dimensional list and use list(t) to convert it into a list form of the list. Next, we insert a list containing the column names at the beginning of the two-dimensional list. Finally, we convert the 2D list to a DataFrame object and write it to an Excel file.
V. Conclusion
This article details how to convert a Python list to the first column of an Excel table using the openpyxl and pandas libraries. Through detailed steps, code and cases, we show common operations such as installing libraries, creating Excel files, writing data, and processing 2D lists. I hope these contents will be helpful to newbies and help them master this skill quickly. In practical applications, we can select appropriate libraries and methods to process Excel files according to specific needs.
This is the article about how to convert Python lists to the first column of Excel table. For more related contents to the first column of Python lists, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!