There are several ways to improve the performance of matching data between two Excel sheets using Python without relying on the traditional nested for loop approach. Here are a few suggestions:
1. **Pandas**: As you mentioned, using the `pandas` library is a quick and efficient way to perform data manipulation and merging operations. You can load both sheets into pandas DataFrames, perform a left join on the desired columns, and then save the result back to an Excel file. Here's an example:
```python
import pandas as pd
df1 = pd.read_excel('A.xlsx')
df2 = pd.read_excel('B.xlsx')
merged_df = pd.merge(df1, df2, left_on='COL 1', right_on='COL 1', how='left')
merged_df.to_excel('output.xlsx', index=False)
```
2. **Dictionary-based approach**: You can use dictionaries to create a lookup table for faster matching. This approach avoids the need for nested loops. Here's an example:
```python
from openpyxl import load_workbook
workbook9 = load_workbook('A.xlsx')
worksheet9 = workbook9.active
workbook10 = load_workbook('B.xlsx')
worksheet10 = workbook10.active
w9_max = 10000
w10_max = 150000
lookup_dict = {}
# Populate the lookup dictionary
for c in range(2, w10_max):
temp10 = worksheet10.cell(row=c, column=1).value
lookup_dict[temp10] = worksheet10.cell(row=c, column=19).value
# Match and update values in Sheet A
for i in range(2, w9_max):
temp9 = worksheet9.cell(row=i, column=1).value
if temp9 in lookup_dict:
worksheet9.cell(row=i, column=7).value = lookup_dict[temp9]
workbook9.save('output.xlsx')
```
By using a dictionary to store the values from Sheet B, you can perform lookups in constant time, significantly improving the performance compared to nested loops.
3. **Numpy**: If you prefer working with arrays, you can utilize the `numpy` library to convert the Excel data into arrays and perform the matching operation. However, keep in mind that this approach may consume more memory compared to the previous methods. Here's an example:
```python
import numpy as np
from openpyxl import load_workbook
workbook9 = load_workbook('A.xlsx')
worksheet9 = workbook9.active
workbook10 = load_workbook('B.xlsx')
worksheet10 = workbook10.active
w9_max = 10000
w10_max = 150000
# Convert Sheet A to a numpy array
data9 = np.array([[worksheet9.cell(row=i, column=j).value for j in range(1, 8)] for i in range(2, w9_max)])
# Convert Sheet B to a numpy array
data10 = np.array([[worksheet10.cell(row=i, column=j).value for j in range(1, 20)] for i in range(2, w10_max)])
# Perform the matching operation
for row9 in data9:
temp9 = row9[0]
for row10 in data10:
temp10 = row10[0]
if temp9 == temp10:
row9[6] = row10[18]
break
# Save the updated data back to Sheet A
for i, row9 in enumerate(data9):
for j, value in enumerate(row9):
worksheet9.cell(row=i+2, column=j+1).value = value
workbook9.save('output.xlsx')
```
These are just a few examples of how you can improve the performance when matching data between Excel sheets using Python. Remember to adjust the code based on your specific column names, ranges, and requirements. |
簡體版
訂閱頻道
訂閱電子報
發表於 2023-9-5 19:12
| 
, 我覺得用CPYTHON 有可能仲快, 不過自己唔識, 而且好滿意
