[FIXED] Lambda and loc function for dataframes with multiple conditions

Issue

I've this problem in my python app. I need to add a the latest column named "ValueOverlap" (I made up this values) to my dataframe with multiple conditions and calculated result.

Date	Open	High	Low	Close	PrevHigh	PrevLow	Direction	PrevDirection	ValueOverlap
31/12/1995	0,2879	0,3170	0,1429	0,1864	0,4475	0,2824	Bearish	Bearish	38
31/12/1996	0,1886	0,2640	0,1138	0,1172	0,3170	0,1429	Bearish	Bearish	13
31/12/1997	0,1217	0,3906	0,1205	0,3655	0,2640	0,1138	Bullish	Bearish	38
31/12/1998	0,3761	1,0536	0,2857	0,9180	0,3906	0,1205	Bullish	Bullish	47
31/12/1999	0,9364	1,3426	0,2433	0,2656	1,0536	0,2857	Bearish	Bullish	34
31/12/2000	0,2656	0,4843	0,2578	0,3911	1,3426	0,2433	Bullish	Bearish	47
31/12/2001	0,3938	0,4673	0,2386	0,2559	0,4843	0,2578	Bearish	Bullish	46
31/12/2002	0,2564	0,4466	0,2271	0,3816	0,4673	0,2386	Bullish	Bearish	32
31/12/2003	0,3848	1,2423	0,3782	1,1500	0,4466	0,2271	Bullish	Bullish	45
31/12/2004	1,1568	2,6950	1,1179	2,5675	1,2423	0,3782	Bullish	Bullish	37
31/12/2005	2,5850	3,3271	1,7914	3,0300	2,6950	1,1179	Bullish	Bullish	46
31/12/2006	3,0818	7,2486	2,9250	7,0743	3,3271	1,7914	Bullish	Bullish	32
31/12/2007	7,1168	7,1521	2,8264	3,0482	7,2486	2,9250	Bearish	Bullish	30
31/12/2008	3,0671	7,6411	2,7929	7,5261	7,1521	2,8264	Bullish	Bearish	36
31/12/2009	7,6225	11,6664	6,7946	11,5200	7,6411	2,7929	Bullish	Bullish	16
31/12/2010	11,6300	15,2393	11,0893	14,4643	11,6664	6,7946	Bullish	Bullish	30
31/12/2011	14,6214	25,1811	14,6071	19,0061	15,2393	11,0893	Bullish	Bullish	48
31/12/2012	19,7793	20,5407	13,7536	20,0364	25,1811	14,6071	Bullish	Bullish	48
31/12/2013	19,8457	29,9375	17,6268	27,5950	20,5407	13,7536	Bullish	Bullish	16
31/12/2014	27,8475	33,6350	23,0000	26,3150	29,9375	17,6268	Bearish	Bullish	28
31/12/2015	25,6525	29,6725	22,3675	28,9550	33,6350	23,0000	Bullish	Bearish	44
31/12/2016	28,9500	44,3000	28,6900	42,3075	29,6725	22,3675	Bullish	Bullish	20
31/12/2017	42,5400	58,3675	36,6475	39,4350	44,3000	28,6900	Bearish	Bullish	37
31/12/2018	38,7225	73,4925	35,5000	73,4125	58,3675	36,6475	Bullish	Bearish	47
31/12/2019	74,0600	138,7900	53,1525	132,6900	73,4925	35,5000	Bullish	Bullish	16
31/12/2020	133,5200	182,1300	116,2100	177,5700	138,7900	53,1525	Bullish	Bullish	16
31/12/2021	177,8300	182,9400	125,8700	129,9300	182,1300	116,2100	Bearish	Bullish	34
31/12/2022	130,2800	199,6200	124,1700	193,6000	182,9400	125,8700	Bullish	Bearish	21

I test with df.loc function with this code:

