I am saving data to a csv file from a Pandas dataframe with 318477 rows using df.to_csv("preprocessed_data.csv") . When I load this file in another notebook with: df = pd.read_csv("preprocessed_data.csv") len(df) # out: 318477 The number of rows is as expected. However, when I try load the dataset with PySpark: spark_df = spark.read.format("csv") .option("header", "true") .option("mode", "DROPMALFORMED") .load("preprocessed_data.csv") spark_df.count() # out: 6422020 or df_test = spark.sql("SELECT * FROM csv.`preprocessed_data.csv`") df_test.count() # out: 6422020 The number of rows is incorrect. The number of rows it reads, 6422020, is the number of lines in the csv file. Since there are rows whose contents spans multiple lines (i.e. https://i.sstatic.net/PwvCg.jpg ) How can I solve this problem? Am I required to save the csv somehow with no newline charaters in any text, or can I specify the csv reading in PySpark more specifically? This is continuing on from my previous question, where I now understand the problem more link Lines from CSV file: 120,teacher industrial design technology mabel park state high school,teach queensland,2018-10-07,brisbane,southern suburbs logan,education training,teaching secondary,mabel park state high school invites applications for a industrial design and technology teacher,,0,30,,0.0,0.03003003003003003 121,fabricatorinstaller,workplace access safety,2018-10-07,melbourne,bayside south eastern suburbs,trades services,welders boilermakers,trade qualified person with skills in welding and fabrication to assist in the manufacturing and installation of our custom height safety products," * Secure long term role with genuine career path to supervisor * Competitive hourly rate with regular opportunity for overtime * Full on-the-job training About the role Having recently won a significant new national contract we are looking for another trade qualified person with welding and fabrication skills to help manage increased demands on our production and installation departments. This role will see you involved in both manufacturing and on-site installation and there is a genuine career path to supervisor if that is your goal. Initially your role will require you to:- <ul> <li>read and interpret drawings </li> <li>fabricate and assemble orders as required</li> <li>provide input to enhance factory processes</li> <li>pack and dispatch orders</li> <li>perform on-site installations (full training will be given)</li> </ul> About you This role is ideal for a trade qualified person (welder, boilermaker, fabricator etc) with good hands-on skills who will enjoy dividing their time between factory/manufacturing and on-site installations. Because installations invariably take place on the roof, physical fitness is essential. What we offer <ul> <li>A secure, long-term role with a successful, well-established organisation</li> <li>Full, ongoing on-the-job training</li> <li>Opportunity for career progression to supervisor for the right person</li> <li>Opportunity to work in a safe, supportive and friendly environment</li> <li>Competitive hourly rate with regular opportunities for overtime</li> <li>Occasional regional and interstate travel in response to major projects</li> </ul> How to apply Please copy and paste the URL below into your browser (it is not a live link so must be copied and pasted). This will take you to our custom online application form which includes a number of screening questions and a profiling checklist which is an essential part of our application process. https://exenet.expr3ss.com/jobDetails?selectJob=296& If you have any difficulties or would like more information please email <a class=""_2L3qcJ0"" data-contact-match=""true"" href=""mailto:gayle@exhr.com.au"">gayle@exhr.com.au</a> or phone <a class=""_2hhDNI-"" data-contact-match=""true"" href=""tel:0468 336 224"">0468 336 224</a>.",0,30,full time,0.0,0.03003003003003003 122,boilermaker,rpm contracting qld pl,2018-10-07,brisbane,southern suburbs logan,trades services,welders boilermakers,perm rate 30 structural steel fab weld out located southside full time hours ongoing work ot modern clean facility offering great conditions,"One of Australia's best engineering workshops is hiring! They have ongoing, rolling projects and need good people now. They are partnered with state and federal governments, international minerals and energy companies, and other market leading entities. The workshop is state of the art, clean, and well-managed. There is a genuine focus on the safety and wellbeing of their people. The facility and conditions are truly exceptional. Secure and long term positions are on offer for forward-thinking, cooperative and professional tradesmen. We are looking for qualified and/or ticketed boilermakers and 1st class welders that can offer high level trade skills. Equally important is a cooperative, team-orientated attitude and a willingness to become involved and take ownership of their important role in this company. They are building on a stable, permanent team, so candidates who step up can look forward to a secure future. The position is ongoing, offering full-time hours, exceptional conditions, and penalties. You require own car and licence, PPE and tools, relevant experience and to be available for an immediate start. Good luck and kind regards, RPM",0,30,full time,0.0,0.03003003003003003

Reputation: 93

PySpark is not reading CSV properly

I am saving data to a csv file from a Pandas dataframe with 318477 rows using df.to_csv("preprocessed_data.csv"). When I load this file in another notebook with:

df = pd.read_csv("preprocessed_data.csv")
len(df)

# out: 318477

The number of rows is as expected. However, when I try load the dataset with PySpark:

spark_df = spark.read.format("csv")
                     .option("header", "true")
                     .option("mode", "DROPMALFORMED")
                     .load("preprocessed_data.csv")
spark_df.count()

# out: 6422020

df_test = spark.sql("SELECT * FROM csv.`preprocessed_data.csv`")
df_test.count()

# out: 6422020

