[Excel & Python] 비선형 검정곡선 : Weighted 4-Parameter Equation

비선형 검정곡선 4-parameter equation에 가중치 (weight) 추가 방법   

이전 글에서 엑셀의 해 찾기 방법과 파이썬 코딩으로 비선형 검정곡선 4-parameter equation을 만들어 보았다.

2025.03.30 - [기기분석 데이터] - [Excel] 비선형 검정곡선 만들기 : 4-Parameter Equation

[Excel] 비선형 검정곡선 만들기 : 4-Parameter Equation

2025.04.03 - [기기분석 데이터] - [Excel+Python] 비선형 검정곡선 만들기 : 4-Parameter Equation

[Excel+Python] 비선형 검정곡선 만들기 : 4-Parameter Equation

앞서 작성된 예시 파일의 내용을 살펴보면 검정곡선의 정확성 결과에서 낮은 농도의 오차가 크게 발생되는 것이 확인된다. 
이런 경우에는 수식으로 인해서 특정 농도 영역에서 상대적으로 큰 오차가 발생되는 문제가 나타난다. 

Level	Concentration (X)	Response (Y)	Back-calculated Concentration (X')	X'_Mean	Recovery (X'/X)
STD1	0.1	2.912	0.10394375	0.10492109	105%
		2.917	0.10589843
STD2	0.05	2.579	0.0452431	0.04874916	97%
		2.654	0.05225522
STD3	0.025	2.130	0.02322094	0.02452360	98%
		2.212	0.02582626
STD4	0.0125	1.651	0.01315076	0.01305324	104%
		1.638	0.01295573
STD5	0.00625	1.073	0.00651074	0.00608358	97%
		0.973	0.00565642
STD6	0.003125	0.585	0.00280178	0.00307601	98%
		0.666	0.00335024
STD7	0.0015625	0.463	0.00200701	0.00166770	107%
		0.356	0.00132839
STD8	0.00078125	0.266	0.00075478	0.00064959	83%
		0.234	0.0005444
STD9	0.00039063	0.228	0.00050414	0.00039663	102%
		0.197	0.00028912
STD10	0.00019531	0.176	0.00013058	0.00027311	140%
		0.215	0.00041565

이와 같은 문제점을 해결하기 위해서 사용되는 방법으로 
검정곡선에 가중치 (Weight)를 추가해서 변형된 수식을 농도 계산에 사용한다. 
가중치에 대한 자세한 내용은 이전 글에서 확인할 수 있다. 

2023.12.24 - [기기분석 데이터] - [Excel] 검정 곡선 (Calibration Curve) 교정 : 가중치 (Weighting)

[Excel] 검정 곡선 (Calibration Curve) 교정 : 가중치 (Weighting)

검정곡선의 최적화를 위해서 가중치 설정이 4-parameter equation에 어떻게 적용되어 계산되는지 알아보고,
엑셀의 해 찾기 기능과 파이썬 방법 간의 차이점이 있는지 확인해 보았다.

---

엑셀 "해 찾기" 방법 

엑셀 "해 찾기" 기능으로 수식의 상수값을 찾는 과정은
개별 데이터의 잔차를 확인하고, 모든 데이터의 잔차가 가장 낮은 값이 되도록 만드는 것이다.

모든 점의 잔차가 동일하게 최소가 되도록 설정하는 과정에서
낮은 농도의 잔차는 큰 농도의 잔차 보다 농도로 변환하면 더 큰 오차가 발생된다. 
이와 같은 오류를 수정하기 위해서 낮은 농도 잔차를 더 최소가 되도록 수정하는 것이 가중치 설정이다.

가중치가 적용된 최적의 상수를 확인하는 과정은
앞서 설명한 일반 수식의 상수를 확인하는 방법과 동일하지만, 
차이점은 가중치가 적용된 잔차를 사용한다는 점이다. 

예를 들어, 가중치 "1/x"을 수식에 적용한다면
계산된 잔차 (residual)에 1/x 값을 곱해서 가중치가 적용된 잔차 (weighted residual)를 계산한다.

$$ Weighted \ Residual = Residual \times \dfrac{1}{x} $$

Level	Concentration (X)	Response (Y)	Y_Mean	Predicted Y	Residual	Weighting Value (1/x)	Weghted Residual
STD1	0.1000000	2.912	2.915	2.864	0.05018	10	0.50
		2.917
STD2	0.0500000	2.579	2.617	2.624	-0.00754	20	-0.15
		2.654
STD3	0.0250000	2.130	2.171	2.202	-0.03090	40	-1.24
		2.212
STD4	0.0125000	1.651	1.645	1.621	0.02362	80	1.89
		1.638
STD5	0.0062500	1.073	1.023	1.042	-0.01858	160	-2.97
		0.973
STD6	0.0031250	0.585	0.626	0.623	0.00265	320	0.85
		0.666
STD7	0.0015625	0.463	0.410	0.385	0.02417	640	15.47
		0.356
STD8	0.0007813	0.266	0.250	0.269	-0.01872	1280	-23.96
		0.234
STD9	0.0003906	0.228	0.213	0.216	-0.00304	2560	-7.78
		0.197
STD10	0.0001953	0.176	0.196	0.192	0.00340	5120	17.40
		0.215

그 다음으로 가중치가 적용된 잔차 제곱 합 (Weighted RSS : Residual Sum of Square)을 계산하고, 
해 찾기 기능을 이용해서 가중치가 적용된 잔차 제곱 합 (Weighted RSS)이 최소가 되도록 상수를 찾아낸다.   

가중치가 적용된 잔차의 계산된 결과를 보면 높은 농도의 값보다 낮은 농도들의 값이 더 증가하는 것이 확인된다. 
낮은 농도의 잔차가 더 큰 값으로 반영되어 수식이 낮은 농도의 점과 더 가까워지도록 상수 값이 결정된다.  

Level	Concentration (X)	Response (Y)	Back-calculated Concentration (X')	X'_Mean	Recovery (X'/X)
STD1	0.1000000	2.912	0.12471138	0.1264	126%
		2.917	0.12805948
STD2	0.0500000	2.579	0.04557636	0.0495	99%
		2.654	0.05340232
STD3	0.0250000	2.130	0.02275342	0.0240	96%
		2.212	0.02534124
STD4	0.0125000	1.651	0.01293212	0.0128	103%
		1.638	0.01274381
STD5	0.0062500	1.073	0.00651732	0.0061	98%
		0.973	0.00568588
STD6	0.0031250	0.585	0.00287110	0.0031	101%
		0.666	0.00341839
STD7	0.0015625	0.463	0.00206867	0.0017	110%
		0.356	0.00137012
STD8	0.0007813	0.266	0.00076217	0.0006	83%
		0.234	0.00053147
STD9	0.0003906	0.228	0.00048651	0.0004	93%
		0.197	0.00023852
STD10	0.0001953	0.176	2.9177E-05	0.0002	106%
		0.215	0.00038637

가중치가 적용된 잔차로 계산된 새로운 상수를 이용해서 농도를 계산해 보면,
이전에 비해서 가장 낮은 농도의 오차가 Recovery 140% → 106%으로 감소된 것을 확인할 수 있다. 
하지만, 가장 높은 농도의 오차는 Recovery 105% → 126%으로 증가된 것이 확인된다.
가중치 설정 1/x, 1/y 은 낮은 농도 오차를 감소시키는데 효과가 있지만, 높은 농도의 오차를 증가시킬 수 있다.
그래서 가중치 설정은 검정곡선의 모든 농도의 오차가 최소가 되도록 설정하는 것이 중요하다.   

가중치 설정 전/후 수식의 그래프

가중치 설정 전과 후를 비교해 보면 큰 차이를 보이지 않고, 미세한 부분의 차이를 보이고 있는데
두드러진 부분은 가장 높은 농도에서 육안으로 확인된다. 
하지만, 농도 계산에 대한 차이는 가장 낮은 농도에서 나타나고 있어서 
검정곡선의 적합성 내용은 농도 계산으로 재확인하는 것이 필요하다. 

---

엑셀 "파이썬" 방법  

엑셀의 파이썬 기능을 이용해서 자동 계산을 수행할 수 있다고 이전 글에서 소개했다. 
파이썬 코딩으로 4-parameter equation의 상수를 찾을 수 있었는데
가중치가 적용된 수식의 상수를 찾는 코딩은 이전과 어떻게 다른지 확인했다.
아래 내용은 가중치가 적용된 상수를 찾는 파이썬 코딩 내용이다. 
가중치가 내용이 추가되면서 코딩 내용도 추가되었다.
추가된 내용은 빨간색으로 표기되었다.  

from scipy.optimize import curve_fit

# 수식 정의
def logistic4(x, A, D, B, C):
      return D + (A - D) / (1 + (x / C) ** B)

# 데이터 가져오기
data = xl("B29:D48")
data.columns = ["X", "C", "Y"]
x_data = data["X"].dropna().to_numpy() # Excel 열 'X' 데이터
y_data = data["Y"].dropna().to_numpy() # Excel 열 'Y' 데이터

weight0 = xl("O29:O48")
weight0.columns = ["W"]
weight0 = weight0["W"].dropna().to_numpy() # Excel 열 'weight' 데이터
weight = np.sqrt(1/weight0)

A0 = xl("S45")
D0 = xl("S46")
B0 = xl("S47")
C0 = xl("S48")
 
# 초기값 설
initial_guesses = [A0, D0, B0, C0]

# 회귀분석 실행
params1, covariance = curve_fit(logistic4, x_data, y_data, p0=initial_guesses, sigma = weight)

#결과 반환 (A, D, B, C)
params1

빨간색 글자의 내용을 살펴보면
첫 문단의 빨간색 글자는 가중치 설정 값을 가져오고 
회귀분석 실행에 맞게 변경한 내용이다. 
그다음 빨간색 글자는 회귀분석 실행에 가중치가 추가되도록 설정된 내용이다. 

회귀분석 실행에서 가중치가 반영되어 계산되는 설정이 있다. 
아래 내용은 "curve_fit"의 설정 내용으로 가중치 설정 "sigma" 부분의 설명이다. 

curve_fit(f, xdata, ydata, p0=None, sigma=None)

sigma : None or scalar or M-length sequence or MxM array, optional
Determines the uncertainty in ydata. If we define residuals as r = ydata - f(xdata, *popt), then the interpretation of sigma depends on its number of dimensions:
A scalar or 1-D sigma should contain values of standard deviations of errors in ydata. In this case, the optimized function is chisq = sum((r / sigma) ** 2).

참고: https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html

가중치 값이 적용되는 계산 내용이 설명되어 있는데 
그 값이 그대로 곱해지는 것이 아니라 잔차를 나누 값에 제곱이 적용된다. 
그래서 기존 값이 변형 없이 적용되기 위해서 
엑셀에서 값을 가져오고 제곱근을 해서 함수에 적용되도록 코딩을 추가했다. 

동일한 데이터에서 같은 가중치 설정으로 해 찾기 방법과 파이썬 결과를 비교했다.
수식의 결과 비교 내용은 다음과 같다.   

Parameter		Excel's Solver	Python
a	Min Y	0.174236	0.174236
d	Max Y	3.078345	3.078345
b	Hill's slope of the curve	1.220849	1.220849
c	Point of inflection (>0)	0.012577	0.012577
R2	Coefficient of determination	0.999729	0.999729
wRSS	Weighted Residual Sum of Square	1191.768	1191.765

수식의 상수 값이 동일하게 계산된 것을 확인할 수 있다. 
동일한 알고리즘이 적용된 결과로 가중치 설정 이전에도 동일한 결과를 얻을 수 있었고
가중치를 추가한 결과에서도 같은 결과를 얻을 수 있었다. 

해찾기와 파이썬 방법으로 가중치 설정된 수식의 그래프

가중치 설정은 검정곡선의 오류를 수정하는 방법으로 비선형 수식에서 계산되는 방법과 
해 찾기 방법과 파이썬 방법의 결과 차이가 있는지 확인하였다. 
해당 내용의 자세한 계산 내용은 첨부 파일로 확인할 수 있습니다.

Weighted Regression_4 parameter equation_V1.xlsx

0.08MB