Skip to content Skip to sidebar Skip to footer

Pandas Set Column Value Based On Matching Row Value And Column Name

I have a dataframe that looks likes this start end 2017-06-08 2018-04-08 2019-04-20 2018-04-20 2019-04-20 NaN NaN NaN 2018-0

Solution 1:

change the row value for matching column name

Here is my way if you want to match the column names from start and end columns:

m=(df.stack().reset_index(level=1)
 .set_index(0,append=True)['level_1'].unstack(fill_value=0).astype(bool)*1)
df.update(m)

print(df)
        start         end  2017-06-08  2018-04-20  2018-04-08  2019-04-20
0  2018-04-20  2019-04-20         0.0         1.0         0.0         1.0
1  2018-04-20  2019-04-20         0.0         1.0         0.0         1.0
2  2017-06-08  2018-04-08         1.0         0.0         1.0         0.0

Solution 2:

One way to melt first then compared , the pivot it back

s=df.reset_index().melt(['index','start','end'])
s['value']=s.variable.between(s.start,s.end).astype(int)
yourdf=s.pivot_table(index=['index','start','end'],columns='variable',values='value',aggfunc='first').reset_index(level=[1,2])
yourdf
variable       start         end  ...  2018-04-20  2019-04-20
index                             ...                        
0         2018-04-20  2019-04-20  ...           1           1
1         2018-04-20  2019-04-20  ...           1           1
2         2017-06-08  2018-04-08  ...           0           0
[3 rows x 6 columns]

Solution 3:

IIUC:

for col in df.columns[2:]:

    df[col] = np.where((df.start==col)|(df.end==col),1,np.nan)

Output:

0       start         end  2017-06-08  2018-04-20  2018-04-08  2019-04-20
1  2018-04-20  2019-04-20         NaN         1.0         NaN         1.0
2  2018-04-20  2019-04-20         NaN         1.0         NaN         1.0
3  2017-06-08  2018-04-08         1.0         NaN         1.0         NaN

Post a Comment for "Pandas Set Column Value Based On Matching Row Value And Column Name"