You can use the pandas all method and Boolean logic. As EdChum commented, I am a bit unclear still on your exact example, but a similar example is:
In [1]: df = DataFrame([[1,2],[-3,5]], index=[0,1], columns=['a','b'])
In [2]: df
Out [2]:
a b
0 1 2
1 -3 5
In [3]: msk = (df>1) & (df<5)
In [4]: msk
Out [4]:
a b
0 False True
1 False False
In [5]: msk.all(axis=1)
Out [5]:
0 False
1 False
dtype: bool
If you wanted to index the original dataframe by the mask you could do:
In [6]: df[msk]
Out [6]:
a b
0 NaN 2
1 NaN NaN
Or as you originally indicated, rows where all the rows are true:
In [7]: idx = msk.all(axis=1)
In [8]: df[idx]
Out [8]:
Empty DataFrame
Columns: [a,b]
Index: []
Or if one row was true
In [9]: idx[0] = True
In [10]: df[idx]
Out [10]:
a b
0 1 2
For the original question after clarification from the comments, where we want different filtering criteria for different columns:
In [10]: msk1 = df[['a']] < 0
In [11]: msk2 = df[['b']] > 3
In [12]: msk = concat((msk1, msk2), axis=1)
In [12]: slct = msk.all(axis=1)
In [13]: df.ix[slct]
Out [13]:
a b
1 -3 5
You can use the pandas all method and Boolean logic. As EdChum commented, I am a bit unclear still on your exact example, but a similar example is:
In [1]: df = DataFrame([[1,2],[-3,5]], index=[0,1], columns=['a','b'])
In [2]: df
Out [2]:
a b
0 1 2
1 -3 5
In [3]: msk = (df>1) & (df<5)
In [4]: msk
Out [4]:
a b
0 False True
1 False False
In [5]: msk.all(axis=1)
Out [5]:
0 False
1 False
dtype: bool
If you wanted to index the original dataframe by the mask you could do:
In [6]: df[msk]
Out [6]:
a b
0 NaN 2
1 NaN NaN
Or as you originally indicated, rows where all the rows are true:
In [7]: idx = msk.all(axis=1)
In [8]: df[idx]
Out [8]:
Empty DataFrame
Columns: [a,b]
Index: []
Or if one row was true
In [9]: idx[0] = True
In [10]: df[idx]
Out [10]:
a b
0 1 2
For the original question after clarification from the comments, where we want different filtering criteria for different columns:
In [10]: msk1 = df[['a']] < 0
In [11]: msk2 = df[['b']] > 3
In [12]: msk = concat((msk1, msk2), axis=1)
In [12]: slct = msk.all(axis=1)
In [13]: df.ix[slct]
Out [13]:
a b
1 -3 5
df[df[['DE', 'GA', 'ID']].all(axis=1) * (1 - df[['FL', 'IA']]).all(axis=1)]
The hard part here is understanding why you're using even/odd column positions to determine the treatment. Based on your code, it looks like you want columns 0, 2, and 4 to actually be 1 minus their current values. However, based on what you claim is the expected output, it actually seems like you want colums 1 and 3 to have 1 minus their current values.
My code above reflects the latter assumption. The general idea still works; just tune it to reflect whatever columns you actually need to have 1 minus the value of, assuming you make your desired output more rigorously defined.
Probably that needs to be cleaned up and turned into a proper helper function first that explicitly shows which columns need to have 1 minus their value, versus which columns can be left alone.
I am learning about Pandas. I have a df similar to this:
| Count | orange | grey | black | violet |
|---|---|---|---|---|
| 234 | 0 | 33 | 45 | 0 |
| 453 | 0 | 0 | 23 | 0 |
I want to change all of the "color" columns' values so that 0 values are False and values > 0 are True.
I know df[df[column name] == 0] = False or df.loc[df[column name] == 0, column name] = False does what I want for 1 column and that I can use a similar operation on the entire dataframe rather than just one column. But when I try to do it to more than 1 column I get an error. How can I do this kind of masking operation to multiple columns?
This is essentially what I tried:
df[df[['orange', 'grey', 'black']] == 0] = False
This is the error I received:
TypeError: Cannot do inplace boolean setting on mixed-types with a non np.nan value
python - How to use 'mask' in Pandas for multiple columns? - Stack Overflow
python - Pandas dataframe boolean mask on multiple columns - Stack Overflow
python - Multiple Masks on Dataframe - Pandas - Stack Overflow
python - Pandas Mask on multiple Conditions - Stack Overflow
Use .assign and np.where
data = data.assign(
ArrDelay=np.where(data["ArrDelay"].lt(0), 0, data["ArrDelay"]),
DepDelay=np.where(data["DepDelay"].lt(0), 0, data["DepDelay"])
)
print(data)
FlightNum ArrDelay DepDelay TailNum Month Dest
0 3232 3 0 432G 1 ORX
1 4342 0 4 476N 2 TOL
2 6344 0 0 643G 3 JFK
3 7564 5 13 653A 4 CVO
You can select the columns to update, clip the values lower than 0, and update the DataFrame in place.
cols = ['ArrDelay', 'DepDelay']
df.update(df[cols].clip(lower=0))
Alternative if you prefer a mask:
df.update(df[cols].mask(df[cols].lt(0), 0))
Output:
FlightNum ArrDelay DepDelay TailNum Month Dest
0 3232 3 0 432G 1 ORX
1 4342 0 4 476N 2 TOL
2 6344 0 0 643G 3 JFK
3 7564 5 13 653A 4 CVO
You can use the results of your apply statement to boolean index select from the original dataframe:
results = df[["A","B"]].apply(lambda x: x.abs()-5*df['d'+x.name] > 0)
Which returns your boolean array above:
A B
0 False True
1 True True
2 True True
3 True False
Now, you can use this array to select rows from your original datafame as follows:
Select where A is True:
df[results.A]
A B dA dB
1 2 -4.0 0.263 0.357
2 5 5.0 0.382 0.397
3 -4 -0.5 0.330 0.115
Select where either A or B are true:
df[results.any(axis=1)]
A B dA dB
0 -1 3.0 0.310 0.080
1 2 -4.0 0.263 0.357
2 5 5.0 0.382 0.397
3 -4 -0.5 0.330 0.115
Select where all the columns true:
df[results.all(axis=1)]
A B dA dB
1 2 -4.0 0.263 0.357
2 5 5.0 0.382 0.397
Using the underlying array data, a vectorized approach would be like so -
cols = ['A','B'] # list holding relevant column names
dcols = ['d'+i for i in cols]
out = np.abs(df[cols].values) - 5*df[dcols].values > 0
Sample run -
In [279]: df
Out[279]:
A B dA dB
0 -1 3.0 0.310 0.080
1 2 -4.0 0.263 0.357
2 5 5.0 0.382 0.397
3 -4 -0.5 0.330 0.115
In [280]: cols = ['A','B'] # list holding relevant column names
...: dcols = ['d'+i for i in cols]
...: out = np.abs(df[cols].values) - 5*df[dcols].values > 0
...:
In [281]: out
Out[281]:
array([[False, True],
[ True, True],
[ True, True],
[ True, False]], dtype=bool)
To extract out the valid ones by setting the invalid ones as NaNs, we could use np.where -
In [293]: df[cols] = np.where(out, df[cols], np.nan)
In [294]: df
Out[294]:
A B dA dB
0 NaN 3.0 0.310 0.080
1 2.0 -4.0 0.263 0.357
2 5.0 5.0 0.382 0.397
3 -4.0 NaN 0.330 0.115
Also, we could get the rows with all matches with all() reduction along each row -
In [283]: np.flatnonzero(out.all(axis=1))
Out[283]: array([1, 2])