Hi Mich, if I understood you well, you may cast the value to float, it will yield null if the value is not a correct float:
val df = Seq(("-", 5), ("1", 6), (",", 7), ("8.6", 7)).toDF("value", "id").createOrReplaceTempView("lines") spark.sql("SELECT cast(value as FLOAT) from lines").show() +-----+ |value| +-----+ | null| | 1. | | null| | 8.6 | +-----+ After it you may filter the DataFrame for values containing null. Regards, -- Bedrytski Aliaksandr sp...@bedryt.ski On Wed, Sep 28, 2016, at 10:11, Mich Talebzadeh wrote: > Thanks all. > > This is the csv schema all columns mapped to String > > scala> df2.printSchema > root > |-- Stock: string (nullable = true) -- Ticker: string (nullable = > |true) -- TradeDate: string (nullable = true) -- Open: string > |(nullable = true) -- High: string (nullable = true) -- Low: string > |(nullable = true) -- Close: string (nullable = true) -- Volume: > |string (nullable = true) > > The issue I have can be shown as below > > 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| > +----------+----+----+---+-----+------+ > Now there are ways of dealing with this. However, the solution has to > be generic! Checking for a column == "-" is not generic. How about if > that column was "," etc. > > 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) > > Spark handles this[1]. I am on Spark 2.0.1 in Class > DataFrameNaFunctions. The simplest one is to drop these rogue rows > df2.filter( $"Open" === "-").drop() > However, a better approach would be to use REPLACE method or testing > any column for NaN > > > > > There is a method called isnan(). However, it does not return > correct values! > > 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 > > > *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 28 September 2016 at 04:07, Mike Metzger > <m...@flexiblecreations.com> wrote: >> Hi Mich - >> >> Can you run a filter command on df1 prior to your map for any rows >> where p(3).toString != '-' then run your map command? >> >> Thanks >> >> >> Mike >> >> >> On Tue, Sep 27, 2016 at 5:06 PM, Mich Talebzadeh >> <mich.talebza...@gmail.com> wrote: >>> Thanks guys >>> >>> Actually these are the 7 rogue rows. The column 0 is the Volume >>> column which means there was no trades on those days >>> >>> *cat stock.csv|grep ",0" *SAP SE,SAP, 23-Dec-11,-,-,-,40.56,0 >>> SAP SE,SAP, 21-Apr-11,-,-,-,45.85,0 SAP SE,SAP, 30-Dec-10,-,-,- >>> ,38.10,0 SAP SE,SAP, 23-Dec-10,-,-,-,38.36,0 SAP SE,SAP, 30-Apr-08,-,-,- >>> ,32.39,0 SAP SE,SAP, 29-Apr-08,-,-,-,33.05,0 SAP SE,SAP, 28-Apr-08,-,-,- >>> ,32.60,0 >>> >>> So one way would be to exclude the rows that there was no volume of >>> trade that day when cleaning up the csv file >>> >>> *cat stock.csv|grep -v ",0"* >>> >>> and that works. Bearing in mind that putting 0s in place of "-" will >>> skew the price plot. >>> >>> BTW I am using Spark csv as well >>> >>> val df1 = spark.read.option("header", true).csv(location) >>> >>> This is the class and the mapping >>> >>> case class columns(Stock: String, Ticker: String, TradeDate: String, >>> Open: Float, High: Float, Low: Float, Close: Float, Volume: Integer) >>> val df2 = df1.map(p => columns(p(0).toString, p(1).toString, >>> p(2).toString, p(3).toString.toFloat, p(4).toString.toFloat, >>> p(5).toString.toFloat, p(6).toString.toFloat, p(7).toString.toInt)) >>> >>> >>> >>> In here I have >>> >>> p(3).toString.toFloat >>> >>> >>> How can one check for rogue data in p(3)? >>> >>> Thanks >>> >>> >>> >>> >>> >>> >>> >>> >>> Dr Mich Talebzadeh >>> >>> >>> >>> LinkedIn * >>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw* >>> >>> >>> >>> 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 27 September 2016 at 21:49, Mich Talebzadeh >>> <mich.talebza...@gmail.com> wrote: >>>> >>>> I have historical prices for various stocks. >>>> >>>> Each csv file has 10 years trade one row per each day. >>>> >>>> These are the columns defined in the class >>>> >>>> case class columns(Stock: String, Ticker: String, TradeDate: >>>> String, Open: Float, High: Float, Low: Float, Close: Float, Volume: >>>> Integer) >>>> >>>> The issue is with Open, High, Low, Close columns that all are >>>> defined as Float. >>>> >>>> Most rows are OK like below but the red one with "-" defined as >>>> Float causes issues >>>> >>>> Date Open High Low Close Volume 27-Sep-16 80.91 80.93 >>>> 79.87 80.85 1873158 >>>> 23-Dec-11 - - - 40.56 0 >>>> >>>> Because the prices are defined as Float, these rows cause the >>>> application to crash >>>> scala> val rs = df2.filter(changeToDate("TradeDate") >= monthsago)- >>>> scala> .select((changeToDate("TradeDate").as("TradeDate")),(('Clos- >>>> scala> e+'Open)/2).as("AverageDailyPrice"), 'Low.as("Day's Low"), >>>> scala> 'High.as("Day's High")).orderBy("TradeDate").collect >>>> 16/09/27 21:48:53 ERROR Executor: Exception in task 0.0 in stage >>>> 61.0 (TID 260) java.lang.NumberFormatException: For input >>>> string: "-" >>>> >>>> >>>> >>>> >>>> One way is to define the prices as Strings but that is not >>>> meaningful. Alternatively do the clean up before putting csv in >>>> HDFS but that becomes tedious and error prone. >>>> >>>> Any ideas will be appreciated. >>>> >>>> >>>> >>>> Dr Mich Talebzadeh >>>> >>>> >>>> >>>> LinkedIn * >>>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw* >>>> >>>> >>>> >>>> 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. >>>> >>>> >>>> Links: 1. https://spark.apache.org/docs/1.5.1/api/java/org/apache/spark/sql/DataFrameNaFunctions.html