Scala Spark functions like group by, describe() returning incorrect result

user_15345826 0
Viewed 142

I have using Scala Spark on intellij IDE to analyze a csv file having 672,112 records . File is available on the link - https://www.kaggle.com/kiva/data-science-for-good-kiva-crowdfunding

File name : kiva_loans.csv

I ran show() command to view few records and it is reading all columns correctly but when I apply group by on the column "repayment_interval", it displays value which appears to be data from other columns (column shift ) as shown below.

enter image description here

distinct values in the "repayment_interval" columns are

  1. Monthly (More frequent)
  2. irregular
  3. bullet
  4. weekly (less frequent)

For testing purpose, I searched for values given in the screenshot and put those rows in a separate file and tried to read that file using scala spark. It is showing all values in correct column and even groupby is returning correct values.

I am facing this issue with describe() function. enter image description here

As shown in above image , column - id & "funded_amount" is numeric columns but not sure why describe() on them is giving string values for "min","max".

read csv command as below

  val kivaloans=spark.read
      //.option("sep",",")
      .format("com.databricks.spark.csv")
      .option("header",true)
      .option("inferschema","true")
      .csv("kiva_loans.csv")

printSchema output after adding ".option("multiline","true")". It is reading few rows as header as shown in the highlighted yellow color.

enter image description here

apache-spark-sql apache-spark scala
1 Answers

It seems, there are new line characters in columns data. Hence, set property multiline as true.

val kivaloans=spark.read.format("com.databricks.spark.csv")
                        .option("multiline","true")
                        .option("header",true)
                        .option("inferschema","true")
                        .csv("kiva_loans.csv")

Data summary is as follows after setting multiline as true:

+-------+------------------+-----------------+-----------------+----------+-----------+--------------------+--------------------+------------------+------------+------------------+--------------------+--------------------+--------------------+--------------------+-----------------+------------------+--------------------+--------------------+--------------------+--------------------+
|summary|                id|    funded_amount|      loan_amount|  activity|     sector|                 use|        country_code|           country|      region|          currency|          partner_id|         posted_time|      disbursed_time|         funded_time|   term_in_months|      lender_count|                tags|    borrower_genders|  repayment_interval|                date|
+-------+------------------+-----------------+-----------------+----------+-----------+--------------------+--------------------+------------------+------------+------------------+--------------------+--------------------+--------------------+--------------------+-----------------+------------------+--------------------+--------------------+--------------------+--------------------+
|  count|            671205|           671205|           671205|    671205|     671205|              666977|              671197|            671205|      614441|            671186|              657699|              671195|              668808|              622890|           671196|            671199|              499834|              666957|              671191|              671199|
|   mean| 993248.5937336581|785.9950611214159|842.3971066961659|      null|       null|             10000.0|                null|              null|        null|              null|  178.20274555550654|  162.01020408163265|  179.12244897959184|               189.3|13.74266332047713|20.588457578299735|   25.68553459119497|                26.4|  26.210526315789473|  27.304347826086957|
| stddev|196611.27542282813|1130.398941057504|1198.660072882945|      null|       null|                 NaN|                null|              null|        null|              null|   94.24892231613454|   78.65564973356628|  100.70555939905975|  125.87299363372507|8.631922222356161|28.458485403188924|  31.131029407317044|   35.87289875191111|   52.43279244938066|   41.99181173710449|
|    min|            653047|              0.0|             25.0|Adult Care|AgricultuTo buy chicken.|  ""fajas"" [wove...| 10 boxes of cream| 3x1 purlins| T-shaped brackets| among other prod...| among other item...| and pay for labour"| and cassava to m...|            yeast|              rice|                milk| among other prod...|#Animals, #Biz Du...|            #Elderly|
|    25%|            823364|            250.0|            275.0|      null|       null|             10000.0|                null|              null|        null|              null|               126.0|               123.0|               105.0|                87.0|              8.0|               7.0|                 8.0|                 8.0|                 9.0|                 6.0|
|    50%|            992996|            450.0|            500.0|      null|       null|             10000.0|                null|              null|        null|              null|               145.0|               144.0|               144.0|               137.0|             13.0|              13.0|                14.0|                15.0|                14.0|                17.0|
|    75%|           1163938|            900.0|           1000.0|      null|       null|             10000.0|                null|              null|        null|              null|               204.0|               177.0|               239.0|               201.0|             14.0|              24.0|                27.0|                31.0|                24.0|                34.0|
|    max|           1340339|         100000.0|         100000.0| Wholesale|  Wholesale|? provide a safer...|                  ZW|          Zimbabwe|       ?ZM?T|           baguida|                 XOF|                 XOF|          Yoro, Yoro|                 USD|              USD|               USD|volunteer_pick, v...|volunteer_pick, v...|              weekly|volunteer_pick, v...|
+-------+------------------+-----------------+-----------------+----------+-----------+--------------------+--------------------+------------------+------------+------------------+--------------------+--------------------+--------------------+--------------------+-----------------+------------------+--------------------+--------------------+--------------------+--------------------+
user_94116960
Related