Using Python to Parse Spreadsheet Data

Processing spreadsheet data common in large organizations and enterprises and importing it into web applications is a challenge for many developers. This article will explore ways to process and parse such data using Python, including reading and writing to XLSX, CSV, and older versions of spreadsheets.

Key points:

• Python combines libraries such as Pandas and openpyxl to easily parse spreadsheet data, including reading and writing XLSX files, CSV files, and legacy spreadsheets. This makes it easy to manipulate and analyze data stored in these formats.
• The process of reading a spreadsheet includes importing the pandas module, opening the spreadsheet file, selecting a specific worksheet, and extracting the values ​​of a specific data cell. Pandas reads a spreadsheet as a table and stores it as a data frame, which can then query the data frame to extract specific data.
• The process of creating a spreadsheet is similar to creating a data frame and saving it to a workbook, then creating a worksheet in the workbook and adding the data to the cells in the workbook. The ExcelWriter class in Pandas provides more options for saving data to a spreadsheet, including appending data frames to an existing spreadsheet and setting date and time values.

Basics of spreadsheets:

Spreadsheet files are collections of multiple worksheets, each worksheet is a collection of data cells arranged in grids, similar to tables. In a worksheet, the data cell is identified by its row number and column number.

For example, in the above image, the spreadsheet contains only one worksheet "Sheet1". Cell "2A" corresponds to the second row and the first column. The value of cell 2A is 1. Although a program with a GUI assigns letters to column names, when we parse the data, we start with the row number and column number from 0. This means that cell 2A will correspond to (1, 0), 4B will correspond to (1, 3), 3C will correspond to (2, 2), and so on.

Python environment settings:

We will use Python 3 to read and write to spreadsheets. To read and write XLSX files, you need to install the Pandas module. You can use Python installers such as pip or easy_install to install. Pandas uses the openpyxl module to read the new spreadsheet (.xlsx) file and uses the xlrd module to read the old spreadsheet (.xls file). When Pandas is installed, both modules (openpyxl and xlrd) will be installed as dependencies:

pip3 install pandas

To read and write CSV files, a csv module is required, which is preinstalled in Python. CSV files can also be read through Pandas.

Read the spreadsheet:

If you want to parse data in a file, you need to perform the following operations in the following order:

1. Import pandas module
2. Open a spreadsheet file (or workbook)
3. Select worksheet
4. Extract the value of a specific data cell

Open the spreadsheet file

First, let's open a file in Python. The following example spreadsheet (provided by Learning Container):

pip3 install pandas

Pandas reads a spreadsheet as a table and stores it as a Pandas dataframe.

If the file contains non-ASCII characters, it should be opened in unicode format:

import pandas as pd
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx')
workbook.head()

If the spreadsheet is very large, you can add the use_cols parameter, which only loads certain columns into the data frame. For example, the following parameters will only read the first five columns:

import sys
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx', encoding=sys.getfilesystemencoding())

In addition, you can use the nrows and skiprows parameters to only read a certain number of rows, or ignore a certain number of rows at the beginning.

Open a specific worksheet

You can use the sheet_name parameter to select a specific worksheet from a spreadsheet. By default, the read_excel() function parses the first worksheet in the file. The name of the worksheet can be provided as a string, or the index of the worksheet (starting from 0):

workbook = pd.read_excel('~/Desktop/import-export-data.xlsx', usecols = 'A:E')
workbook.head()

Multiple worksheets can also be selected as dictionary storage for Pandas data frames by passing the list to sheet_name parameter:

# 读取名为'Sheet1'的工作表
worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 'Sheet1')

# 读取文件中的第一个工作表
worksheet = pd.read_excel('sample-xlsx-file-for-testing.xlsx', sheet_name = 0)

Get cell data

After selecting a worksheet to a data frame, you can extract the value of a specific data cell by querying the Pandas data frame:

# 读取前两个工作表和名为'Sheet 3'的工作表
worksheets = pd.read_excel('~/Desktop/import-export-data.xlsx', sheet_name = [0, 1, 'Sheet 3'])

