Set values of column based on other column

apples-oranges

I have a data frame which looks like this:

  ID     Score New.ID New.Score
  123     5      456          
  456     1      789          
  789     0      123   

I would like to give the same scores to the New.ID column (which are just in a different order).

Desired result:

  ID    Score New.ID New.Score
  123     5      456         1
  456     1      789         0
  789     0      123         5

Code to reconstruct data frame:

ID <- as.factor(c(123,456,789))
Score <- c(5,1,0)
New.ID<- as.factor(c(456, 789, 123))
New.Score <- c(1,0,5)
dt <- data.frame(ID, Score, New.ID, New.Score)

Update

Desired output:

  Group  ID Score New.ID New.Score
     1 123     5    456         1
     1 456     1    789         0
     1 789     0    123         5
     2 555     1    999         0
     2 123     1    123         1
     2 999     0    555         1

So I am trying to attempt to use the function for each group. The ID 123 has score 5 in group 1, but in group 2 it has score 1. And I only want to use the scores that appear within each group.

I tried with ave:

mtch <- function(x) {
  dt[match(x,dt$ID),"Score"]  
}

dt$New.Score <- ave(dt$New.ID, dt$Group, FUN = mtch)

But it gives me NA values.

Code for second df:

Group <- as.factor(c(1, 1, 1, 2, 2, 2))
ID <- as.factor(c(123,456,789, 555, 123, 999))
Score <- c(5,1,0, 1,1,0)
dt <- data.frame(Group, ID, Score, New.ID)
Mike H.

A simple match should do the trick. Using the data you provided:

data <- data.frame(ID, Score, New.ID)
data$New.Score <- data[match(data$New.ID,data$ID),"Score"]

And then checking that it's our desired result:

identical(dt,data)
#[1] TRUE

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Set column values based on lookup from other column

Set values in a column based on the values of other columns as a group

Set values of a column in pandas dataframe based on values in other columns

How to set values of a column based on multiple conditions in other columns in python?

Getting values based on other column

Multiply values in column based on other column

select column values based on other column date

Shift Column based on Values in other column in pandas

Get values from a column based on other column

iterate over pandas rows and set column values based on values in other column

Summing values based on values in other column (Variable)

Filling in NA values based on other values in the column

Handle missing values based on other column values

Increase the values in a column values based on values in other column in pandas

Is there a function to filter column values based on other column values?

Pandas change values in column based on values in other column

How to merger same column values based on other column values?

How to sort the values in one column based on other column values?

overwrite column values using other column values based on conditions pyspark

Summing up values from one column based on values in other column

Select the range of values in the column based on the interval of values in the other column in pandas

How to count number of unique values in column based on values in other column

Replace values in column with NaN based on other values in same column

How to replace nan values of a column based on certain values of other column

Choosing the values in the column based on the maximum values of other column

Oracle : Selecting the values of column based on the values in other column

How to sum a single column values based on values in other column?

How to fill a column with values based on other column's values in Python?

comparing column values based on other column values in pandas

TOP Ranking

HotTag

Archive