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