def average_se_trace_full_experiment(file_max_column, file_max_row, sheet):
# average_se_trace_full_experiment function calculates the average and standard error of all the samples in each time-point
# generating and average trace for the whole experiment with its standard error.
# file_max_column: calculated by any of the analysis functions.
# file_max_row: calculated by any of the analysis functions.
# sheet: calculated by any of the analysis functions.
average_trace_header = sheet.cell(row=2,
column=file_max_column + 4).coordinate
sheet[average_trace_header] = "Experiment Average"
ca_ex_st.style_headers(average_trace_header, sheet)
average_trace_SE_header = sheet.cell(row=2,
column=file_max_column + 5).coordinate
sheet[average_trace_SE_header] = "Experiment SE"
ca_ex_st.style_headers(average_trace_SE_header, sheet)
for row in range(3, file_max_row + 1):
first_cell = sheet.cell(row=row, column=2).coordinate
last_cell = sheet.cell(row=row, column=file_max_column).coordinate
average_cells = f"= average({first_cell}:{last_cell})"
average_value = sheet.cell(row=row,
column=file_max_column + 4).coordinate
sheet[average_value] = average_cells
ca_ex_st.style_number(average_value, sheet)
standar_error_cells = f"= stdev({first_cell}:{last_cell})/sqrt({file_max_column-1})"
standar_error_value = sheet.cell(row=row,
column=file_max_column + 5).coordinate
sheet[standar_error_value] = standar_error_cells
ca_ex_st.style_number(standar_error_value, sheet)
def basal_ratio_pretreatment_and_increment_calculation(column, file_max_column,
integral_time,
row_number, sheet):
# basal_ratio_pretreatment_and_increment_calculation function calculates the basal ratio before pre-estimuli or precalcium,
# and generates the "ratio true value - ratio basal value" for each frame,
# allowing us to calculate the agonist-evoked Ca2+ release and/or entry
# as the integral of the rise in fura-2 fluorescence ratio
# for 2½ min after the addition of the agonist or CaCl2.
# to achieve that, the function requieres the following values:
# column: calculated by any of the analysis functions.
# integral_time: Set as default as 150 seconds, but could be modified by the user.
# file_max_column: calculated by any of the analysis functions.
# row_number: use pre_stimuli to calculate the calcium release integral or pre_calcium for the calcium entry integral.
# sheet: calculated by any of the analysis functions.
ca_integral = row_number
basal_begining = sheet.cell(row=ca_integral - 11, column=column).coordinate
basal_ending = sheet.cell(row=ca_integral - 1, column=column).coordinate
ca_integral_header = sheet.cell(row=ca_integral,
column=file_max_column + 7).coordinate
sheet[ca_integral_header] = f"= average({basal_begining}:{basal_ending})"
ca_ex_st.style_average_values(ca_integral_header, sheet)
# Cálculo incremento ratio de calcio basal tras el estímulo
for row in range(ca_integral + 1, ca_integral + 1 + integral_time + 1):
ratio_sample = sheet.cell(row=row, column=column).coordinate
ratio_normalized = sheet.cell(row=row,
column=file_max_column + 7).coordinate
sheet[
ratio_normalized] = f"= {ratio_sample}-{absolute_coordinate(ca_integral_header)}"
ca_ex_st.style_number(ratio_normalized, sheet)
def parameter_initial_ratio_values(column, file_max_column, n_integral_value,
sheet):
# parameter_initial_ratio_values function generates an excel cell with the average ratio value for the first 10 rows (or 20 seconds)
# file_max_column: calculated by any of the analysis functions.
# n_integral_value: value that calculates the position of the excel cell.
# n_initial_ratio_value
# sheet: calculated by any of the analysis functions.
calcium_beginning = sheet.cell(row=3, column=column).coordinate
calcium_ending = sheet.cell(row=13, column=column).coordinate
calcium_integral = f"= (average({calcium_beginning}:{calcium_ending}))"
cell_n_calcium_value = sheet.cell(row=n_integral_value,
column=file_max_column + 7).coordinate
sheet[cell_n_calcium_value] = calcium_integral
ca_ex_st.style_number(cell_n_calcium_value, sheet)
def parameter_peak_values(file_max_column, integral_time, n_integral_value,
row_number, sheet):
# parameter_peak_values function generates an excel cell with the maximun value (Peak) in the integral_time range
# for a given parameter in each sample. (Parameters= Calcium release | Calcium Entry)
# file_max_column: calculated by any of the analysis functions.
# integral_time: Set as default as 150 seconds, but could be modified by the user.
# n_integral_value: value that calculates the position of the excel cell. Calculated by any of the analysis functions.
# n_calcium_release_peak_value | n_calcium_entry_peak_value
# row_number: use pre_stimuli to calculate the calcium release integral or pre_calcium for the calcium entry integral.
# sheet: calculated by any of the analysis functions.
calcium_beginning = sheet.cell(row=row_number + 1,
column=file_max_column + 7).coordinate
calcium_ending = sheet.cell(row=row_number + 1 + integral_time,
column=file_max_column + 7).coordinate
calcium_integral = f"= MAX({calcium_beginning}:{calcium_ending})"
cell_n_calcium_value = sheet.cell(row=n_integral_value,
column=file_max_column + 7).coordinate
sheet[cell_n_calcium_value] = calcium_integral
ca_ex_st.style_number(cell_n_calcium_value, sheet)
def single_cell_ratio_normalized_f_f0(keyword, sheet_f_f0_max_column,
sheet_f_f0_max_row, sheet_f_f0):
# single_cell_ratio_normalized_f_f0 function normalizes the ratio along the time of each sample with its basal value.
# To achieve that, the program, and not this function, will create a copy of the main worksheet 'Time Measurement Report' and rename it as 'F_F0'.
# Following along the function will them calculate the average ratio basal value for the 10 first values
# within the timelap in 'Time Measurement Report', and will write the result in the first row of 'F_F0' for each sample.
# Finally, it will divide each of the values in the timelap for each sample with its calculated average,
# writing the result in the worksheet 'F_F0'.
number_cell = 1
for column in range(1, sheet_f_f0_max_column + 1):
if sheet_f_f0.cell(row=2, column=column).value:
if keyword in sheet_f_f0.cell(row=2, column=column).value:
sample_number_header = sheet_f_f0.cell(
row=2, column=column).coordinate
sheet_f_f0[
sample_number_header] = f"#{number_cell:02} {keyword} - F/F0"
ca_ex_st.style_headers(sample_number_header, sheet_f_f0)
basal_begining = sheet_f_f0.cell(row=3,
column=column).coordinate
basal_ending = sheet_f_f0.cell(row=13,
column=column).coordinate
ca_f_f0_integral_header = sheet_f_f0.cell(
row=1, column=column).coordinate
sheet_f_f0[
ca_f_f0_integral_header] = f"= average('Time Measurement Report'!{basal_begining}:{basal_ending})"
ca_ex_st.style_average_values(ca_f_f0_integral_header,
sheet_f_f0)
# Cálculo ratio de calcio basal al comienzo del experimento
for row in range(3, sheet_f_f0_max_row + 1):
ratio_sample = sheet_f_f0.cell(row=row,
column=column).coordinate
ratio_normalized = sheet_f_f0.cell(
row=row, column=column).coordinate
sheet_f_f0[
ratio_normalized] = f"= 'Time Measurement Report'!{ratio_sample}/F_F0!{absolute_coordinate(ca_f_f0_integral_header)}"
ca_ex_st.style_number(ratio_normalized, sheet_f_f0)
number_cell += 1
def single_cell_average_se_paramemeters(analyzed_cells, analyzed_parameter,
n_integral_title, n_integral_value,
sheet, total_column):
# single_cell_average_se_paramemeters function calculates the average and standard error of each parameter for the full experiment.
# analyzed_parameter: Release | Release Peak | Release Slope | Entry | Entry Peak | Entry Slope.
# n_integral_title: value that calculates the position of the excel cell. Calculated by any of the analysis functions.
# n_calcium_release_integral_title | n_calcium_entry_integral_title
# n_calcium_release_peak_title | n_calcium_entry_peak_title
# n_calcium_release_slope_title | n_calcium_entry_slope_title
# n_integral_value: value that calculates the position of the excel cell. Calculated by any of the analysis functions.
# n_calcium_release_integral_value | n_calcium_entry_integral_value
# n_calcium_release_peak_value | n_calcium_entry_peak_value
# n_calcium_release_slope_value | n_calcium_entry_slope_value
# sheet: calculated by any of the analysis functions.
# total_column: final number of columns in the file after all the calculations are performed.
average_title = sheet.cell(row=n_integral_title,
column=total_column + 2).coordinate
sheet[average_title] = f"Average {analyzed_parameter}"
ca_ex_st.style_average_values(average_title, sheet)
standar_error_title = sheet.cell(row=n_integral_title,
column=total_column + 3).coordinate
sheet[standar_error_title] = "SE"
ca_ex_st.style_average_values(standar_error_title, sheet)
first_cell = sheet.cell(row=n_integral_value,
column=(total_column + 1) -
analyzed_cells).coordinate
last_cell = sheet.cell(row=n_integral_value,
column=total_column).coordinate
average_cells = f"= average({first_cell}:{last_cell})"
average_value = sheet.cell(row=n_integral_value,
column=total_column + 2).coordinate
sheet[average_value] = average_cells
ca_ex_st.style_number(average_value, sheet)
standar_error_cells = f"= stdev({first_cell}:{last_cell})/sqrt({analyzed_cells})"
standar_error_value = sheet.cell(row=n_integral_value,
column=total_column + 3).coordinate
sheet[standar_error_value] = standar_error_cells
ca_ex_st.style_number(standar_error_value, sheet)
def parameter_slope_values(column, file_max_column, n_integral_value,
row_number, sheet, slope_time, time_column):
# slope_values function generates an excel cell with the slope value of the trendline calculated from the integral_time range
# for a given parameter in each sample. (Parameters= Calcium release | Calcium Entry)
# file_max_column: calculated by any of the analysis functions.
# integral_time: Set as default as 150 seconds, but could be modified by the user.
# n_integral_value: value that calculates the position of the excel cell. Calculated by any of the analysis functions.
# n_calcium_release_slope_value | n_calcium_entry_slope_value
# row_number: use pre_stimuli to calculate the calcium release integral or pre_calcium for the calcium entry integral.
# sheet: calculated by any of the analysis functions.
calcium_beginning = sheet.cell(row=row_number + 1,
column=column).coordinate
calcium_ending = sheet.cell(row=row_number + 1 + slope_time,
column=column).coordinate
slope_beginning = sheet.cell(row=row_number + 1,
column=time_column).coordinate
slope_ending = sheet.cell(row=row_number + 1 + slope_time,
column=time_column).coordinate
calcium_slope = f"= SLOPE({calcium_beginning}:{calcium_ending},{slope_beginning}:{slope_ending})"
cell_n_calcium_value = sheet.cell(row=n_integral_value,
column=file_max_column + 7).coordinate
sheet[cell_n_calcium_value] = calcium_slope
ca_ex_st.style_number(cell_n_calcium_value, sheet)
def imaging_ca_oscillation_multi_analysis(adquisiton_time, keyword, file, folder, peak_amplitude, peak_longitude, route, time_initial_linregress, time_final_linregress, y_max_value, y_min_value):
route = os.path.join(folder, file)
os.chdir(folder)
wb = openpyxl.load_workbook(route)
sheets = wb.sheetnames
for sheet in sheets:
if sheet != "Time Measurement Report":
to_delete = wb[sheet]
wb.remove(to_delete)
sheet = wb.active
sheet.delete_cols(2)
sheet.delete_rows(3)
sheet.delete_rows(1)
file_max_row = sheet.max_row
file_max_column = sheet.max_column
event_number_time_final_linregress = file_max_row
ca_cal.calcium_oscillation_experiment_time_in_seconds(
adquisiton_time, file_max_row, sheet)
number_cell = 1
for column in range(1, file_max_column + 1):
if sheet.cell(row=1, column=column).value:
if keyword in sheet.cell(row=1, column=column).value:
sample_number_header = sheet.cell(
row=1, column=column).coordinate
sheet[sample_number_header] = f"#{number_cell:02} {keyword}"
ca_ex_st.style_headers(sample_number_header, sheet)
number_cell += 1
splited_file = file.split(".xlsx")[0]
non_space_filename = splited_file.replace(" ", "_")
wb.save(f"{non_space_filename}_modified.xlsx")
file_route = f"{non_space_filename}_modified.xlsx"
df = pd.read_excel(file_route)
x = df["Time (s)"]
y = df.iloc[:, 1:]
max_column_num = len(df.columns)
column_num_plots = max_column_num + 2
row_num_plots = 2
column_num_data = max_column_num + 10
row_num_data = 2
for column in y.columns:
fig, ax = plt.subplots(
2, 1, sharex=True, sharey=True, constrained_layout=True)
fig = matplotlib.pyplot.gcf()
fig.set_size_inches(6, 5)
ax[0].plot(x, y[column], color="black")
ax[1].plot(x, y[column], color="black")
# Añade peaks al archivo excel
peaks, _ = find_peaks(
y[column], prominence=peak_amplitude, width=peak_longitude) # BEST!
parameter_value_peaks = f"Peak time {column}"
df_peaks = pd.DataFrame(
{parameter_value_peaks: peaks * adquisiton_time})
append_df_to_excel(file_route, df_peaks.T, sheet_name="Time Measurement Report",
startrow=row_num_data, startcol=column_num_data, header=True, index=True, truncate_sheet=False)
row_num_data += 2
peaks, properties = find_peaks(
y[column], prominence=peak_amplitude, width=peak_longitude)
properties["prominences"], properties["widths"]
parameter_value_max = f"Max {column}"
df_max = pd.DataFrame({parameter_value_max: properties["prominences"]})
append_df_to_excel(file_route, df_max.T, sheet_name="Time Measurement Report",
startrow=row_num_data, startcol=column_num_data, header=False, index=True, truncate_sheet=False)
row_num_data += 1
parameter_value_width = f"Width {column}"
df_width = pd.DataFrame(
{parameter_value_width: properties["widths"] * adquisiton_time})
append_df_to_excel(file_route, df_width.T, sheet_name="Time Measurement Report",
startrow=row_num_data, startcol=column_num_data, header=False, index=True, truncate_sheet=False)
row_num_data += 2
# The numpy and scipy libraries include the composite trapezoidal (numpy.trapz)
# and Simpson's (scipy.integrate.simps)rules.
# https://stackoverflow.com/questions/13320262/calculating-the-area-under-a-curve-given-a-set-of-coordinates-without-knowing-t/13323861#13323861
# Añade los valores de la recta de regresión obtenida con los valores iniciales y el área bajo la curva al archivo excel
df_initial_time = x[:time_initial_linregress]
df_initial_trend = y[column][:time_initial_linregress]
res_initial = stats.linregress(
df_initial_time, df_initial_trend)
df_initial_linear_regresion = res_initial.intercept + res_initial.slope*x
corrected_y_initial = y[column].sub(
df_initial_linear_regresion.squeeze())
r_squared_initial = float(f"{res_initial.rvalue**2:.6f}")
intercept_initial = res_initial.intercept
slope_initial = res_initial.slope
addapted_df_to_excel(column, r_squared_initial, file_route,
column_num_data, row_num_data, "R-squared initial")
row_num_data += 1
addapted_df_to_excel(column, intercept_initial, file_route,
column_num_data, row_num_data, "Intercept initial")
row_num_data += 1
addapted_df_to_excel(column, slope_initial, file_route,
column_num_data, row_num_data, "Slope initial")
row_num_data += 1
area_initial_trapz = trapz(
corrected_y_initial, x) * adquisiton_time
addapted_df_to_excel(column, area_initial_trapz, file_route, column_num_data,
row_num_data, "Trapz AUC Initial linear regresion")
row_num_data += 1
area_initial_simps = simps(
corrected_y_initial, x) * adquisiton_time
addapted_df_to_excel(column, area_initial_simps, file_route, column_num_data,
row_num_data, "Simps AUC Initial linear regresion")
row_num_data += 2
# Añade los valores de la recta de regresión obtenida con los valores finales y el área bajo la curva al archivo excel
df_final_time = x.tail(time_final_linregress)
df_final_trend = y[column].tail(time_final_linregress)
res_final = stats.linregress(df_final_time, df_final_trend)
df_final_linear_regresion = res_final.intercept + res_final.slope*x
corrected_y_final = y[column].sub(
df_final_linear_regresion.squeeze())
r_squared_final = float(f"{res_final.rvalue**2:.6f}")
intercept_final = res_final.intercept
slope_final = res_final.slope
addapted_df_to_excel(column, r_squared_final, file_route,
column_num_data, row_num_data, "R-squared final")
row_num_data += 1
addapted_df_to_excel(column, intercept_final, file_route,
column_num_data, row_num_data, "Intercept final")
row_num_data += 1
addapted_df_to_excel(column, slope_final, file_route,
column_num_data, row_num_data, "Slope final")
row_num_data += 1
area_final_trapz = trapz(
corrected_y_final, x) * adquisiton_time
addapted_df_to_excel(column, area_final_trapz, file_route, column_num_data,
row_num_data, "Trapz AUC Final linear regresion")
row_num_data += 1
area_final_simps = simps(
corrected_y_final, x) * adquisiton_time
addapted_df_to_excel(column, area_final_simps, file_route, column_num_data,
row_num_data, "Simps AUC Final linear regresion")
row_num_data += 11
# Make a plot with major ticks that are multiples of 20 and minor ticks that
# are multiples of 5. Label major ticks with '%d' formatting but don't label
# minor ticks.
ax[0].fill_between(
x, y[column], df_initial_linear_regresion, color="peachpuff", where=x > time_initial_linregress)
ax[1].fill_between(
x, y[column], df_final_linear_regresion, color="wheat", where=x < event_number_time_final_linregress)
ax[0].xaxis.set_major_locator(MultipleLocator(60))
ax[0].xaxis.set_minor_locator(MultipleLocator(30))
ax[1].xaxis.set_major_locator(MultipleLocator(60))
ax[1].xaxis.set_minor_locator(MultipleLocator(30))
ax[0].set_title(f'{column}', fontsize=12)
ax[0].set_xlabel('Time (s)', fontsize=9)
ax[1].set_xlabel('Time (s)', fontsize=9)
ax[0].set_ylabel('Fura2 fluorescencia (a.u)', fontsize=9)
ax[1].set_ylabel('Fura2 fluorescencia (a.u)', fontsize=9)
if y_min_value != "" and y_max_value != "":
plt.ylim(y_min_value, y_max_value)
else:
pass
ax[0].vlines(x=peaks*adquisiton_time, ymin=y[column][peaks] - properties["prominences"],
ymax=y[column][peaks], color="blue")
ax[0].hlines(y=properties["width_heights"], xmin=properties["left_ips"]*adquisiton_time,
xmax=properties["right_ips"]*adquisiton_time, color="blue")
ax[1].vlines(x=peaks*adquisiton_time, ymin=y[column][peaks] - properties["prominences"],
ymax=y[column][peaks], color="blue")
ax[1].hlines(y=properties["width_heights"], xmin=properties["left_ips"]*adquisiton_time,
xmax=properties["right_ips"]*adquisiton_time, color="blue")
ax[0].plot(peaks*adquisiton_time, y[column]
[peaks], "o", color="red")
ax[1].plot(peaks*adquisiton_time, y[column]
[peaks], "o", color="red")
ax[0].plot(x, df_initial_linear_regresion,
color="saddlebrown", label="AUC Initial linear regresion")
ax[1].plot(x, df_final_linear_regresion,
color="darkgoldenrod", label="AUC Final linear regresion")
ax[0].legend()
ax[1].legend()
plt.rcParams.update({'figure.max_open_warning': 0})
temporal_images = os.path.join(folder, non_space_filename)
Path(temporal_images).mkdir(parents=True, exist_ok=True)
fig_name = os.path.join(temporal_images, f"{column}.png")
plt.savefig(fig_name, dpi=100)
# plt.show()
print("")
print(f"Number of analyzed cells: {ca_cal.analyzed_cell_number(sheet)}.")
for folder, subfolders, files in os.walk(folder):
for subfolder in subfolders:
if subfolder == non_space_filename:
wb = openpyxl.load_workbook(file_route)
sheet = wb.active
for temporal_image in os.listdir(subfolder):
temporal_image_route = os.path.join(
folder, subfolder, temporal_image)
img = openpyxl.drawing.image.Image(
temporal_image_route)
img_cell = sheet.cell(
row=row_num_plots, column=column_num_plots).coordinate
img.anchor = img_cell
sheet.add_image(img)
row_num_plots += 26
filename = f"{non_space_filename}_analyzed.xlsx"
final_file_max_row = sheet.max_row
final_file_max_column = sheet.max_column
for row in range(1, final_file_max_row + 1):
for column in range(1, final_file_max_column):
if sheet.cell(row=row, column=column).value:
float_formating = sheet.cell(
row=row, column=column).coordinate
if type(sheet[float_formating].value) == float:
ca_ex_st.style_number(float_formating, sheet)
elif type(sheet[float_formating].value) == int:
ca_ex_st.style_time(float_formating, sheet)
for row in range(1, final_file_max_row + 1):
if sheet.cell(row=row, column=column_num_data + 1).value:
parameter_sample = sheet.cell(
row=row, column=column_num_data + 1).coordinate
ca_ex_st.style_oscillation_calculated_parameters_header(
parameter_sample, sheet)
ca_ex_st.colum_max_widths_oscillation_calcium_experiments(
sheet, column_num_data)
wb.save(filename)
os.remove(file_route)
print(f"{filename} has been saved.")