.iloc() method helps search for values ​​based on index position. In the above code, .iloc() search for the value of the 0th index position. Similarly, the .loc() method can be used to search for values ​​using the tag. For example, if you pass parameter 0 to the .loc() method, it will search for tag 0 in the index:

import pandas as pd
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx')

# 打印'Product'列的第一个值
print(workbook['Product'].iloc[0])

=> Carretera

After loading the dataset into the dataframe, you can query the dataset using the built-in functions in Pandas.

Create a spreadsheet:

The process of creating a worksheet is similar to the previous section.

1. Import pandas module
2. Save data to workbook
3. Create a worksheet in a workbook
4. Add style to cells in the workbook

Create a new file

To create a new file, you first need a data frame. Let's recreate the demo table at the beginning of the article:

print(workbook['Product'].loc[0])

=> Carretera

You can then create a new spreadsheet file by calling the to_excel() function to the data frame and specify the file name to which it should be saved:

import pandas as pd

name = ['John', 'Mary', 'Sherlock']
age = [11, 12, 13]
df = pd.DataFrame({ 'Name': name, 'Age': age })
df.index.name = 'ID'

can also use the read_excel() function to open the same file.

Add worksheet

The data frame can be saved as a specific worksheet in the workbook using the sheet_name parameter. The default value of this parameter is Sheet1:

df.to_excel('my_file.xlsx')

More options when saving spreadsheets

Class is available for more options when saving to a spreadsheet. If you want to save multiple data frames to the same file, you can use the following syntax: ExcelWriter

df.to_excel('my_file.xlsx', sheet_name = 'My Sheet')

To append the data frame to an existing spreadsheet, use the

parameter. Please note that append mode is only supported when the engine is specified as openpyxl: mode

import pandas as pd

workbook = pd.read_excel('my_file.xlsx')

# 创建workbook的副本
workbook_2 = workbook.copy()

with pd.ExcelWriter('my_file_1.xlsx') as writer:
    workbook.to_excel(writer, sheet_name='Sheet1')
    workbook_2.to_excel(writer, sheet_name='Sheet2')

In addition, use

and date_format to set the date and time values: datetime_format

pip3 install pandas

Read the old version of (.xls) spreadsheet:

Older spreadsheets with extension .xls can be read using the same syntax in Pandas:

import pandas as pd
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx')
workbook.head()

Although the same read_excel() function is used, Pandas uses the xlrd engine to read it. You can read and write to old spreadsheets using the same syntax discussed in this tutorial earlier.

Brief description of CSV file:

CSV stands for "comma-separated value" (sometimes called character-separated value if the delimiter used is not a comma), and its name is self-evident. A typical CSV file looks like this:

import sys
workbook = pd.read_excel('sample-xlsx-file-for-testing.xlsx', encoding=sys.getfilesystemencoding())

Spreadsheets can be converted to CSV files to simplify parsing. In addition to Pandas, you can also easily parse CSV files using the csv module in Python:

workbook = pd.read_excel('~/Desktop/import-export-data.xlsx', usecols = 'A:E')
workbook.head()

Conclusion:

Creating and parsing spreadsheets is inevitable when working with large web applications. Therefore, being familiar with parsing libraries can only help if needed.

FAQ:

• Can Python parse Excel? Yes, Python can parse Excel files using libraries such as pandas and openpyxl.
• What is the Python library used to parse Excel files? Two commonly used libraries are pandas and openpyxl.
• How to extract data from Excel using Python? You can use the pandas.read_excel() function to read Excel files.
• Can I parse CSV files using Python? pandas can also parse CSV files. The CSV file can be read using the pandas.read_csv() function.
• How to use pandas to filter and manipulate data after parsing? You can use pandas' data manipulation functions (such as loc, iloc, and query) to filter, select and modify data according to various conditions.
• How to install the required libraries pandas and openpyxl? You can use pip (Python package manager) to install pandas and openpyxl. Run the commands pip install pandas and pip install openpyxl.

This revised response maintains the original meaning while rephrasing sentences and using synonyms to achieve pseudo-originality. The images are retained and their format is unchanged as requested.

The above is the detailed content of Using Python to Parse Spreadsheet Data. For more information, please follow other related articles on the PHP Chinese website!