How to Select True/False Based on Column Value

How to select true/false based on column value?

Use a CASE. I would post the specific code, but need more information than is supplied in the post - such as the data type of EntityProfile and what is usually stored in it. Something like:

CASE WHEN EntityProfile IS NULL THEN 'False' ELSE 'True' END

Edit - the entire SELECT statement, as per the info in the comments:

SELECT EntityID, EntityName, 
CASE WHEN EntityProfile IS NULL THEN 'False' ELSE 'True' END AS HasProfile
FROM Entity

No LEFT JOIN necessary in this case...

select true/false based on col value in a group by

Here's a way to do this.

First create a test table and insert some values:

CREATE TABLE dbo.T
(
[counter] int not null,
[type] nvarchar(250) not null,
[name] nvarchar(50) not null
);

INSERT INTO dbo.T ([counter], [type], [name])
VALUES (1, N'Alpha', N'Bassem Akl'),
(2, N'Alpha', N'aaaaa'),
(3, N'Alpha', N'Akl Bassem'),
(4, N'Bravo', N'bbbbb'),
(5, N'Bravo', N'A Bassem'),
(6, N'Charlie', N'ccccc'),
(7, N'Charlie', N'ddddd');

Then use a CTE (common table expression) to determine if the name contains the text you are searching for. You don't have to use a CTE here, but it makes the overall SELECT statement easier to understand.

WITH cte AS
(
SELECT [counter], [type], IIF([name] LIKE N'%Bassem%', 1, 0) AS 'contains'
FROM dbo.T
)
SELECT SUM([counter]) AS 'SumCounter', [type], CAST(MAX([contains]) AS bit) as 'contains'
FROM cte
GROUP BY [type];

enter image description here

Note that Transact-SQL doesn't have a Boolean data type; instead it has a bit type. See Books Online > bit (Transact-SQL) -- https://msdn.microsoft.com/en-gb/library/ms177603.aspx

To check Pandas Dataframe column for TRUE/FALSE, if TRUE check another column for condition to satisfy and generate new column with values PASS/FAIL

If TRUE are boolean your solution is simplify by compare by df['Space'] only:

df['Space_Test'] = np.where(df['Space'],
np.where(df['Threshold'] <= 0.2, 'Pass', 'Fail'),'FALSE')
print (df)
Space Threshold Space_Test
0 True 0.10 Pass
1 True 0.25 Fail
2 False 0.50 FALSE
3 False 0.60 FALSE

Alternative with numpy.select:

m1 = df['Space']
m2 = df['Threshold'] <= 0.2
df['Space_Test'] = np.select([m1 & m2, m1 & ~m2], ['Pass', 'Fail'],'FALSE')
print (df)
Space Threshold Space_Test
0 True 0.10 Pass
1 True 0.25 Fail
2 False 0.50 FALSE
3 False 0.60 FALSE

Move column values to values with True/False

I the original index values do not matter, you could use get_dummies to concat the A, B and C columns to the original dataframe, then group by lat lon and animal and sum the boolean columns:

categs = np.sort(mdf['category'].unique())

resul = pd.concat([mdf, pd.get_dummies(mdf['category']).astype(bool)], axis=1
).groupby(['lat', 'lon', 'animal'])[categs].sum().reset_index()

it gives:

    lat   lon animal      A      B      C
0 0.15 0.87 cat False False True
1 0.15 0.87 dog True False False
2 0.15 0.87 rat False True True
3 0.25 0.12 cat False True False
4 0.25 0.12 rat False True False
5 0.48 0.59 cat False False True
6 0.48 0.59 dog True False True
7 0.48 0.59 rat False False True

how to select both true and false column value in mysql

There are three ways to do the same

Using OR (cheaper in cost)

select * from file_download where downloaded = 1 or downloaded = 0

Using IN (short and accurate way)

select * from file_download where downloaded in (0, 1);

Using is not null (way not recommended)

select * from file_download where downloaded is not null

Creating a new column based on TRUE/FALSE of several columns in R

Here are tidyverse approaches.

df = tibble(Red = c(T,T,F,F), Blue = c(F,F,T,F), Green = c(F,F,F,T))

Approach 1: case_when, a vectorised multiple if - else.

df %>%
mutate(color = case_when(Red ~ "Red",
Blue ~ "Blue",
Green ~ "Green"))

Swap mutate with transmute to only return the new color column.

Approach 2: Use column name properties.

df %>%
pivot_longer(everything(), names_to = "color") %>%
filter(value) %>%
select(color)

Approach 3: subset column names

df %>%
mutate(color = names(.)[apply(., 1, which)])


Related Topics



Leave a reply



Submit