I need to replace the value in columns ending in _4 with an updated value based on what value they include in the other columns. if the first 3 columns contain 1, the fourth should be zero. if the first three columns contain zero, then the fourth column should be 1.
in my code below, I want to assign this counts var to have the same name as the common column (i.e. a or b) - and then replace the x'_4' column in the original dataframe with this new counts column However its also not counting correctly, as the counts for a and b are currently. any comments welcome. Thankyou
import pandas as pd
import numpy as np
df = pd.DataFrame({ 'a_1':[1, 0, 0, 0],
'a_2':[0, 0, 0, 1],
'a_3':[0, 0, 0, 0],
'a_4':[1, 0, 1, 1],
'b_1':[0, 0, 0, 1],
'b_2':[0, 0, 0, 1],
'b_3':[1, 1, 0, 0],
'b_4':[0, 1, 0, 1]})
my current code look like this
out = []
counts = []
col_list = ('a','b')
for col in col_list:
d = df.loc[:,df.columns.str.startswith(col)]
dat = df.loc[:, ~df.columns.str.endswith('4')]
counts = dat[(dat.isin([1]).any(1).astype(int))|(d.isin([1]).all(0).astype(int))]
counts^=1
counts_df = pd.DataFrame(counts)
out.append(counts_df)
the overwritten columns in the original dataframe should look like this
a_4 b_4
0 0 0
1 1 0
2 1 1
3 0 0
try via filter() and get column names like '_4':
cols=df.filter(like='_4').columns
Finally:
df[cols]=(df.filter(regex='_[0-3]')
.rename(columns=lambda x:x.split('_')[0])
.groupby(axis=1,level=0).sum().eq(0).astype(int))
output of df:
a_1 a_2 a_3 a_4 b_1 b_2 b_3 b_4
0 1 0 0 0 0 0 1 0
1 0 0 0 1 0 0 1 0
2 0 0 0 1 0 0 0 1
3 0 1 0 0 1 1 0 0