Changing variable values in dataframe

pnwballr · February 24, 2021, 8:58pm

I am attempting to change the variables in NETRTG to be 0 if it is a negative value and 1 if it is a positive value. I have been trying to use a combination of mutate if functions, but having little success. Will take any and all guidance here! Thanks

ID	Home	Away	Team	NETRTG

4983535	UCLA	Seattle U	Seattle U	-36.111
4984328	Long Beach St.	Seattle U	Seattle U	-5.212
4984564	Washington	Seattle U	Seattle U	-45.503
4985254	Seattle U	Northwest (WA)	Seattle U	69.172
4985790	Seattle U	Col. of Idaho	Seattle U	37.563
4986583	California	Seattle U	Seattle U	-6.2
4986962	Seattle U	Portland	Seattle U	16.855
4988878	Seattle U	Saint Martin's	Seattle U	46.131
4989835	Seattle U	Utah Valley	Seattle U	-3.333
4996158	Dixie St.	Seattle U	Seattle U	-4.146
4996988	Dixie St.	Seattle U	Seattle U	25.034

pnwballr · February 24, 2021, 9:13pm

Another way I would actually prefer to do this is create a new column beyond dPTS that compares the PTS and dPTS values for each row. If dPTS is greater, write a 0 in the new column. If PTS is greater, write a 1 in the new column

Home	Away	Team	PTS	dPTS


4983535	UCLA	Seattle U	52	78
4984328	Long Beach St.	Seattle U	75	80
4984564	Washington	Seattle U	41	73
4985254	Seattle U	Northwest (WA)	89	40
4985790	Seattle U	Col. of Idaho	78	54
4986583	California	Seattle U	65	70
4986962	Seattle U	Portland	84	68
4988878	Seattle U	Saint Martin's	98	63
4989835	Seattle U	Utah Valley	92	93
4996158	Dixie St.	Seattle U	76	77
4996988	Dixie St.	Seattle U	77	56

pnwballr · February 24, 2021, 9:22pm

This should maybe be it's own thread, but trying to do one more thing to the data of the similar nature. I want to revalue the ID column to the value of Home and Away that is not equal to the Team value for that row. For example,
4983535. UCLA. Seattle U. Seattle U would look like
UCLA. UCLA. Seattle U. Seattle U after the transformation

ID	Home	Away	Team


4983535	UCLA	Seattle U	Seattle U
4984328	Long Beach St.	Seattle U	Seattle U
4984564	Washington	Seattle U	Seattle U
4985254	Seattle U	Northwest (WA)	Seattle U
4985790	Seattle U	Col. of Idaho	Seattle U
4986583	California	Seattle U	Seattle U
4986962	Seattle U	Portland	Seattle U
4988878	Seattle U	Saint Martin's	Seattle U
4989835	Seattle U	Utah Valley	Seattle U
4996158	Dixie St.	Seattle U	Seattle U
4996988	Dixie St.	Seattle U	Seattle U

technocrat · February 25, 2021, 1:19am

