-
Notifications
You must be signed in to change notification settings - Fork 0
/
update_BN.py
69 lines (59 loc) · 2.92 KB
/
update_BN.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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
#!/usr/bin/env python
# Script that updates the Excel file with summary of all the bioinformatics tools with the BioNumerics results from a summary Excel file.
# Importing the required libraries
import openpyxl
# Read the input & output file
input_file = "/home/guest/BIT11_Traineeship/Ecoli_AMR/Summary_Excel/BN_summary.xlsx"
output_file = "/home/guest/BIT11_Traineeship/Ecoli_AMR/INFO_MTT_STRAINS_tests.xlsx"
# Load the input and output workbooks
input_wb = openpyxl.load_workbook(input_file)
output_wb = openpyxl.load_workbook(output_file)
# Select the active sheets (assuming there's only one sheet in each file)
input_ws = input_wb.active
output_ws = output_wb["test_update_BN"]
# Start row at 3
row = 3
# Loop through each row in the input file containing the Bionumerics results for each strain
while row <= input_ws.max_row:
# Read values from specific cells
value_2 = input_ws.cell(row=row, column=2).value
value_3 = input_ws.cell(row=row, column=3).value
value_4 = input_ws.cell(row=row, column=4).value
value_5 = input_ws.cell(row=row, column=5).value
value_6 = input_ws.cell(row=row, column=6).value
value_7 = input_ws.cell(row=row, column=7).value
value_8 = input_ws.cell(row=row, column=8).value
value_9 = input_ws.cell(row=row, column=9).value
value_10 = input_ws.cell(row=row, column=10).value
# Row 11 is skipped as it contains results for piperacillin, but this AB is not present on it's own in the output file
value_12 = input_ws.cell(row=row, column=12).value
value_13 = input_ws.cell(row=row, column=13).value
value_14 = input_ws.cell(row=row, column=14).value
value_15 = input_ws.cell(row=row, column=15).value
value_16 = input_ws.cell(row=row, column=16).value
# Write these values to specific cells in the output file
output_ws.cell(row=row, column=5).value = value_2
output_ws.cell(row=row, column=12).value = value_3
output_ws.cell(row=row, column=19).value = value_4
output_ws.cell(row=row, column=26).value = value_5
output_ws.cell(row=row, column=33).value = value_6
output_ws.cell(row=row, column=40).value = value_7
output_ws.cell(row=row, column=47).value = value_8
output_ws.cell(row=row, column=54).value = value_9
output_ws.cell(row=row, column=61).value = value_10
output_ws.cell(row=row, column=68).value = value_12
output_ws.cell(row=row, column=75).value = value_13
output_ws.cell(row=row, column=82).value = value_14
# Only if row 15 (trimethoprim) & 16 (sulfamethoxazole) are both "R", then write "R" to the output file
if value_15 == "R" and value_16 == "R":
output_ws.cell(row=row, column=89).value = "R"
else:
output_ws.cell(row=row, column=89).value = "S"
# Increase the row to move to the next row
row += 1
# Save the changes to the output Excel file
output_wb.save(output_file)
# Close the workbooks
input_wb.close()
output_wb.close()
print(f"Data has been transferred successfully.")