Importing Data

Importing Data Using the Object Model

When the data volume is small, you can first import the data into an Excel worksheet, then use Excel, Excel functions, Excel VBA, or Python xlwings for data processing and analysis.

Section 1.3.1 introduced opening an existing Excel file using the Open method of the workbook object; this will not be repeated here.

Importing Data Using the pandas Package

Both Excel’s Power Query and Python’s pandas package were developed for data cleaning of large datasets, allowing easy processing of millions of rows. In contrast, Excel and Excel VBA can only handle hundreds of thousands of rows. For Power Query methods, readers may refer to relevant materials; this section focuses on how to import data using the pandas package.

Section 1.3.1 introduced opening an existing Excel file using the Open method of the workbook object; this will not be repeated here.

1. Reading/Writing Excel Files

The pandas package’s read_excel method can read Excel data. The read_excel method has many parameters; the commonly used ones are shown in Table 9-1. Using these parameters, you can import both regular and many irregular Excel datasets. The imported data is of DataFrame type.

Table 9-1 Commonly Used Parameters of the read_excel Method

Note: When using read_excel, errors such as missing xlrd may occur. It is recommended to install openpyxl and specify engine='openpyxl' when calling read_excel.

To install openpyxl:

Open the Command Prompt (Windows → Accessories → Command Prompt).

Enter:

pip install openpyxl

Press Enter to install. A message like Finished processing dependencies for openpyxl indicates success.

The following Python script opens the Excel file ID_Numbers.xlsx in the current directory using pandas. The file contains two worksheets with staff information. The script imports data from the first worksheet. Script path: Samples\ch21\Python\ID_Numbers.py.

import pandas as pd   # Import pandas
import os             # Import os
root = os.getcwd()   # Get current path
# Read data from specified file
df = pd.read_excel(io=root + r'\ID_Numbers.xlsx', engine='openpyxl')
print(df)  # Output data

Running the script outputs the first worksheet data in the Python Shell:

   ID Department Name                ID_Card Gender
0 1001   Finance  Chen Dong  5103211978100300**      F
1 1002   Finance  Tian Ju  4128231980052512**      M
2 1003 Production Wang Wei  4302251980031135**      M
3 1004 Production Wei Long  4302251985111635**      F
4 1005    Sales Liu Yang  4302251980081235**      F

By default, the first row is used as the header (column index), and row indices start from 0.

The sheet_name parameter can specify one or more worksheets; index_col can set a column as the row index. Below, we open two worksheets, set the “ID” column as the row index:

df2 = pd.read_excel(io='D:\ID_Numbers.xlsx', sheet_name=[0,1], index_col='ID', engine='openpyxl')
print(df2)

Output:

{0:         Department Name                ID_Card Gender
ID
1001   Finance  Chen Dong  510321197810030016      F
1002   Finance  Tian Ju  412823198005251008      M
... ,
 1:         Department  Name                ID_Card Gender
ID
1006   Production  Lv Chuan  320325197001017024      F
... 
}

The result is a dictionary: keys are worksheet indices, values are DataFrames.

Use the DataFrame object’s to_excel method to write pandas data to an Excel file. For example, merge the first two worksheets and save to another file:

df3 = df2[0]  # Data from worksheet 1
df4 = df2[1]  # Data from worksheet 2
df5 = pd.concat([df3, df4])  # Vertical concatenation
print(df5)
df5.to_excel(root + r'\new_file.xlsx')

This saves merged data to new_file.xlsx.

2. Reading/Writing CSV Files

CSV is one of the most common data storage formats. Use pandas read_csv to read CSV files. Common parameters are in Table 9-2.

Table 9-2 Commonly Used Parameters of the read_csv Method

Example script reading ID_Numbers.csv:

import pandas as pd
import os
root = os.getcwd()
df = pd.read_csv(root + r'\ID_Numbers.csv', encoding='gbk')
print(df)

To extract female staff and save:

df2 = df[df['Gender'] == 'F']
df2.to_csv(root + r'\new_file.csv', encoding='gbk')