Data organization refers to processing imported data at the file level, including adding, moving, and deleting row and column data, as well as operations such as data sorting, filtering, merging, and concatenation.
Data Organization Using Object Models
Using Excel VBA and Python xlwings, data can be imported into Excel worksheets and methods provided by worksheet objects and cell objects can be used to copy, move, insert, and delete rows and columns. Sorting, filtering, and other operations can also be performed to achieve data organization. Please refer to Chapters 4 and 13 for details; this section will not elaborate further. Data can be processed using data graphics, charts, and pivot tables—please refer to Chapters 17–19 (this section will not elaborate further).
Data Organization Using Excel Functions
Using Excel, Excel VBA, and Python xlwings, Excel functions can be called to process data. Please refer to Chapter 16 (this section will not elaborate further).
Data Organization Using Power Query and the pandas Package
Both Power Query and the pandas package are designed for handling large datasets and cannot operate on the Excel object model—displaying massive data in Excel worksheets is meaningless. Additionally, loading and unloading data in worksheets takes time and affects processing speed.
Power Query and the pandas package provide many tools or functions for row operations, column operations, data merging, splitting, sorting, filtering, etc., making data organization easy. Section 9.1.2 introduced an example of data concatenation using the concat method of the pandas package. Below is another example of data filtering.
During data processing, sometimes only a subset of raw data is needed. When using the read_excel method of the pandas package, parameters such as usecols, skiprows, nrows, skip_footer, and sheet_name can be used to selectively import partial data. For imported data, boolean indexing can be used for filtering.
The following Python script uses the pandas package to open the Excel file Personnel by Department.xlsx in the current path. It imports specified columns of data from the file using the read_excel method of pandas. The script is stored at Samples\ch21\Python\Personnel by Department.py.
import pandas as pd
import os
root = os.getcwd()
df = pd.read_excel(
io=root + r'\Personnel by Department.xlsx',
usecols=['ID', 'Gender', 'Age', 'Department', 'Salary'],
engine='openpyxl'
)
print(df)When running the script, the selected data is output in the Python Shell window:
>>> = RESTART: ...\Basic\Samples\ch21\Python\Personnel by Department.py
ID Gender Age Department Salary
0 10001 F 45 Dept2 4300
1 10002 F 42 Dept1 3800
2 10003 M 29 Dept1 3600
3 10004 F 40 Dept1 4400
4 10005 M 55 Dept2 4500
5 10006 M 35 Dept3 4100
6 10007 M 23 Dept2 3500
7 10008 M 36 Dept1 3700
8 10009 M 50 Dept1 4800To select data for female staff, add the following line to the script:
df2 = df[df['Gender'] == 'F']
print(df2)Running the script outputs the selected data:
>>> = RESTART: ...\Basic\Samples\ch21\Python\Personnel by Department.py
ID Gender Age Department Salary
0 10001 F 45 Dept2 4300
1 10002 F 42 Dept1 3800
3 10004 F 40 Dept1 4400To select data for staff with a salary greater than 4000 yuan and age ≤ 40, add the following line:
df3 = df[(df['Salary'] > 4000) & (df['Age'] <= 40)]
print(df3)Running the script outputs:
>>> = RESTART: ...\Basic\Samples\ch21\Python\Personnel by Department.py
ID Gender Age Department Salary
3 10004 F 40 Dept1 4400
5 10006 M 35 Dept3 4100The where method of the DataFrame object can also be used for filtering (it is also based on boolean indexing). Below is an example of filtering staff aged ≥ 35. Add the following line to the script:
df4 = df.where(df['Age'] >= 35)
print(df4)Running the script outputs:
>>> = RESTART: ...\Basic\Samples\ch21\Python\Personnel by Department.py
ID Gender Age Department Salary
0 10001.0 F 45.0 Dept2 4300.0
1 10002.0 F 42.0 Dept1 3800.0
2 NaN NaN NaN NaN NaN
3 10004.0 F 40.0 Dept1 4400.0
4 10005.0 M 55.0 Dept2 4500.0
5 10006.0 M 35.0 Dept3 4100.0
6 NaN NaN NaN NaN NaN
7 10008.0 M 36.0 Dept1 3700.0
8 10009.0 M 50.0 Dept1 4800.0By default, the where method replaces non-matching data with NaN (i.e., empty). An other parameter can be used to specify a replacement value.
Data Organization Using SQL
In Python, the pandasql package can be used to process DataFrame data. To install pandasql, first open the DOS command window via Windows → Accessories → Command Prompt, then enter the following command:
pip install –U pandasql
Press Enter to start installation.
The main function in the pandasql package is sqldf, which has two parameters:
The first parameter is the SQL query statement.
The second parameter specifies the environment variable (can be locals() or globals()).
To simplify usage, a lambda function is often defined to create an anonymous function—this way, only the SQL statement needs to be specified when using it:
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
q = ... # SQL query statement to retrieve data from table df
df2 = pysqldf(q) # df2 is the result of the queryThe following Python script uses the pandas package to open Personnel by Department.xlsx in the current path. It first imports data from the file using read_excel, then uses SQL to extract all information for male staff. The script is stored at Samples\ch21\Python\sql.py.
import pandas as pd # Import pandas package
from pandasql import sqldf # Import pandasql package
import os # Import os package
root = os.getcwd() # Get current path
# Read data from the specified file
df = pd.read_excel(io=root + r'\Personnel by Department.xlsx', engine='openpyxl')
pysqldf = lambda q: sqldf(q, globals()) # Define anonymous function
q = "SELECT * FROM df WHERE Gender = 'M'" # SQL query statement
df2 = pysqldf(q) # Call anonymous function(parameter: query statement) to return results
df2 = pysqldf(q) # Call anonymous function again(redundant, but retained per original text)
print(df2) # Output query resultsRunning the script outputs:
>>> = RESTART: ...\Basic\Samples\ch21\Python\sql.py
ID Gender Age Education Department PositionLevel Salary
0 10003 M 29 PhD Dept1 Director 3600
1 10005 M 55 Bachelor Dept2 DeputyDirector 4500
2 10006 M 35 Master Dept3 Director 4100
3 10007 M 23 Bachelor Dept2 Clerk 3500
4 10008 M 36 College Dept1 Clerk 3700
5 10009 M 50 Master Dept1 DirectorGeneral 4800