22-02-2021 Guido Cattani
from pathlib import Path
import pandas as pd
def read_BCD_output_65():
f_in = '/media/guido/LACIE/Cingle_Guido/Master/BCD_band_output.xlsx'
p_in = Path(f_in)
df = pd.read_excel(p_in, sheet_name='BCD_output_65', header=0, nrows=85)
df = df.drop(['Unnamed: 0', 'Study_ID', 'Device'], axis=1)
df = df.fillna(pd.NA)
return df
def read_BCD_output_55():
f_in = '/media/guido/LACIE/Cingle_Guido/Master/BCD_band_output.xlsx'
p_in = Path(f_in)
df = pd.read_excel(p_in, sheet_name='BCD_output_55', header=0, nrows=85)
df = df.drop(['Unnamed: 0', 'Study_ID', 'Device'], axis=1)
df = df.fillna(pd.NA)
return df
def read_head():
# function to read first 2 columns
f_in = '/media/guido/LACIE/Cingle_Guido/Master/SL_BC.xlsx'
p_in = Path(f_in)
df = pd.read_excel(p_in, sheet_name=0, header=0, usecols=[1, 2], nrows=85)
return df
def read_ISTS_SPL():
# function to read ISTS dB SPL for 1/3 thirdbands, 65 dB & 55 dB
f_in = '/media/guido/LACIE/Cingle_Guido/Master/constants.xlsx'
p_in = Path(f_in)
col_to_use = list(range(20))
df = pd.read_excel(p_in, sheet_name='ISTS_sound_pressure',
header=0, nrows=2, usecols=col_to_use)
df = df.fillna(pd.NA)
df = df.rename(columns={'Unnamed: 0' : 'Signal'})
df = df.set_index(['Signal'])
df = df.drop(['125_Hz', '160_Hz', '200_Hz'], axis = 1)
s65 = pd.Series(df.iloc[0])
s55 = pd.Series(df.iloc[1])
return (s65, s55)
def ISTS_input_difference():
t = read_ISTS_SPL()
diff = t[0] - t[1]
return diff
def gain_65():
t = read_ISTS_SPL()
inpt = t[0]
gn = read_BCD_output_65() - inpt
return gn
def gain_55():
t = read_ISTS_SPL()
inpt = t[0]
gn = read_BCD_output_55() - inpt
return gn
def compression_ratio():
cr = (ISTS_input_difference()) / (gain_65() - gain_55())
return cr
gain_65()
250_Hz | 315_Hz | 400_Hz | 500_Hz | 630_Hz | 800_Hz | 1000_Hz | 1250_Hz | 1600_Hz | 2000_Hz | 2500_Hz | 3150_Hz | 4000_Hz | 5000_Hz | 6300_Hz | 8000_Hz | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 7.4 | 16.6 | 22.1 | 27.6 | 33.8 | 38.0 | 34.3 | 31.0 | 30.8 | 29.3 | 27.7 | 27.3 | 25.6 | 24.5 | 22.4 | 15.1 |
1 | 2.1 | 9.7 | 14.1 | 22.8 | 31.3 | 37.3 | 35.1 | 35.1 | 35.5 | 33.4 | 29.1 | 26.6 | 21.9 | 19.1 | 15.5 | 8.0 |
2 | 1.2 | 13.5 | 20.2 | 28.0 | 35.0 | 40.0 | 36.0 | 33.0 | 32.1 | 30.3 | 28.3 | 28.2 | 26.7 | 25.4 | 22.8 | 16.5 |
3 | -2.3 | 5.6 | 10.1 | 18.5 | 27.1 | 33.9 | 32.5 | 32.8 | 33.6 | 31.4 | 26.8 | 24.3 | 19.4 | 16.5 | 12.3 | 4.3 |
4 | 0.2 | 11.7 | 18.3 | 25.7 | 33.1 | 37.9 | 34.7 | 31.7 | 31.3 | 29.9 | 28.1 | 27.7 | 26.2 | 25.1 | 23.1 | 16.6 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
80 | -9.3 | -4.8 | 5.1 | 20.9 | 27.8 | 31.5 | 31.3 | 36.1 | 38.7 | 36.5 | 30.3 | 31.2 | 33.8 | 31.3 | 25.6 | 14.9 |
81 | -8.4 | -3.0 | 5.4 | 19.4 | 25.0 | 27.0 | 24.3 | 29.3 | 33.2 | 32.8 | 34.3 | 36.6 | 36.5 | 34.1 | 30.9 | 21.6 |
82 | -8.6 | -4.6 | 2.3 | 17.8 | 24.7 | 28.1 | 26.3 | 27.5 | 27.8 | 25.0 | 26.6 | 31.2 | 33.9 | 32.3 | 28.5 | 18.8 |
83 | -8.9 | -3.2 | 6.9 | 21.4 | 27.7 | 30.9 | 29.3 | 34.3 | 39.5 | 40.1 | 37.4 | 36.5 | 35.2 | 31.6 | 26.8 | 16.6 |
84 | -9.7 | -6.1 | 2.8 | 19.1 | 26.4 | 30.6 | 30.5 | 35.1 | 36.6 | 32.7 | 28.1 | 31.4 | 34.8 | 33.7 | 30.9 | 21.5 |
85 rows × 16 columns
cr = compression_ratio()
cr = cr.round(2)
cr = pd.concat([read_head(), cr], axis=1)
cr
Study_ID | Device | 250_Hz | 315_Hz | 400_Hz | 500_Hz | 630_Hz | 800_Hz | 1000_Hz | 1250_Hz | 1600_Hz | 2000_Hz | 2500_Hz | 3150_Hz | 4000_Hz | 5000_Hz | 6300_Hz | 8000_Hz | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | BP110 | 0.97 | 0.92 | 0.94 | 0.96 | 0.98 | 0.99 | 1.02 | 1.00 | 0.96 | 0.94 | 0.91 | 0.91 | 0.91 | 0.93 | 0.94 | 0.97 |
1 | 2 | BP110 | 1.41 | 1.33 | 1.28 | 1.10 | 1.04 | 1.03 | 1.01 | 0.99 | 1.00 | 0.99 | 0.96 | 0.93 | 0.88 | 0.88 | 0.91 | 1.00 |
2 | 3 | BP110 | 0.99 | 0.95 | 0.97 | 1.00 | 1.04 | 1.01 | 1.03 | 1.02 | 0.99 | 0.96 | 0.93 | 0.92 | 0.92 | 0.92 | 0.94 | 0.96 |
3 | 4 | BP110 | 1.69 | 1.52 | 1.47 | 1.19 | 1.11 | 1.08 | 1.03 | 0.99 | 0.97 | 0.96 | 0.95 | 0.93 | 0.88 | 0.91 | 0.93 | 1.19 |
4 | 5 | BP110 | 1.14 | 1.03 | 1.05 | 1.04 | 1.01 | 0.99 | 1.02 | 1.00 | 0.97 | 0.94 | 0.93 | 0.93 | 0.92 | 0.93 | 0.93 | 0.93 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
80 | 81 | BAHA5P | 1.14 | 1.12 | 1.02 | 0.93 | 0.91 | 0.92 | 0.97 | 1.06 | 1.09 | 1.09 | 0.98 | 0.93 | 0.93 | 0.93 | 0.92 | 0.95 |
81 | 82 | BAHA5P | 1.23 | 1.22 | 1.16 | 1.12 | 1.10 | 1.04 | 0.96 | 0.93 | 0.93 | 0.94 | 0.93 | 0.93 | 0.93 | 0.99 | 1.03 | 1.05 |
82 | 83 | BAHA5P | 1.09 | 1.05 | 0.96 | 0.91 | 0.89 | 0.89 | 0.93 | 0.98 | 1.02 | 1.00 | 0.93 | 0.92 | 0.91 | 0.92 | 0.90 | 0.93 |
83 | 84 | BAHA5P | 1.33 | 1.30 | 1.22 | 1.12 | 1.12 | 1.09 | 1.05 | 1.05 | 1.03 | 1.00 | 0.95 | 0.93 | 0.93 | 0.94 | 0.93 | 0.94 |
84 | 85 | BAHA5P | 1.12 | 1.25 | 1.12 | 1.00 | 0.96 | 0.93 | 0.95 | 0.95 | 0.96 | 1.00 | 0.95 | 0.94 | 0.92 | 0.93 | 0.92 | 0.94 |
85 rows × 18 columns
# write results to xlsx file in Master directory
fout = '/media/guido/LACIE/Cingle_Guido/Master/Compression_ratio.xlsx'
pout = Path(fout)
with pd.ExcelWriter(pout) as writer:
cr.to_excel(writer, sheet_name='CR')