Update a Value in One Column Based on Criteria in Other Columns

Update a Value in One Column Based on Criteria in Other Columns

df <- data.frame(Name=c('John Smith', 'John Smith', 'Jeff Smith'),
                 State=c('MI','WI','WI'), stringsAsFactors=F)

df <- within(df, Name[Name == 'John Smith' & State == 'WI'] <- 'John Smith1')

> df
         Name State
1  John Smith    MI
2 John Smith1    WI
3  Jeff Smith    WI

** Edit **

Edited to add that you can put whatever you like in the within expression:

df <- within(df, {
    f <- Name == 'John Smith' & State == 'WI'
    Name[f] <- 'John Smith1'
    State[f] <- 'CA'
}) 

Update multiple values in a single column based on multiple criteria

You may only do a single UPDATE per statement. So, your options are doing several separate updates, or you may do a single update using a CASE expression:

UPDATE [Phasing] 
SET [Multiplier] = CASE WHEN [DateKey] = 201801
                        THEN 0.08745
                        WHEN [DateKey] = 201802
                        THEN 0.07605 END
WHERE
    [TypeKey] = 1 AND [VersionKey] = 1234 AND
    [DateKey] IN (201801, 201802);

Update value in one column based on criteria from multiple other columns in r

Solution using data.table package:

library(data.table)

#initilize sample data.table
DT <- data.table(c(1,2,3,4), c(1,1,2,2),c(0,1,0,0),c(0,NaN,0,0),c(NaN,NaN,0,1))
colnames(DT) <- c("Subject","Drug","Death_3Months","Death_6months","Death_12months")

#Add row together using death columns and exclude NA's
DT[, "1 year mortality" := rowSums(.SD, na.rm=TRUE), .SDcols = 3:5]

#run through ifelse logic as described
DT[, "Time to Event" := ifelse(!is.finite(Death_3Months), 0,
                               ifelse(Death_3Months == 1, 3,
                                      ifelse(!is.finite(Death_6months), 3, 
                                             ifelse(Death_6months == 1, 6,
                                                    ifelse(!is.finite(Death_12months), 6, 12)))))]

DT

> DT
   Subject Drug Death_3Months Death_6months Death_12months 1 year mortality Time to Event
1:       1    1             0             0            NaN                0             6
2:       2    1             1           NaN            NaN                1             3
3:       3    2             0             0              0                0            12
4:       4    2             0             0              1                1            12

Pandas/Python: Set value of one column based on value in another column

one way to do this would be to use indexing with .loc.

Example

In the absence of an example dataframe, I'll make one up here:

import numpy as np
import pandas as pd

df = pd.DataFrame({'c1': list('abcdefg')})
df.loc[5, 'c1'] = 'Value'

>>> df
      c1
0      a
1      b
2      c
3      d
4      e
5  Value
6      g

Assuming you wanted to create a new column c2, equivalent to c1 except where c1 is Value, in which case, you would like to assign it to 10:

First, you could create a new column c2, and set it to equivalent as c1, using one of the following two lines (they essentially do the same thing):

df = df.assign(c2 = df['c1'])
# OR:
df['c2'] = df['c1']

Then, find all the indices where c1 is equal to 'Value' using .loc, and assign your desired value in c2 at those indices:

df.loc[df['c1'] == 'Value', 'c2'] = 10

And you end up with this:

>>> df
      c1  c2
0      a   a
1      b   b
2      c   c
3      d   d
4      e   e
5  Value  10
6      g   g

If, as you suggested in your question, you would perhaps sometimes just want to replace the values in the column you already have, rather than create a new column, then just skip the column creation, and do the following:

df['c1'].loc[df['c1'] == 'Value'] = 10
# or:
df.loc[df['c1'] == 'Value', 'c1'] = 10

Giving you:

>>> df
      c1
0      a
1      b
2      c
3      d
4      e
5     10
6      g

Add Value From One Cell to Another Based on Criteria From Different Column

I think the base premise here is a grouped-calculation (not easy in a for loop), and it should be grouped on whether category is "Opponnent" or not (lumping "P1", "P2", etc, together).

Data prep: starting with just the first two columns of your dataset above:

smallerDF <- structure(list(category = c("Opponent", "Opponent", "Opponent", "Opponent", "P1", "P2", "P3", "P2", "P2", "Opponent", "Opponent", "P1"), Event = c("Good Pass", "Good Pass", "Good Pass", "Turnover", "Good Pass", "Good Pass", "Good Pass", "Good Pass", "Bad Pass", "Intercepted Pass", "Bad Pass", "Good Pass")), row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"))

I'll add a "time" column: some utilities (e.g., base::merge) do not honor row-order, despite some best efforts. I think it's generally safer anyway to have a "time" component in there to remove accidental reordering. Neither the data.table nor dplyr solutions below reorder it inadvertently, but it's still not a horrible idea.

smallerDF$time <- seq_len(nrow(smallerDF))

base R

This is perhaps the least-intuitive of the three, since the grouping functions in R can seem daunting. These include ave, aggregate, by, tapply, etc. I'll stick with ave for now, as it is the simplest and perhaps easiest to read.

First, we'll create a "merge/join" table for Value (other ways exist to bring in these values, see https://stackoverflow.com/a/68999591/3358272; @ViníciusFélix's answer is a great example using case_when for this purpose). Second, we'll aggregate by "Opponent vs NotOpponent".

