CODEWITHSUNDEEP
pythonpandas
Matt
Matt

Reputation: 93

pandas apply condition to column value based on another column

I have a df that looks like the below:

        date   2017-10-07  2017-10-08
id                                                                 
1    2017-09-07      46.0         5.0   
2    2017-10-07       1.0         0.0   
3    2017-11-07     123.0         0.0   
4    2017-10-07       0.0         0.0   
5    2017-10-08       0.0         0.0

and want to be able to lookup the date value from the 'date' column, and if it matches the column name of the columns with date titles, change that specific value in the df.

For instance, in the df above, the output would look like:

        date   2017-10-07  2017-10-08
id                                                                 
1    2017-09-07      46.0         5.0   
2    2017-10-07       1.0         0.0   
3    2017-11-07     123.0         0.0   
4    2017-10-07   "CHANGED"       0.0   
5    2017-10-08       0.0      "CHANGED"

I can do this in a for loop over each column and row, but it is time consuming and I know there has to be a better way.

Thanks in advance!

Upvotes: 0

Views: 469

Answers (2)

BENY
BENY

Reputation: 323226

By using stack and unstack

df1=df.reset_index().melt(['id','date'])
df1.loc[df1.date==df1.variable,'value']='changed'
df1.set_index(['id','variable','date']).unstack(-2)
Out[189]: 
                   value           
variable      2017-10-07 2017-10-08
id date                            
1  2017-09-07         46          5
2  2017-10-07    changed          0
3  2017-11-07        123          0
4  2017-10-07    changed          0
5  2017-10-08          0    changed

Upvotes: 1

cs95
cs95

Reputation: 402263

You can use numpy broadcasting, perform assignment, and reassign the result back. 

v = df.values[:, 1:]
v[df.date.values[:, None] == df.columns[1:].values] = 'CHANGED'
df.iloc[:, 1:] = v

df

          date 2017-10-07 2017-10-08
id                                  
1   2017-09-07         46          5
2   2017-10-07    CHANGED          0
3   2017-11-07        123          0
4   2017-10-07    CHANGED          0
5   2017-10-08          0    CHANGED

Upvotes: 3

Related Questions