How to select and add data to specific rows?

Question

How would you loop through the values in one column looking for a specific set of characters and then, if the row has those characters, assign a value specifically to that row in a new column?

For example, in this problem we must assign one point to every disagreeing response and zero to the agreeing ones.

     Id   Gender   Age Participate Question            Response Score
    <int>  <chr> <int>       <int>    <chr>               <chr> <dbl>
1     16   Male    20           1       Q1   Slightly Disagree     0
2     17   Male    40           1       Q1    Definitely Agree     0
3     18   Male    33           1       Q1    Definitely Agree     0
4     19   Male    18           1       Q1    Definitely Agree     0
5     20   Male    24           1       Q1 Definitely Disagree     0
6     21 Female    42           1       Q1   Slightly Disagree     0
7     22 Female    19           1       Q1      Slightly Agree     0
8     28 Female    49           1       Q1   Slightly Disagree     0
9     29 Female    17           1       Q1      Slightly Agree     0
10    31   Male    18           1       Q1      Slightly Agree     0`

Answer 1

This must be a duplicate, couldn't find one.

Anyways, as @Zheyuan Li pointed out, you can use ifelse in this case.

You can use grepl to find if the text has the word "Disagree" in it. grepl returns a boolean vector , you can convert it to numeric by wrapping it in as.numeric

as.numeric(grepl("Disagree", df$Response))

#[1] 1 0 0 0 1 1 0 1 0 0

So, considering df as your dataframe, you can add a new_column by

df$new_column <- as.numeric(grepl("Disagree", df$Response))

Answer 2

According to my understanding of your problem below query should work for you.

UPDATE yourSchema.yourTable anAlias
SET Score = finalData.Score
FROM
  (SELECT Id,
          CASE
              WHEN Response LIKE '%Disagree%' THEN 1
              ELSE 0
          END AS score
   FROM yourSchema.yourTable) finalData
WHERE anAlias.Id = finalData.Id;

Answer 3

Take a look in:

SELECT * FROM UPDATE WHERE

Postgres SELECT* FROM table WHERE column-varchar=="string-example"?

SQL query SELECT FROM [Select value from tablename]

Php , Mysql Select from table where row = ? it is possible?