On Sep 29, 2016, at 10:29 AM, Mich Talebzadeh <mich.talebza...@gmail.com<mailto:mich.talebza...@gmail.com>> wrote:
Good points :) it took take "-" as a negative number -123456? Yeah… you have to go down a level and start to remember that you’re dealing with a stream or buffer of bytes below any casting. At this moment in time this is what the code does 1. csv is imported into HDFS as is. No cleaning done for rogue columns done at shell level 2. Spark programs does the following filtration: 3. val rs = df2.filter($"Open" !== "-").filter($"Volume".cast("Integer") > 0) So my first line of defence is to check for !== "-" which is a dash, commonly used for not available. The next filter is for volume column > 0 (there was trades on this stock), otherwise the calculation could skew the results. Note that a filter with AND with !== will not work. You can’t rely on the ‘-‘ to represent NaN or NULL. The issue is that you’re going from a loose typing to a stronger typing (String to Double). So pretty much any byte buffer could be interpreted as a String, but iff the String value is too long to be a Double, you will fail the NaN test. (Or its a NULL value/string) As to filtering… you would probably want to filter on volume being == 0. (Its possible to actually have a negative volume. Or you could set the opening, low, high to the close if the volume is 0 regardless of the values in those columns. Note: This would be a transformation of the data and should be done during ingestion so you’re doing it only once. Or you could just remove the rows since no trades occurred and then either reflect it in your graph as gaps or the graph interpolates it out . scala> val rs = df2.filter($"Open" !== "-" && $"Volume".cast("Integer") > 0) <console>:40: error: value && is not a member of String val rs = df2.filter($"Open" !== "-" && $"Volume".cast("Integer") > 0) Will throw an error. But this equality === works! scala> val rs = df2.filter($"Open" === "-" && $"Volume".cast("Integer") > 0) rs: org.apache.spark.sql.Dataset[columns] = [Stock: string, Ticker: string ... 6 more fields] Another alternative is to check for all digits here scala> def isAllPostiveNumber (price: String) = price forall Character.isDigit isAllPostiveNumber: (price: String)Boolean Not really a good idea. You’re walking thru each byte in a stream and checking to see if its a digit. What if its a NULL string? What do you set the value to? This doesn’t scale well… Again why not remove the rows where the volume of trades is 0? Retuns Boolean true or false. But does not work unless someone tells me what is wrong with this below! scala> val rs = df2.filter(isAllPostiveNumber("Open") => true) scala> val rs = df2.filter(isAllPostiveNumber("Open") => true) <console>:1: error: not a legal formal parameter. Note: Tuples cannot be directly destructured in method or function parameters. Either create a single parameter accepting the Tuple1, or consider a pattern matching anonymous function: `{ case (param1, param1) => ... } val rs = df2.filter(isAllPostiveNumber("Open") => true) Thanks Dr Mich Talebzadeh LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw http://talebzadehmich.wordpress.com<http://talebzadehmich.wordpress.com/> Disclaimer: Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 29 September 2016 at 13:45, Michael Segel <msegel_had...@hotmail.com<mailto:msegel_had...@hotmail.com>> wrote: Hi, Just a few thoughts so take it for what its worth… Databases have static schemas and will reject a row’s column on insert. In your case… you have one data set where you have a column which is supposed to be a number but you have it as a string. You want to convert this to a double in your final data set. It looks like your problem is that your original data set that you ingested used a ‘-‘ (dash) to represent missing data, rather than a NULL value. In fact, looking at the rows… you seem to have a stock that didn’t trade for a given day. (All have Volume as 0. ) Why do you need this? Wouldn’t you want to represent this as null or no row for a given date? The reason your ‘-‘ check failed when isnan() is that ‘-‘ actually could be represented as a number. If you replaced the ‘-‘ with a String that is wider than the width of a double … the isnan should flag the row. (I still need more coffee, so I could be wrong) ;-) HTH -Mike On Sep 28, 2016, at 5:56 AM, Mich Talebzadeh <mich.talebza...@gmail.com<mailto:mich.talebza...@gmail.com>> wrote: This is an issue in most databases. Specifically if a field is NaN.. --> (NaN, standing for not a number, is a numeric data type value representing an undefined or unrepresentable value, especially in floating-point calculations) There is a method called isnan() in Spark that is supposed to handle this scenario . However, it does not return correct values! For example I defined column "Open" as String (it should be Float) and it has the following 7 rogue entries out of 1272 rows in a csv df2.filter( $"OPen" === "-").select((changeToDate("TradeDate").as("TradeDate")), 'Open, 'High, 'Low, 'Close, 'Volume).show +----------+----+----+---+-----+------+ | TradeDate|Open|High|Low|Close|Volume| +----------+----+----+---+-----+------+ |2011-12-23| -| -| -|40.56| 0| |2011-04-21| -| -| -|45.85| 0| |2010-12-30| -| -| -|38.10| 0| |2010-12-23| -| -| -|38.36| 0| |2008-04-30| -| -| -|32.39| 0| |2008-04-29| -| -| -|33.05| 0| |2008-04-28| -| -| -|32.60| 0| +----------+----+----+---+-----+------+ However, the following does not work! df2.filter(isnan($"Open")).show +-----+------+---------+----+----+---+-----+------+ |Stock|Ticker|TradeDate|Open|High|Low|Close|Volume| +-----+------+---------+----+----+---+-----+------+ +-----+------+---------+----+----+---+-----+------+ Any suggestions? Thanks Dr Mich Talebzadeh LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw http://talebzadehmich.wordpress.com<http://talebzadehmich.wordpress.com/> Disclaimer: Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction.