R Dataframe Factor conversion to numeric issue

Question

I've been trying to merge and sort a couple of csv files (links below). I've successfully merged the files and can sort the result manually in excel. But I want to automate this and be able to get the sorted results out.

THE ISSUE In the the last step, I try to convert the factor 'rankingGDP' in the merged DF to be able to sort it in desc order by value. When i assign the result DF to the order function, the values are completely different for rankingGDP for each country. The data has become misaligned. Can anybody tell me what I am doing wrong. Thanks heaps

   #Fetch the files
    fileUrl <- "https://d396qusza40orc.cloudfront.net/getdata%2Fdata%2FGDP.csv"
    download.file(fileUrl, destfile="./fgdp.csv")
    fileUrl <-"https://d396qusza40orc.cloudfront.net/getdata%2Fdata%2FEDSTATS_Country.csv"
    download.file(fileUrl, destfile="./fed.csv")

    #Read the files
    fgdp <- read.csv("fgdp.csv",skip = 4, header = T)
    fed <- read.csv("fed.csv" ,header = T)

    #subset relevant columns
    fgdp <- fgdp[,c(1,2,4,5)]

    #remove rows that are empty
    fed <- fed[rowSums(is.na(fed))<ncol(fed),]
    fgdp <- fgdp[rowSums(is.na(fgdp))<ncol(fgdp),]

    #name the columns for fgdp to match fed
    colnames(fgdp) <- c("CountryCode","rankingGDP", 
                        "Long.Name", "gdp")

    #merge the files based on Country Code
    dt <- merge(fgdp, fed, by.x ="CountryCode", by.y = "CountryCode", all = TRUE)

    #Remove  rows where the relevant columns are empty
    dt <- dt[!dt$CountryCode=="" ,]
    dt <- dt[!(dt$rankingGDP=="" | is.na(dt$rankingGDP)) ,]

    #subset the columns used for analysis
    dt1 <- dt[,1:4]

    #remove NAs
    dt1 <- dt1[!(is.na(dt1$rankingGDP)),]

    #Convert factor to numeric to be able to sort rankingGDP decending
    #THE ISSUE IS HERE WHERE THE result gives me different values for the
    #rankingGDP column(2). By that I mean factor numbers(type chars) are not
    #converted to the associated number in most cases.

    dt1[,2]<- as.numeric(dt1[,2])

Did you allready check if as.numeric(dt1[,2]) delivers the desired numeric values? If this is not the case you could try as.numeric(as.character(dt1[,2])). — TinglTanglBob, Sep 13 '18 at 14:32
@TinglTanglBob That worked. Thanks mate, you're a legend! .Do you know why it behaves that way? — user2819985, Sep 13 '18 at 14:48

score 0 · Answer 1 · answered Sep 13 '18 at 14:45

So You are trying to convert the factor to numeric. Let's take an example:

> x <- factor(rep(11:20,2))
> x
[1] 11 12 13 14 15 16 17 18 19 20 11 12 13 14 15 16 17 18 19 20
Levels: 11 12 13 14 15 16 17 18 19 20

If you now try to convert it into numeric. Then it will give you the following result.

> as.numeric(x)
[1]  1  2  3  4  5  6  7  8  9 10  1  2  3  4  5  6  7  8  9 10

It will not return you the desired output. To get that You need to do following:

> as.numeric(levels(x))[x]
[1] 11 12 13 14 15 16 17 18 19 20 11 12 13 14 15 16 17 18 19 20

You can apply this to your data.

>as.numeric(levels(dt1[,2]))[dt1[,2]]
[1] 161 105  60 125  32  26 133 172  12  27  68 162  25 140 128  59  76  93
[19] 138 111  69 169 149  96   7 153 113 167 117 165  11  20  36   2  99  98
[37] 121  30 182 166  81  67 102  51   4 183  33  72  48  64  38 159  13 103
[55]  85  43 155   5 185 109   6 114  86 148 175 176 110  42 178  77 160  37
[73] 108  71 139  58  16  10  46  22  47 122  40   9 116  92   3  50  87 145
[91] 120 189 178  15 146  56 136  83 168 171  70 163  84  74  94  82  62 147
[109] 141 132 164  14 188 135 129 137 151 130 118 154 127 152  34 123 144  39
[127] 126  18  23 107  55  66  44  89  49  41 187 115  24  61  45  97  54  52
[145]   8 142  19  73 119  35 174 157 100  88 131 186 150  63  80  21 158 173
[163]  65 124 156  31 143  91 170 184 101  79  17 190  95 106  53  78   1  75
[181] 180  29  57 177 181  90  28 112 104 134

For more information, you can visit How to convert a factor to integer\numeric without loss of information?

Hope it will help.

Yes, I realized my ignorance in this case hehe. Thanks for clarifying. It is one of those things that should be but it is not! — user2819985, Sep 13 '18 at 14:55

score 0 · Accepted Answer · answered Sep 13 '18 at 14:47

I re-ran your script by adding stringsAsFactors=F to 3 places and it seems to be working fine now:

fgdp <- read.csv("fgdp.csv",skip = 4, header = T, stringsAsFactors=F)
fed <- read.csv("fed.csv" ,header = T, stringsAsFactors=F)

dt <- merge(fgdp, fed, by.x ="CountryCode", by.y = "CountryCode", all = TRUE, stringsAsFactors=F)

Let me know if it worked for you

R Dataframe Factor conversion to numeric issue

2 Answers2