Python Pandas replace replaces wrong

Question

My DataFrame looks like this:

Date  WaterTemp
1       21.4
2       20.8
3       20.4
4       20.0
5       19.9
6       20.5
7       20.8
8       21.4
9       21.7
10       22.1
11       21.9
12       21.3
13       21.5
14       21.6
15       21.4
16       21.7
17       22.4
18       22.4
19       22.5
20       22.6
21       23.1
22       23.8
23       23.8
24       23.1
25       22.9
26       23.4
27       23.6
28       23.4
29       24.1
30       24.6
31       24.1
32       23.9
33       23.6
34       23.3
35       23.2
36       23.4
37       23.1
38       22.3
39       22.4
40       22.4

I want to replace the values of WaterTemp like this:

wt = df['WaterTemp']

for line in wt:
        if line <= 11.5:
            WaterTemp = WaterTemp.replace(line, "LWT")
        elif line >= 22.5:
            WaterTemp = WaterTemp.replace(line, "HWT")
        else:
            WaterTemp = WaterTemp.replace(line, "")

But the Result as .csv looks like this:

1,
2,
3,
4,
5,
6,
LWT,
LWT,
LWT,
LWT,HWT
LWT,HWT
,
,
,
,
,
,HWT
,HWT
,HWT
,HWT
,HWT
HWT,HWT
HWT,HWT
HWT,HWT
HWT,HWT
HWT,HWT
HWT,HWT
HWT,HWT
29,HWT
30,HWT
31,HWT
32,HWT
33,HWT
34,HWT
35,HWT
36,HWT
37,HWT
38,HWT
39,HWT
40,HWT

So it changes the Date values from 7 to 28.

I hope you can help me soon.

Answer 1

This is slightly tricky in that normally I'd suggest using several statements with loc but once you set a number of rows to str you introduce mixed dtypes and you can't compare str with float. Anyway you can achieve this using nested np.where statements:

In [61]:
df['WaterTemp'] = np.where(df['WaterTemp'] <= 11.5, 'LWT', np.where(df['WaterTemp'] >= 22.5, 'HWT', ''))
df

Out[61]:
    Date WaterTemp
0      1          
1      2          
2      3          
3      4          
4      5          
5      6          
6      7          
7      8          
8      9          
9     10          
10    11          
11    12          
12    13          
13    14          
14    15          
15    16          
16    17          
17    18          
18    19       HWT
19    20       HWT
20    21       HWT
21    22       HWT
22    23       HWT
23    24       HWT
24    25       HWT
25    26       HWT
26    27       HWT
27    28       HWT
28    29       HWT
29    30       HWT
30    31       HWT
31    32       HWT
32    33       HWT
33    34       HWT
34    35       HWT
35    36       HWT
36    37       HWT
37    38          
38    39          
39    40        

Answer 2

I'd use pd.cut

df.WaterTemp = pd.cut(df.WaterTemp,
                      [0, 11.5, 22.5, 100],
                      labels=['LWT', '', 'HWT'])

---

    Date WaterTemp
0      1          
1      2          
2      3          
3      4          
4      5          
5      6          
6      7          
7      8          
8      9          
9     10          
10    11          
11    12          
12    13          
13    14          
14    15          
15    16          
16    17          
17    18          
18    19          
19    20       HWT
20    21       HWT
21    22       HWT
22    23       HWT
23    24       HWT
24    25       HWT
25    26       HWT
26    27       HWT
27    28       HWT
28    29       HWT
29    30       HWT
30    31       HWT
31    32       HWT
32    33       HWT
33    34       HWT
34    35       HWT
35    36       HWT
36    37       HWT
37    38          
38    39          
39    40