The number of rows is incorrect. The number of rows it reads, 6422020, is the number of lines in the csv file. Since there are rows whose contents spans multiple lines (i.e. https://i.sstatic.net/PwvCg.jpg)

How can I solve this problem? Am I required to save the csv somehow with no newline charaters in any text, or can I specify the csv reading in PySpark more specifically?

This is continuing on from my previous question, where I now understand the problem more link

Lines from CSV file:

120,teacher industrial design technology mabel park state high school,teach queensland,2018-10-07,brisbane,southern suburbs logan,education training,teaching secondary,mabel park state high school invites applications for a industrial design and technology teacher,,0,30,,0.0,0.03003003003003003
121,fabricatorinstaller,workplace access safety,2018-10-07,melbourne,bayside south eastern suburbs,trades services,welders boilermakers,trade qualified person with skills in welding and fabrication to assist in the manufacturing and installation of our custom height safety products,"<p>&nbsp;</p>
        <p><strong><em>*&nbsp; Secure long term role with genuine career path to supervisor</em></strong></p>
        <p><strong><em>*&nbsp; Competitive hourly rate with regular opportunity for overtime</em></strong></p>
        <p><strong><em>*&nbsp; Full on-the-job training</em></strong></p>
        <p><strong>About the&nbsp;role</strong></p>
        <p>Having recently won a significant new national contract we are looking for another trade qualified person with welding and fabrication skills to help manage increased demands on our production and installation departments.&nbsp; This role will
          see you involved in both manufacturing and on-site installation and there is a genuine career path to supervisor if that is your goal.&nbsp; Initially your role will require you to:-</p>
        <ul>
          <li>read and interpret drawings&nbsp;</li>
          <li>fabricate and assemble orders as required</li>
          <li>provide input to enhance factory processes</li>
          <li>pack&nbsp;and dispatch orders</li>
          <li>perform on-site installations (full training will be given)</li>
        </ul>
        <p><strong>About you</strong></p>
        <p>This role is ideal for a trade qualified person&nbsp;(welder, boilermaker, fabricator etc) with good hands-on skills who will enjoy&nbsp;dividing their time between&nbsp;factory/manufacturing and on-site installations.&nbsp; Because installations
          invariably take place on the roof, physical fitness is&nbsp;essential.</p>
        <p><strong>What we offer</strong></p>
        <ul>
          <li>A secure, long-term role with a successful, well-established organisation</li>
          <li>Full, ongoing on-the-job training</li>
          <li>Opportunity for career progression to supervisor for the right person</li>
          <li>Opportunity to work&nbsp;in a safe, supportive and friendly environment</li>
          <li>Competitive hourly rate with regular opportunities for overtime</li>
          <li>Occasional regional and interstate travel in response to major projects</li>
        </ul>
        <p><strong>How to apply</strong></p>
        <p>Please copy and paste the URL below into your browser (it is <em>not</em> a live link so&nbsp;must be copied and pasted).&nbsp; This will take you to our custom online application form which includes a number of screening questions&nbsp;and a
          profiling checklist which is an essential part of our application process.</p>
        <p><strong>https://exenet.expr3ss.com/jobDetails?selectJob=296&amp;</strong></p>
        <p>If you have any difficulties or would like more information please email <a class=""_2L3qcJ0"" data-contact-match=""true"" href=""mailto:[email protected]"">[email protected]</a> or phone <a class=""_2hhDNI-"" data-contact-match=""true"" href=""tel:0468 336 224"">0468 336 224</a>.</p>",0,30,full time,0.0,0.03003003003003003
122,boilermaker,rpm contracting qld pl,2018-10-07,brisbane,southern suburbs logan,trades services,welders boilermakers,perm rate 30 structural steel fab weld out located southside full time hours ongoing work ot modern clean facility offering great conditions,"<p>One of Australia's best engineering workshops is hiring!</p>
        <p>They have ongoing, rolling projects and need good people now.</p>
        <p>They are partnered with state and federal governments, international minerals and energy companies, and other market leading entities.</p>
        <p>The workshop is state of the art, clean, and well-managed. There is a genuine focus on the safety and wellbeing of their people.</p>
        <p>The facility and conditions are truly exceptional.</p>
        <p>Secure and long term positions are on offer for forward-thinking, cooperative and professional tradesmen.</p>
        <p>We are looking for qualified and/or ticketed boilermakers and 1st class welders that can offer high level trade skills.</p>
        <p>Equally important is a cooperative, team-orientated attitude and a willingness to become involved and take ownership of their important role in this company.</p>
        <p>They are building on a stable, permanent team, so candidates who step up can look forward to a secure future.</p>
        <p>The position is ongoing, offering full-time hours, exceptional conditions, and penalties.</p>
        <p>You require own car and licence, PPE and tools, relevant experience and to be available for an immediate start.</p>
        <p>Good luck and kind regards,</p>
        <p>RPM</p>",0,30,full time,0.0,0.03003003003003003

Upvotes: 4

Answers (2)

Kiran kumar

Reputation: 11

This due to installation of pyspark in windows machine. If you have multiple instances of Pyspark installed in your system. Then this issue occurs. By reinstalling Pyspark should fix this issue.

Upvotes: 1

iurii_n

Reputation: 1370

Based on provided example I tried to use following code which returned me 3 lines:

>>> df = spark.read.csv('file:///tmp/test.csv', sep=',', multiLine=True)
>>> df.count()
3

If it still does not work for you, I'd try to enforce pandas to use quotes and separator

Upvotes: 8

PySpark is not reading CSV properly

Answers (2)

Related Questions