How to elegantly create a pyspark Dataframe from a csv file and convert it to a Pandas Dataframe?
<p>I'm having a CSV file which I want to read into an RDD or DataFrame. This is working so far, but if I collect the data and convert it into a pandas DataFrame for plotting the table is "malformed".</p>

<p>Here is how I read the CSV file:</p>

<pre><code>NUMERIC_DATA_FILE = os.path.join(DATA_DIR, "train_numeric.csv")
numeric_rdd = sc.textFile(NUMERIC_DATA_FILE)
numeric_rdd = numeric_rdd.mapPartitions(lambda x: csv.reader(x, delimiter=","))
numeric_df = sqlContext.createDataFrame(numeric_rdd)
numeric_df.registerTempTable("numeric")
</code></pre>

<p>The result looks like this:</p>

<p><a href="https://i.stack.imgur.com/fRwjI.png" rel="nofollow"><img src="https://i.stack.imgur.com/fRwjI.png" alt="enter image description here"></a></p>

<p>Is there an easy way to correctly set the first row of the CSV data to columns and the first column as index?</p>

<hr>

<p>This problem goes further as I try to select data from the <code>DataFrame</code>:</p>

<pre><code>numeric_df.select("SELECT Id FROM numeric")
</code></pre>

<p>which gives me:</p>

<pre><code>AnalysisException: u"cannot resolve 'SELECT Id FROM numeric' given input columns _799, _640, _963, _70, _364, _143, _167, 
_156, _553, _835, _780, _235, ...
</code></pre>