# Spark: calculating percentage from a non-Int field

I have a spreadsheet of banking information and one of the goals is to find out the failure rate of an advertising campaign. I think I need to get a count of the total number of entries vs. the total number of non-subscribers. The column for subscribers is a simple "yes" or "no" instead of an integer.

Of course, getting the total number of entries was easy:

scala> val input = sc.textFile("project_1_data.csv")
input: org.apache.spark.rdd.RDD[String] = MapPartitionsRDD[1] at textFile at <console>:27

scala> input.count()
res0: Long = 45211


Next, I filter for "no" in the subscriber column (called "y"):

scala> val sub = bankDF.filter($"y" === "no") sub: org.apache.spark.sql.DataFrame = [age: int, job: string, marital: string, education: string, default: string, balance: int, housing: string, loan: string, contact: string, day: string, month: string, duration: int, campaign: int, pdays: string, previous: int, poutcome: string, y: string] scala> sub.count() res2: Long = 39922  So now I have two numbers to work with, but they're the results of two different operations. How can I work out a percentage of these two numbers in a single pass? Also, if I may add a second question, is there maybe a reference page on how to do averages, medians, means, etc. - so I don't have to ask you guys questions every time? Thanks in advance for any assistance! ## 1 Answer EDIT, in light of your comment and closer reading of your code Ok, so it looks like in your code you used the RDD api to count the total number of rows in the file (treating it as just a plain RDD[String]), whereas for counting the number of rows where $"y" === "no" you used the Spark-SQL API to read in the data as a DataFrame. I'd advise sticking to one API if you're just starting out, otherwise things will likely feel inconsistent and confusing. Since your problem lends itself nicely to operations that DataFrame is designed to perform, I'd recommend you stick to Spark-SQL's API.

If you want to simply calculate the ratio (as a Double) of rows where $"y" === "no", then you could do the following: val ratio = bankDF.filter($"y" === "no").count().toDouble / bankDF.count()

I still strongly recommend you read (and run for yourself) the examples in the Spark-SQL documentation. That'll give you a sense of the sorts of operations you can perform on these dataframes (e.g. means, sums, etc.).

If all you want is the proportion of rows where y == "no", then simply dividing the two Long's you've calculated is a really straightforward and natural way to get what you want. However, I'd highly recommend you take a look at the Spark-SQL API instead of the lower-level RDD API that you used here. For example, using Spark-SQL you can read a CSV file directly with spark.read.csv(), and then treat your dataset like a database table, on which you can do groupBy(), filter(), and aggregate functions like mean(), sum(), etc. The documentation is quite good, so give it a look. Spark takes some getting used to, but its really powerful once you get the hang of it. Keep at it, and good luck!

• Thanks for your response. Could you perhaps give an example of how you would express that in a command line? Unfortunately, the course I'm taking hasn't really given us these kinds of answers. May 14 '17 at 13:23