0

I'm new to R and got stuck in my assignment when couldn't convert the text values to numerical. I've tried a number of solutions but they are not working. As an example, when doing the following, I overwrite the first line of code, basically I need to mutate both and then convert it to numbers.

fifa$Release_Clause2 = str_replace(fifa$`Release Clause`,"K","000")
fifa$Release_Clause2 = str_replace(fifa$`Release Clause`,"M","000000")
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
JME19
  • 3
  • 1
  • Fixing a problem like this requires users here to see a sample of the input data as well as the code you are using. Could you include the results of `dput(head(fifa))` in your question please? – Allan Cameron Sep 27 '21 at 10:49

1 Answers1

0

Supposing you have data that looks like this:

 fifa2 <- data.frame(Value = c("€565K", "€5.65M", "€777777"))

you can do this:

library(dplyr)
fifa2 %>%
  mutate(Value1 = as.numeric(gsub("[€MK]", "", Value)),
         Value1 = ifelse(grepl("K$", Value), Value1 * 1000, 
                                   ifelse(grepl("M$", Value), Value1 * 1000000, 
                                          Value1)))
    Value  Value1
1   €565K  565000
2  €5.65M 5650000
3 €777777  777777
Chris Ruehlemann
  • 20,321
  • 4
  • 12
  • 34
  • Hi, is not working ): All the column went to NA... fifa2$Value <- as.numeric(ifelse(grepl("K", fifa2$Value), + sub("K", "000", fifa2$Value), + ifelse(grepl("M", fifa2$Value), + sub("M", "000000", fifa2$Value), + fifa2$Value))) Warning message: NAs introduced by coercion – JME19 Sep 28 '21 at 11:31
  • Well, post some of **your** data so I can take a look... – Chris Ruehlemann Sep 28 '21 at 11:32
  • fifa2$Value <- as.numeric(ifelse(grepl("K$", fifa2$Value), sub("K$", "000", fifa2$Value), ifelse(grepl("M$", fifa2$Value), sub("M$", "000000", fifa2$Value), fifa2$Value))) – JME19 Sep 28 '21 at 11:34
  • That's **code not data**. I need data to see exactly what went wrong. You can post it using `dput()`. For example, if your dataframe is called `fifa2` you can do: `dput(fifa2[1:10,])`to output the first 10 lines – Chris Ruehlemann Sep 28 '21 at 11:39
  • Try your code again with this data: `fifa2 <- data.frame(Value = c("2K", "3M", "7777"))`- it works perfectly! – Chris Ruehlemann Sep 28 '21 at 11:44
  • # A tibble: 10 × 95 ...1 ID Name Age Photo Nationality Flag Overall Potential Club `Club Logo` Value Wage Special 1 0 158023 L. Me… 31 https:… Argentina https:… 94 94 FC B… https://cdn… €110… €565K 2202 – JME19 Sep 28 '21 at 12:05
  • Sorry, that's not processable... Please use dput() as described earlier – Chris Ruehlemann Sep 28 '21 at 12:40
  • > dput(fifa[1:1,]) structure(list(...1 = 0, ID = 158023, Name = "L. Messi", Age = 31, Photo = "https://cdn.sofifa.org/players/4/19/158023.png", Nationality = "Argentina", Flag = "https://cdn.sofifa.org/flags/52.png", Overall = 94, Potential = 94, Club = "FC Barcelona", `Club Logo` = "https://cdn.sofifa.org/teams/2/light/241.png", Value = "€110.5M", Wage = "€565K", Special = 2202, `Preferred Foot` = "Left", `International Reputation` = 5, `Weak Foot` = 4, `Skill Moves` = 4, `Work Rate` = "Medium/ Medium", – JME19 Sep 28 '21 at 15:29
  • `Body Type` = "Messi", `Real Face` = "Yes", Position = "RF", `Jersey Number` = 10, Joined = "Jul 1, 2004", `Loaned From` = NA_character_, `Contract Valid Until` = "2021", Height = "5'7", Weight = "159lbs", LS = "88+2", ST = "88+2", RS = "88+2", LW = "92+2", LF = "93+2", CF = "93+2", RF = "93+2", RW = "92+2", LAM = "93+2", CAM = "93+2", RAM = "93+2", LM = "91+2", LCM = "84+2", CM = "84+2", RCM = "84+2", RM = "91+2", LWB = "64+2", LDM = "61+2", CDM = "61+2", RDM = "61+2", – JME19 Sep 28 '21 at 15:30
  • RWB = "64+2", LB = "59+2", LCB = "47+2", CB = "47+2", RCB = "47+2", RB = "59+2", Crossing = 84, Finishing = 95, HeadingAccuracy = 70, ShortPassing = 90, Volleys = 86, Dribbling = 97, Curve = 93, FKAccuracy = 94, LongPassing = 87, BallControl = 96, Acceleration = 91, SprintSpeed = 86, Agility = 91, Reactions = 95, Balance = 95, ShotPower = 85, Jumping = 68, Stamina = 72, Strength = 59, LongShots = 94, Aggression = 48, Interceptions = 22, Positioning = 94, Vision = 94, Penalties = 75, Composure = 96, Marking = 33, – JME19 Sep 28 '21 at 15:30
  • StandingTackle = 28, SlidingTackle = 26, GKDiving = 6, GKHandling = 11, GKKicking = 15, GKPositioning = 14, GKReflexes = 8, `Release Clause` = "€226.5M", WageK = 565, ValueK = 110.5, Release_ClauseK = 226.5, WageK2 = 565, ValueK2 = 110.5, Release_ClauseK2 = 226.5), row.names = c(NA, -1L), class = c("tbl_df", "tbl", "data.frame")) – JME19 Sep 28 '21 at 15:30
  • The format of your columns is not accepted in R: column names must be contiguous strings, while you frequently have strings interrupted by whitespace – Chris Ruehlemann Sep 28 '21 at 16:29
  • Also you seem to have values such as "€565K". The currency symbol must be removed before any conversion to numeric! (Otherwise you'll get NA) – Chris Ruehlemann Sep 28 '21 at 16:36
  • What's more you also have decimal values. Please see edited solution that takes care of the issues. – Chris Ruehlemann Sep 28 '21 at 16:45
  • It worked, I'll remember you when I create the new facebook! Thank you so much! – JME19 Sep 28 '21 at 17:24