This won't go in github

Anything not relating to the X-Universe games (general tech talk, other games...) belongs here. Please read the rules before posting.

Moderator: Moderators for English X Forum

Post Reply
User avatar
philip_hughes
Posts: 7757
Joined: Tue, 29. Aug 06, 16:06
x3tc

This won't go in github

Post by philip_hughes » Wed, 18. Oct 17, 06:14

Every time I get a data set from excel with dashes in it, the numbers that are below 12 get converted into months. This is super frustrating. I know to look for this, but the people supplying the dat usually do not.

My solution? A script that changes the excel dates back into numbers- currently optimised for my purposes but could easily be changed to do other things. The name is not work safe- it represents my frustration with Microsoft, but has been sanitised prior to posting in this forum (hint: replace the "*" with something else :) )

Code: Select all

excelun***k<-function(x){
emonths<-c("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
enumeric<-c(1:12)
mnums<-cbind(emonths,enumeric)
for (i in 1:length(x)){
  if(length(grep(x[i],emonths))==0){print("ok")}
     else{
  x[i]<-mnums[grep(x[i],emonths),2]  
}}
return(as.numeric(x))
}

excelun***k("Jan")
Split now give me death? Nah. Just give me your ship.

pjknibbs
Posts: 41359
Joined: Wed, 6. Nov 02, 20:31
x4

Post by pjknibbs » Wed, 18. Oct 17, 09:25

If it's actually an Excel spreadsheet you're being given, the people giving it ought to know how to set the data type for those columns to "Text" so Excel doesn't automatically try to convert them to dates.

User avatar
red assassin
Posts: 4613
Joined: Sun, 15. Feb 04, 15:11
x3

Post by red assassin » Wed, 18. Oct 17, 11:55

Excel is notorious for this issue. In some cases it'll throw errors if you have numeric fields stored as text. Sometimes the conversions are not uniquely reversible so if you're ever not paying attention, now your data is broken. The only good solution is to stop using it for data analysis.
A still more glorious dawn awaits, not a sunrise, but a galaxy rise, a morning filled with 400 billion suns - the rising of the Milky Way

User avatar
philip_hughes
Posts: 7757
Joined: Tue, 29. Aug 06, 16:06
x3tc

Post by philip_hughes » Wed, 18. Oct 17, 14:12

I don't use excel. Ppl supply me data from Excel. I spend my life cleaning data.
Split now give me death? Nah. Just give me your ship.

Post Reply

Return to “Off Topic English”