your_df  %>% mutate(your_target = ifelse(your_target <0,0,your_target)

pnwballr · February 25, 2021, 1:54am

Thank you for the reply, but I don't fully understand what is happening there. Could you explain the code and what it is doing. Sorry beginner here

technocrat · February 25, 2021, 2:35am

Anyone who still doesn't believe in their beginner status (especially me) is delusional.

See the FAQ: How to do a minimal reproducible example reprex for beginners

And come back with a concrete example.

pnwballr · February 25, 2021, 3:02am

Hahaha ok I like that, I will remember that. Here is the minimal reprex

Trying to get the net rating to =1 for positive values and =0 for negative values and then rename the ID variable depending on what the Home and Away variables are set to

CODE:
fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
mutate(NETRTG >= "0",1) %>%
mutate(NETRTG <= "0",0) %>%
rename(ID = if("Home" != "Seattle U") = "Home", ifelse(= "Away"))

RESULT:
fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%

mutate(NETRTG >= "0",1) %>%
mutate(NETRTG <= "0",0) %>%
rename(ID = if("Home" != "Seattle U") = "Home", ifelse(= "Away"))
Error: unexpected '=' in:
" mutate(NETRTG <= "0",0) %>%
rename(ID = if("Home" != "Seattle U") ="

df [fdecteamstats]

ID	Home	Away	Team	Mins	oMins	dMins	oPOSS	dPOSS	ORTG	DRTG	NETRTG	PTS	dPTS

4983535	UCLA	Seattle U	Seattle U	38.683	19.6	19.083	72	72	72.222	108.333	-36.111	52	78
4984328	Long Beach St.	Seattle U	Seattle U	38.167	21.633	16.533	76	77	98.684	103.896	-5.212	75	80
4984564	Washington	Seattle U	Seattle U	38.683	21.083	17.6	68	69	60.294	105.797	-45.503	41	73
4985254	Seattle U	Northwest (WA)	Seattle U	39.183	16.417	22.767	70	69	127.143	57.971	69.172	89	40
4985790	Seattle U	Col. of Idaho	Seattle U	39.183	19.067	20.117	68	70	114.706	77.143	37.563	78	54
4986583	California	Seattle U	Seattle U	39.083	19.867	19.217	63	64	103.175	109.375	-6.2	65	70
4986962	Seattle U	Portland	Seattle U	39.917	20.85	19.067	79	76	106.329	89.474	16.855	84	68
4988878	Seattle U	Saint Martin's	Seattle U	38.817	18	20.817	74	73	132.432	86.301	46.131	98	63
4989835	Seattle U	Utah Valley	Seattle U	43.583	22.717	20.867	92	90	100	103.333	-3.333	92	93
4996158	Dixie St.	Seattle U	Seattle U	39.767	20.767	19	75	73	101.333	105.479	-4.146	76	77
4996988	Dixie St.	Seattle U	Seattle U	39.483	21.633	17.85	78	76	98.718	73.684	25.034	77	56

technocrat · February 25, 2021, 7:26am

I know we have a homeless problem in SEA.

suppressPackageStartupMessages({
  library(dplyr)
})
decteamstats <- data.frame(
  me =
    c(4983535, 4984328, 4984564, 4985254, 4985790, 4986583, 4986962, 4988878, 4989835, 4996158, 4996988),
  Away =
    c("UCLA", "Long Beach St.", "Washington", "Seattle U", "Seattle U", "California", "Seattle U", "Seattle U", "Seattle U", "Dixie St.", "Dixie St."),
  Team =
    c("Seattle U", "Seattle U", "Seattle U", "Northwest (WA)", "Col. of Idaho", "Seattle U", "Portland", "Saint Martin's", "Utah Valley", "Seattle U", "Seattle U"),
  Mins =
    c("Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U", "Seattle U"),
  oMins =
    c(38.683, 38.167, 38.683, 39.183, 39.183, 39.083, 39.917, 38.817, 43.583, 39.767, 39.483),
  dMins =
    c(19.6, 21.633, 21.083, 16.417, 19.067, 19.867, 20.85, 18, 22.717, 20.767, 21.633),
  oPOSS =
    c(19.083, 16.533, 17.6, 22.767, 20.117, 19.217, 19.067, 20.817, 20.867, 19, 17.85),
  dPOSS =
    c(72, 76, 68, 70, 68, 63, 79, 74, 92, 75, 78),
  ORTG =
    c(72, 77, 69, 69, 70, 64, 76, 73, 90, 73, 76),
  DRTG =
    c(72.222, 98.684, 60.294, 127.143, 114.706, 103.175, 106.329, 132.432, 100, 101.333, 98.718),
  NETRTG =
    c(108.333, 103.896, 105.797, 57.971, 77.143, 109.375, 89.474, 86.301, 103.333, 105.479, 73.684),
  PTS =
    c(-36.111, -5.212, -45.503, 69.172, 37.563, -6.2, 16.855, 46.131, -3.333, -4.146, 25.034),
  dPTS =
    c(52, 75, 41, 89, 78, 65, 84, 98, 92, 76, 77)
)

pnwballr · February 25, 2021, 8:15am

Yes we do, as any big city does. Something needs to be done though to help!

I ran that line of code:

"
fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
rename(ID = if("Home" != "Seattle U") = "Home", ifelse(= "Away"))
"
And still got the error
Error: unexpected '=' in:
"fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
rename(ID=if("Home" != "Seattle U")="

What is not correct there?

technocrat · February 25, 2021, 8:26am

Our homeless problem is worse than other cities, by the standards we hold ourselves to.

The problem is that there is no variable Home. And if there were, it shouldn't be quoted.

vlad_aluas · February 25, 2021, 8:58am

The problem is that R uses a single = as an attribution sign. If you want to compare something, use double ==.

Another thing that I noticed is with the mutate. You were trying to compare your variable with a character value.
You can change the variable with a simple mutate:

decteamstats  %>% mutate(NETRTG = ifelse(NETRTG <= 0, 1, 0)

I don't actually understand what you are trying to achieve with the rename part of the code, change the value of the variable or rename the variable.

If you can be a bit more explicit with that part I can try and help you.

nirgrahamuk · February 25, 2021, 10:18am

the problem with

is too many to list ... but the easy point to make is that its substantially not what technocrat recommended you try which was syntax of the form

your_df %>% mutate(your_target = ifelse(your_target <0,0,your_target)

I think you may have been confused because technocrat quoted your incorrect syntax in the post in which he provides example data ...

pnwballr · February 25, 2021, 6:06pm

Thank you! I was really struggling to convert the help I was getting into successful code, but the mutate on NETRTG is now working perfectly!

What I am trying to do with the rename of the code is to change the ID variable in each row to be the Home or Away value as long as it is not "Seattle U". I am trying to make the ID become the team name that is not Seattle U.

For example, after the rename I want row 1 to go from
4998535 UCLA Seattle U Seattle U to
UCLA UCLA Seattle U Seattle U

ID	Home	Away	Team


4983535	UCLA	Seattle U	Seattle U
4984328	Long Beach St.	Seattle U	Seattle U
4984564	Washington	Seattle U	Seattle U
4985254	Seattle U	Northwest (WA)	Seattle U
4985790	Seattle U	Col. of Idaho	Seattle U
4986583	California	Seattle U	Seattle U
4986962	Seattle U	Portland	Seattle U
4988878	Seattle U	Saint Martin's	Seattle U
4989835	Seattle U	Utah Valley	Seattle U
4996158	Dixie St.	Seattle U	Seattle U
4996988	Dixie St.	Seattle U	Seattle U

nirgrahamuk · February 25, 2021, 6:47pm

where df is your data

mutate(rowwise(df),
       ID= (setdiff(c(Home,Away),Team)))

p.s. although markdown tables are nice ways to present tabular information to the reader of a forum post, they are a bad way to share example data, as it is non-trivial for a typical forum user to bring the data into their R session. Therefore please consider studying the reprex guide (which is a short read) to understand the preferred methods of sharing example data on the forum.

pnwballr · February 25, 2021, 7:54pm

Ok thank you so much! That worked and I will look at the reprex guide and do it that way moving forward. I can get the code to run correctly as it's own step, but when I try to group it in with other lines of code it doesn't work. I don't understand the error message I am getting. Do you see what is happening wrong?

fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
  mutate("NETRTG" = ifelse(NETRTG <= 0, 0, 1)) %>%
  mutate_if(is.numeric, round) %>%
  rename("W/L" = NETRTG) %>%
  mutate("Home" = ifelse(Home == "Seattle U", 1, 0)) %>%
  mutate(rowwise(fdecteamstats),
         ID= (setdiff(c(Home,Away),Team)))

And the error I am getting back is

Error: Problem with `mutate()` input `..1`.
x Input `..1` can't be recycled to size 1.
ℹ Input `..1` is `rowwise(fdecteamstats)`.
ℹ Input `..1` must be size 1, not 11.
ℹ The error occurred in group 1: ID = 4983535, Home = 0, Away = "Seattle U"

I would assume that I would need to take (fdecteamstats) out because it is already set on the correct df from abov, but am not sure.

nirgrahamuk · February 25, 2021, 8:24pm

you do want the data.frame to be process rowwise, but you are setting rowwise on a dataframe that logically wont exist untill the series of pipes complete. its sufficient to add rowwise as an operation between other operations

%>% rowwise() %>% mutate(ID = ... etc.)

pnwballr · February 25, 2021, 9:13pm

I am still running to an error for some reason

fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
  mutate("NETRTG" = ifelse(NETRTG <= 0, 0, 1)) %>%
  mutate_if(is.numeric, round) %>%
  rename("W/L" = NETRTG) %>%
  mutate("Home" = ifelse(Home == "Seattle U", 1, 0)) %>%
  rowwise() %>%
  ID = (setdiff(c("Home","Away"),"Team"))

> fdecteamstats=filter(decteamstats, Team == "Seattle U") %>%
+   mutate("NETRTG" = ifelse(NETRTG <= 0, 0, 1)) %>%
+   mutate_if(is.numeric, round) %>%
+   rename("W/L" = NETRTG) %>%
+   mutate("Home" = ifelse(Home == "Seattle U", 1, 0)) %>%
+   rowwise() %>%
+   ID= (setdiff(c("Home","Away"),"Team"))
`mutate_if()` ignored the following grouping variables:
Columns `ID`, `Home`, `Away`
Error in filter(decteamstats, Team == "Seattle U") %>% mutate(NETRTG = ifelse(NETRTG <=  : 
  could not find function "%>%<-"

It is saying it can't find the function "%>%<-" but I don't see that in any line of my code so I am confused

nirgrahamuk · February 25, 2021, 9:26pm

You omitted the mutate ( that gives setting ID to some value context.

pnwballr · February 25, 2021, 10:05pm

I feel like an idiot. I am an absolute self taught beginner if you can't tell.

  mutate("NETRTG" = ifelse(NETRTG <= 0, 0, 1)) %>%
  mutate_if(is.numeric, round) %>%
  rename("W/L" = NETRTG) %>%
  mutate("Home" = ifelse(Home == "Seattle U", 1, 0)) %>%
  rowwise() %>%
  mutate("ID" = (setdiff(c("Home","Away"),"Team")))

I am still getting an error though?

Error: Problem with `mutate()` input `ID`.
x Input `ID` can't be recycled to size 1.
ℹ Input `ID` is `(setdiff(c("Home", "Away"), "Team"))`.
ℹ Input `ID` must be size 1, not 2.
ℹ Did you mean: `ID = list((setdiff(c("Home", "Away"), "Team")))` ?
ℹ The error occurred in row 1.

I have tried having ID both in quotes and not in quotes

nirgrahamuk · February 25, 2021, 10:54pm

You introduced quotes around home team away to make them literal, when they rather should be unquoted and represent columns in your frame