values <- data.frame(
  Event = c("Good Pass", "Bad Pass", "Intercepted Pass", "Turnover"),
  Value = c(2, -2, 1, -3)
)
smallerDF2 <- merge(smallerDF, values, by = "Event", all.x = TRUE, sort = FALSE)
## feel free to verify that `smallerDF2` is no longer in the original order,
## despite `sort=FALSE`. Order is not guaranteed with `base::merge`, period.
smallerDF2 <- smallerDF2[order(smallerDF2$time),]
smallerDF2
#               Event category time Value
# 1         Good Pass Opponent    1     2
# 2         Good Pass Opponent    2     2
# 3         Good Pass Opponent    3     2
# 9          Turnover Opponent    4    -3
# 5         Good Pass       P1    5     2
# 6         Good Pass       P2    6     2
# 7         Good Pass       P3    7     2
# 4         Good Pass       P2    8     2
# 10         Bad Pass       P2    9    -2
# 12 Intercepted Pass Opponent   10     1
# 11         Bad Pass Opponent   11    -2
# 8         Good Pass       P1   12     2
smallerDF2$`Score Sum2` <- ave(smallerDF2$Value, smallerDF2$category == "Opponent", FUN = cumsum)
smallerDF2
#               Event category time Value Score Sum2
# 1         Good Pass Opponent    1     2          2
# 2         Good Pass Opponent    2     2          4
# 3         Good Pass Opponent    3     2          6
# 9          Turnover Opponent    4    -3          3
# 5         Good Pass       P1    5     2          2
# 6         Good Pass       P2    6     2          4
# 7         Good Pass       P3    7     2          6
# 4         Good Pass       P2    8     2          8
# 10         Bad Pass       P2    9    -2          6
# 12 Intercepted Pass Opponent   10     1          4
# 11         Bad Pass Opponent   11    -2          2
# 8         Good Pass       P1   12     2          8

data.table

library(data.table)
smallerDT <- as.data.table(smallerDF)
smallerDT[values, Value := Value, on = .(Event)]
smallerDT[, `Score Sum2` := cumsum(Value), by = .(category == "Opponent")]

dplyr

library(dplyr)
left_join(smallerDF, values, by = "Event") %>%
  group_by(g = (category == "Opponent")) %>%
  mutate(`Score Sum` = cumsum(Value)) %>%
  ungroup() %>%
  select(-g)

R: Update Column Based on Text Condition from Another Column

In your example, exact string matching works. But I am assuming it does not hold true for your entire data.

Implementing the contains condition would be something like this:

library(dplyr)
library(purrr)

df = df %>% 
  dplyr::mutate(NewColumn = purrr::map2_dbl(.x=Winner,.y=Name,~ifelse(grepl(.y,.x),1,0)))

Adding an alternate solution with stringr:

df = df %>% 
  dplyr::mutate(NewColumn=ifelse(str_detect(Winner,Name),1,0))

Let me know if this works.

P.S.: str_detect is faster.

Change single cells in column based on string pattern in other column in R

If your sentence "if df$Time have W02" then spring in that row = 1" describes what you want to do, then that should work:

df$spring <- ifelse(grepl("W02$", df$Time), df$spring == 1, df$spring)

Alternatively:

df$spring[grepl("W02$",df$Time)] <- 1

In dplyr:

df %>%
  mutate(spring = ifelse(grepl("W02$", Time), 1, spring)) 

R - Update a Value in One Column Based on Criteria in Other Tables

We can use %chin% to create the logical index of elements in 'w' that are not found in 'z', then assign (:=) the corresponding elements in 'w' to "NONE".

tb1[!w %chin% tb2$z, w:= "NONE"]
tb1
#         w       x       y
# 1:    NONE   alpha    zulu
# 2:    NONE   bravo  yankee
# 3:    NONE charlie    xray
# 4:   alpha    xray charlie
# 5:   bravo  yankee   bravo
# 6: charlie    zulu   alpha
# 7:    NONE    xray    zulu
# 8:    NONE  yankee  yankee
# 9:    NONE    zulu    xray
#10:   alpha   alpha charlie
#11:   bravo   bravo   bravo
#12: charlie charlie   alpha

If we need to change all the columns in 'tb1' based on the 'z' column of 'tb2', we can loop through the columns of 'tb1' and assign as showed earlier.

nm1 <- names(tb1)
for(j in seq_along(tb1)){
  tb1[!tb1[[j]] %chin% tb2$z, nm1[j] := "NONE"]
}
tb1
#          w       x       y
# 1:    NONE   alpha    NONE
# 2:    NONE   bravo    NONE
# 3:    NONE charlie    NONE
# 4:   alpha    NONE charlie
# 5:   bravo    NONE   bravo
# 6: charlie    NONE   alpha
# 7:    NONE    NONE    NONE
# 8:    NONE    NONE    NONE
# 9:    NONE    NONE    NONE
#10:   alpha   alpha charlie
#11:   bravo   bravo   bravo
#12: charlie charlie   alpha

---

Related Topics