df.loc[(df['PrevDirection'] == 'Bullish') & (df['Direction'] == 'Bullish'), 'Value_Overlap'] = min((df['PrevHigh'] - df['Low']) / (df['High'] - df['Low']) * 100, (df['PrevHigh'] - df['Low']) / (df['PrevHigh'] - df['PrevLow']) * 100)
df.loc[(df['PrevDirection'] == 'Bearish') & (df['Direction'] == 'Bearish'), 'Value_Overlap'] = min((df['High'] - df['PrevLow']) / (df['High'] - df['Low']) * 100, (df['High'] - df['PrevLow']) / (df['PrevHigh'] - df['PrevLow']) * 100)

And with lambda funtion with this other code:

df['Value_Overlap'] = df.apply(lambda x: min((df['PrevHigh'] - df['Low']) / (df['High'] - df['Low']) * 100, (df['PrevHigh'] - df['Low']) / (df['PrevHigh'] - df['PrevLow']) * 100) if x['PrevDirection'] == 'Bullish' and x['Direction'] == 'Bullish' else min((df['High'] - df['PrevLow']) / (df['High'] - df['Low']) * 100, (df['High'] - df['PrevLow']) / (df['PrevHigh'] - df['PrevLow']) * 100) if x['PrevDirection'] == 'Bearish' and x['Direction'] == 'Bearish' else 0, axis=1)

In booth codes are the same error: "ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()."

Any idea to solve this problem? Thanks a lot in advance!

Solution

You can't use min with a list of values, you have to use a vectorized min function:

import numpy as np

v1 = (df['PrevHigh'] - df['Low']) / (df['High'] - df['Low']) * 100
v2 = (df['PrevHigh'] - df['Low']) / (df['PrevHigh'] - df['PrevLow']) * 100
bullish = np.min([v1, v2], axis=0)

v1 = (df['High'] - df['PrevLow']) / (df['High'] - df['Low']) * 100
v2 = (df['High'] - df['PrevLow']) / (df['PrevHigh'] - df['PrevLow']) * 100
bearish = np.min([v1, v2], axis=0)

m = (df['PrevDirection'] == 'Bullish') & (df['Direction'] == 'Bullish')
df.loc[m, 'Value_Overlap'] = bullish[m]

m = (df['PrevDirection'] == 'Bearish') & (df['Direction'] == 'Bearish')
df.loc[m, 'Value_Overlap'] = bearish[m]

I get a result but Value_Overlap are clearly not the same than ValueOverlap:

>>> df.loc[:, 'Direction':]
   Direction PrevDirection  ValueOverlap  Value_Overlap
0    Bearish       Bearish            38    -151.847365
1    Bearish       Bearish            13     104.766187
2    Bullish       Bearish            38            NaN
3    Bullish       Bullish            47      13.660633
4    Bearish       Bullish            34            NaN
5    Bullish       Bearish            47            NaN
6    Bearish       Bullish            46            NaN
7    Bullish       Bearish            32            NaN
8    Bullish       Bullish            45       7.915750
9    Bullish       Bullish            37     -14.662895
10   Bullish       Bullish            46     -99.101387
11   Bullish       Bullish            32      43.625020
12   Bearish       Bullish            30            NaN
13   Bullish       Bearish            36            NaN
14   Bullish       Bullish            16      17.375508
15   Bullish       Bullish            30      11.845724
16   Bullish       Bullish            48       5.978816
17   Bullish       Bullish            48     108.071685
18   Bullish       Bullish            16      23.669653
19   Bearish       Bullish            28            NaN
20   Bullish       Bearish            44            NaN
21   Bullish       Bullish            20  -12112.778236
22   Bearish       Bullish            37            NaN
23   Bullish       Bearish            47            NaN
24   Bullish       Bullish            16     -39.293262
25   Bullish       Bullish            16      -0.436205
26   Bearish       Bullish            34            NaN
27   Bullish       Bearish            21            NaN

Answered By - Corralien