title: Approach to cleaning Data in spark
body: <p>I am a very new student of data engineering/machine learning and learning by myself. While working in a sample problem, I came across the following task of data cleaning</p>
  
  <pre><code>1. Remove extra whitespaces (keep one whitespace in between word but remove more 
  than one whitespaces) and punctuations
  
  2. Turn all the words to lower case and remove stop words (list from NLTK)
  
  3. Remove duplicate words in ASSEMBLY_NAME column
  </code></pre>
  
  <p>Though I've worked on writing code to perform these task during assignments in college, I've never done it in a single piece of code (for any project) and I am looking out for guidance here from experts who can help me by pointing towards the best approach to get it done <code>(in python or scala)</code></p>
  
  <p><strong>Work done so far:</strong></p>
  
  <p><strong>1. Read data from parquet file</strong></p>
  
  <pre><code>partFitmentDF = sqlContext.read.parquet("/mnt/blob/devdatasciencesto/pga-parts-forecast/raw/parts-fits/")
  
  display(partFitmentDF)
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Wlpq0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Wlpq0.png" alt="enter image description here"></a>
   <strong>2. Create table from DF</strong></p>
  
  <pre><code>partFitmentDF.createOrReplaceTempView("partsFits")
  partFitmentDF.write.mode("overwrite").format("delta").saveAsTable("partsFitsTable")
  </code></pre>
  
  <p><strong>3. Rearrange Data of fits_assembly_name in the table so that all the fits_assembly_name and fits_assembly_id roll over to single row for each distinct itemno</strong></p>
  
  <pre><code>%sql
  
  select itemno, concat_ws(' | ' , collect_set(cast(fits_assembly_id as int))) as fits_assembly_id, concat_ws(' | ' ,collect_set(fits_assembly_name)) as fits_assembly_name 
  from partsFitsTable 
  WHERE itemno = 1014584
  group by itemno
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XmwXK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XmwXK.png" alt="enter image description here"></a></p>
  
  <p><strong>P.S.</strong></p>
  
  <p><strong>Sample Data from selected columns of partFitmentDF</strong></p>
  
  <pre><code>itemno  fits_assembly_id        fits_assembly_name
  0450056 44011           OIL PUMP ASSEMBLY - A01EA09CA (4999202399920239A06)
  0450056 135502          OIL PUMP ASSEMBLY - A02EA09CA/CB/CC (4999202399920239A06)
  0450056 37884           OIL PUMP ASSEMBLY - A01EA05CA (4999202399920239A06)
  0450056 19618           OIL PUMP ASSEMBLY - A06FA09CA/CB/CC (4999202399920239A06)
  0450056 135021          OIL PUMP ASSEMBLY - A02EA05CA (4999202399920239A06)
  0450056 4147            OIL PUMP ASSEMBLY - A04KA05CA (4999202359920235A06)
  0450056 12003           OIL PUMP ASSEMBLY - A05FA09CA/CB/CC (4999202399920239A06)
  </code></pre>
  
  <p><strong>Now, I need to roll over these multiple rows into one row by item no <em>(all the assembly_name and id's belonging to one itemno should be in one row)</em> and then I need to perform task#1, 2 and 3 as listed at the very top to clean the fits_assembly_name column and save the processed data to final dataframe or table with itemno, fits_assembly_id and fits_assembly_name columns</strong> but I am not sure how to get started in python to do it. Could you please help me by suggesting the approach <em>(and hint of code)</em> so that I can take this task further?</p>