Pandas indexing by columns and mask

Viewed 134

I have the following dataframe:

import numpy as np
import pandas as pd


np.random.seed(398)

df = pd.DataFrame(np.random.randn(20, 3), columns=['a', 'b', 'c'])

And I wish to index the dataframe by column and row values, using the following indexing:

row_indexer = (df.index > 5)
col_indexer = (df > -1) & (df < 1)

And modify the values at these positions. So essentially all cases in the dataframe where the index is more than 5 and the value is between -1 and 1. This can be done in the following manner:

df[row_indexer[:, None] & col_indexer] = np.nan

with no issues. However, I now want to do this on a dataframe that contains another 3 columns called ["a_str", "b_str", "c_str"]. See the following:

str_cols = [i + "_str" for i in df.columns]
df[str_cols] = 'blank'

And using the exact same mask I used for the previous values, modify all of the _str columns in the dataframe. So using this same example, if the original dataframe was modified in the following manner for the numerical columns:

           a         b         c
0  -1.810802 -0.776590 -0.495147
1   1.381038  0.235168  2.334671
2   0.406279 -1.571401  1.011139
3  -1.200217 -1.013983 -0.040659
4   1.261759  0.863896  0.228914
5   0.696952 -1.384910  1.204492
6        NaN  1.180030       NaN
7  -2.027946       NaN       NaN
8        NaN       NaN       NaN
9        NaN       NaN       NaN
10 -1.389175  2.263662       NaN
11       NaN -1.077414       NaN
12       NaN -1.696859 -1.049889
13 -1.057308       NaN       NaN
14       NaN       NaN -1.206815
15       NaN       NaN       NaN
16  2.063715 -1.981503       NaN
17       NaN -1.022833  1.957646
18  1.315031       NaN  1.425088
19 -1.860641       NaN       NaN

Then the string columns would look like:

         a_str       b_str       c_str
0        blank       blank       blank
1        blank       blank       blank
2        blank       blank       blank
3        blank       blank       blank
4        blank       blank       blank
5        blank       blank       blank
6   new string       blank  new string
7        blank  new string  new string
8   new string  new string  new string
9   new string  new string  new string
10       blank       blank  new string
11  new string       blank  new string
12  new string       blank       blank
13       blank  new string  new string
14  new string  new string       blank
15  new string  new string  new string
16       blank       blank  new string
17  new string       blank       blank
18       blank  new string       blank
19       blank  new string  new string

The issue is I don't know how to do this on the original dataframe, I can only do it on a copy:

x = row_indexer[:, None] & col_indexer
x.columns = [i + "_str" for i in x.columns]

a = df[['a_str', 'b_str', 'c_str']]
a[x] = 'new string'

Full code to reproduce this found below:

import numpy as np
import pandas as pd


np.random.seed(398)

df = pd.DataFrame(np.random.randn(20, 3), columns=['a', 'b', 'c'])

row_indexer = (df.index > 5)
col_indexer = (df > -1) & (df < 1)
x = row_indexer[:, None] & col_indexer
df[row_indexer[:, None] & col_indexer] = np.nan
print(df)
str_cols = [i + "_str" for i in df.columns]
df[str_cols] = 'blank'

x.columns = [i + "_str" for i in x.columns]

a = df[['a_str', 'b_str', 'c_str']]
a[x] = 'new string'
print(a)

Edit:

Wanted to add that this can technically be solved with the following:

df[str_cols] = a

However, this would mean doubling the memory as a copy of the original dataframe would need to be taken, which ideally I would like to avoid. I'm pretty sure the memory is already doubled because of the mask (df[row_indexer[:, None] & col_indexer), so I want to avoid exploding the memory as much as possible

1 Answers

The basic approach to take in such scenarios is to create a boolean mask and get the view of this mask as numpy array, then select and mask the columns where you wish to substitute the values

mask = row_indexer[:, None] & col_indexer
df[str_cols] = df[str_cols].mask(mask.values, 'new string')

Why use mask.values instead of mask?

As pandas align data on indices, so the names of the columns in the mask must match with the names of str_cols in order for the mask method to work properly. To solve this problem there are two options either rename the columns in mask corresponding to the str_cols, the other option is to create a view of mask as numpy array (as the numpy array has no naming information) so the index alignment is no longer a problem and mask would work properly.


           a         b         c       a_str       b_str       c_str
0  -1.810802 -0.776590 -0.495147       blank       blank       blank
1   1.381038  0.235168  2.334671       blank       blank       blank
2   0.406279 -1.571401  1.011139       blank       blank       blank
3  -1.200217 -1.013983 -0.040659       blank       blank       blank
4   1.261759  0.863896  0.228914       blank       blank       blank
5   0.696952 -1.384910  1.204492       blank       blank       blank
6        NaN  1.180030       NaN  new string       blank  new string
7  -2.027946       NaN       NaN       blank  new string  new string
8        NaN       NaN       NaN  new string  new string  new string
9        NaN       NaN       NaN  new string  new string  new string
10 -1.389175  2.263662       NaN       blank       blank  new string
11       NaN -1.077414       NaN  new string       blank  new string
12       NaN -1.696859 -1.049889  new string       blank       blank
13 -1.057308       NaN       NaN       blank  new string  new string
14       NaN       NaN -1.206815  new string  new string       blank
15       NaN       NaN       NaN  new string  new string  new string
16  2.063715 -1.981503       NaN       blank       blank  new string
17       NaN -1.022833  1.957646  new string       blank       blank
18  1.315031       NaN  1.425088       blank  new string       blank
19 -1.860641       NaN       NaN       blank  new string  new string
Related