-
Notifications
You must be signed in to change notification settings - Fork 0
/
filterExcel.py
33 lines (25 loc) · 1000 Bytes
/
filterExcel.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#Install necessary library
#pip install pandas, xlsxwriter, openpyxl
#Read original excel file, filter column, remove unnecessary column and export final data to new excel file
import pandas as pd
data = pd.read_excel(r"C:\Users\user\Desktop\Sample.xlsx")
print(data)
print("-------------------")
#result1 = data[(data["City"]=="Tokyo") & (data["Job"]=="Engineer")]
result2 = data[(data["Job"]=="Engineer") | (data["Job"]=="Teacher") & (data["Gender"]=="Female")]
#print(result1)
#print("-------------------")
#print(result2)
#print("-------------------")
#Create a new Excel file to store filtered data
writer = pd.ExcelWriter(r"C:\Users\user\Desktop\Result.xlsx", engine='xlsxwriter')
writer.save()
#Use Dataframe to sort/filter data
processDrop = pd.DataFrame(result2)
print(processDrop)
print("------------------")
#Remove Gender column
del processDrop["Gender"]
print(processDrop)
#Export final data to new Excel
processDrop.to_excel(r"C:\Users\user\Desktop\Result.xlsx", index=False)