from pathlib import Path
import pandas as pd
def read_BC_SL():
# function to read BC_sensation levels from xlsx file
f_in = '/media/guido/LACIE/Cingle_Guido/Master/Headband/SL_BC.xlsx'
p_in = Path(f_in)
l = list()
for i in range(6):
df = pd.read_excel(p_in, sheet_name=i, header=0, nrows=85)
df = df.drop(['Unnamed: 0', 'Study_ID', 'Device'], axis=1)
df = df.fillna(pd.NA)
l.append(df)
return l
def read_AC_SL():
# function to read AC sensation levels
f_in = '/media/guido/LACIE/Cingle_Guido/Master/Headband/SL_AC.xlsx'
p_in = Path(f_in)
l = list()
for i in range(6):
df = pd.read_excel(p_in, sheet_name=i, header=0, nrows=85)
df = df.drop(['Unnamed: 0','Study_ID', 'Device'], axis=1)
df = df.fillna(pd.NA)
l.append(df)
return l
def read_head():
# function to read first 2 columns
f_in = '/media/guido/LACIE/Cingle_Guido/Master/Headband/SL_BC.xlsx'
p_in = Path(f_in)
df = pd.read_excel(p_in, sheet_name=0, header=0, usecols=[1, 2], nrows=85)
#df = df.drop(['Unnamed: 0'], axis=1)
return df
def read_freq():
clm = (read_BC_SL()[0]).columns
return clm
def diff_sl():
# return list with BC sensation levels for 0, 90 and 270 degrees
bcsl = read_BC_SL()
acsl = read_AC_SL()
idx = read_freq()
info = read_head()
res = list()
l = [(0, 0), (1, 2), (2, 1)] # combinations of degrees for 65 dB
for t in l:
bc = bcsl[t[0]]
ac = acsl[t[1]]
dsl = bc - ac
dsl = dsl.reindex(columns=idx)
dsl = dsl.fillna(-999)
dsl = dsl.round(0)
dsl = dsl.astype('int32')
dsl = dsl.replace(-999, pd.NA)
dsl = pd.concat([info, dsl], axis=1)
res.append(dsl)
l1 = [(3, 3), (4, 5), (5, 4)] # combinations of degrees for 55 dB
for t in l1:
bc = bcsl[t[0]]
ac = acsl[t[1]]
dsl = bc - ac
dsl = dsl.reindex(columns=idx)
dsl = dsl.fillna(-999)
dsl = dsl.round(0)
dsl = dsl.astype('int32')
dsl = dsl.replace(-999, pd.NA)
dsl = pd.concat([info, dsl], axis=1)
res.append(dsl)
return res
dsl = diff_sl()
dsl[1]
Study_ID | Device | 125_Hz | 160_Hz | 200_Hz | 250_Hz | 315_Hz | 400_Hz | 500_Hz | 630_Hz | ... | 1000_Hz | 1250_Hz | 1600_Hz | 2000_Hz | 2500_Hz | 3150_Hz | 4000_Hz | 5000_Hz | 6300_Hz | 8000_Hz | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | BP110 | -23 | -40 | -51 | -55 | -47 | -42 | -36 | -26 | ... | -18 | -23 | -29 | -28 | -31 | -25 | -32 | -16 | 0 | -14 |
1 | 2 | BP110 | -29 | -46 | -56 | -60 | -54 | -50 | -41 | -27 | ... | -12 | 1 | 7 | -4 | -5 | 6 | -6 | 0 | 3 | -21 |
2 | 3 | BP110 | -29 | -40 | -44 | -41 | -34 | -30 | -26 | -11 | ... | 4 | <NA> | <NA> | -7 | -15 | -14 | -36 | -16 | 2 | -8 |
3 | 4 | BP110 | -23 | -40 | -50 | -54 | -49 | -43 | -35 | -23 | ... | -9 | <NA> | <NA> | -1 | <NA> | <NA> | 2 | 12 | 20 | 0 |
4 | 5 | BP110 | -20 | -38 | -48 | -52 | -41 | -32 | -23 | -10 | ... | 3 | <NA> | <NA> | -8 | <NA> | <NA> | -7 | 2 | 9 | -13 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
80 | 81 | BAHA5P | -42 | -57 | -66 | -68 | -72 | -66 | -57 | -36 | ... | -7 | -11 | -17 | -9 | -6 | 7 | -9 | 0 | 5 | -19 |
81 | 82 | BAHA5P | -26 | -39 | -48 | -48 | -50 | -47 | -38 | -24 | ... | -9 | -7 | -6 | -3 | -6 | -2 | -16 | -9 | -3 | -28 |
82 | 83 | BAHA5P | -26 | -44 | -54 | -58 | -57 | -53 | -40 | -30 | ... | -22 | -4 | 6 | 4 | -6 | -7 | -24 | -12 | -4 | -25 |
83 | 84 | BAHA5P | -37 | -50 | -58 | -58 | -55 | -44 | -31 | -16 | ... | 6 | 3 | -4 | -10 | -13 | -3 | 2 | 15 | 26 | 7 |
84 | 85 | BAHA5P | -42 | -58 | -67 | -69 | -70 | -62 | -48 | -35 | ... | -17 | -16 | -16 | -13 | -11 | 2 | -18 | -6 | 3 | -18 |
85 rows × 21 columns
# write results to xlsx file in Master directory
fout = '/media/guido/LACIE/Cingle_Guido/Master/Headband/Diff_SL.xlsx'
pout = Path(fout)
with pd.ExcelWriter(pout) as writer:
dsl[0].to_excel(writer, sheet_name='Diff_SL_65dB_0deg')
dsl[1].to_excel(writer, sheet_name='Diff_SL_65dB_90deg')
dsl[2].to_excel(writer, sheet_name='Diff_SL_65dB_270deg')
dsl[3].to_excel(writer, sheet_name='Diff_SL_55dB_0deg')
dsl[4].to_excel(writer, sheet_name='Diff_SL_55dB_90deg')
dsl[5].to_excel(writer, sheet_name='Diff_SL_55dB_270deg')