13
$\begingroup$

How can I import a .csv file into pyspark dataframes? I even tried to read csv file in Pandas and then convert it to a spark dataframe using createDataFrame, but it is still showing some error. Can someone guide me through this? Also, please tell me how can I import an xlsx file? I'm trying to import csv content into pandas dataframes and then convert it into spark data frames, but it is showing the error:

"Py4JJavaError" An error occurred while calling o28.applySchemaToPythonRDD. : java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient 

My code is:

from pyspark import SparkContext 
from pyspark.sql import SQLContext 
import pandas as pd 
sqlc=SQLContext(sc) 
df=pd.read_csv(r'D:\BestBuy\train.csv') 
sdf=sqlc.createDataFrame(df) 
$\endgroup$
  • 1
    $\begingroup$ If you have an error message, you should post it; it most likely has important info in helping to debug the situation. $\endgroup$ – j.a.gartner Aug 1 '16 at 15:55
  • $\begingroup$ i'm trying to import csv contents into pandas dataframes and then converting it into spark data frames....but it is showing error something like "Py4JJavaError" An error occurred while calling o28.applySchemaToPythonRDD. : java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient $\endgroup$ – neha Aug 2 '16 at 4:59
  • $\begingroup$ and my code was--> from pyspark import SparkContext from pyspark.sql import SQLContext import pandas as pd sqlc=SQLContext(sc) df=pd.read_csv(r'D:\BestBuy\train.csv') sdf=sqlc.createDataFrame(df) ----> Error $\endgroup$ – neha Aug 2 '16 at 5:01
  • 1
    $\begingroup$ Welcome to DataScience.SE! Please edit your original post instead of adding comments. $\endgroup$ – Emre Aug 2 '16 at 7:54
  • $\begingroup$ file path must be in HDFS then only u can run the data $\endgroup$ – Prakash Reddy Mar 9 '19 at 12:47
14
$\begingroup$

"How can I import a .csv file into pyspark dataframes ?" -- there are many ways to do this; the simplest would be to start up pyspark with Databrick's spark-csv module. You can do this by starting pyspark with

pyspark --packages com.databricks:spark-csv_2.10:1.4.0

then you can follow the following steps:

from pyspark.sql import SQLContext
sqlContext = SQLContext(sc)

df = sqlContext.read.format('com.databricks.spark.csv').options(header='true', inferschema='true').load('cars.csv')

The other method would be to read in the text file as an rdd using

myrdd = sc.textFile("yourfile.csv").map(lambda line: line.split(","))

Then transform your data so that every item is in the correct format for the schema (i.e. Ints, Strings, Floats, etc.). You'll want to then use

>>> from pyspark.sql import Row
>>> Person = Row('name', 'age')
>>> person = rdd.map(lambda r: Person(*r))
>>> df2 = sqlContext.createDataFrame(person)
>>> df2.collect()
[Row(name=u'Alice', age=1)]
>>> from pyspark.sql.types import *
>>> schema = StructType([
...    StructField("name", StringType(), True),
...    StructField("age", IntegerType(), True)])
>>> df3 = sqlContext.createDataFrame(rdd, schema)
>>> df3.collect()
[Row(name=u'Alice', age=1)]

Reference: http://spark.apache.org/docs/1.6.1/api/python/pyspark.sql.html#pyspark.sql.Row

"Also, please tell me how can I import xlsx file?" -- Excel files are not used in "Big Data"; Spark is meant to be used with large files or databases. If you have an Excel file that is 50GB in size, then you're doing things wrong. Excel wouldn't even be able to open a file that size; from my experience, anything above 20MB and Excel dies.

| improve this answer | |
$\endgroup$
  • $\begingroup$ I think there may be an issue with the RDD approach above: fields may contain newlines (albeit surrounded by double-quotes), viz., tools.ietf.org/html/rfc4180#section-2. $\endgroup$ – flow2k Jun 17 '19 at 1:32
  • $\begingroup$ you may use tools to convert xlsx file to csv (things like gnumeric or open office apis). then you can do the data science as normal $\endgroup$ – vpathak Feb 12 at 17:00
2
$\begingroup$

Following worked well for me:

from pyspark.sql.types import *
schema = StructType([StructField("name", StringType(), True),StructField("age", StringType(), True)]
pd_df = pd.read_csv("<inputcsvfile>")
sp_df = spark.createDataFrame(pd_df, schema=schema)
| improve this answer | |
$\endgroup$
1
$\begingroup$

I have in my local directory a file 'temp.csv'. From there, using a local instance I do the following:

>>> from pyspark import SQLContext
>>> from pyspark.sql import Row
>>> sql_c = SQLContext(sc)
>>> d0 = sc.textFile('./temp.csv')
>>> d0.collect()
[u'a,1,.2390', u'b,2,.4390', u'c,3,.2323']
>>> d1 = d0.map(lambda x: x.split(',')).map(lambda x: Row(label = x[0], number = int(x[1]), value = float(x[2])))
>>> d1.take(1)
[Row(label=u'a', number=1, value=0.239)]
>>> df = sql_c.createDataFrame(d1)
>>> df_cut = df[df.number>1]
>>> df_cut.select('label', 'value').collect()
[Row(label=u'b', value=0.439), Row(label=u'c', value=0.2323)]

So d0 is the raw text file that we send off to a spark RDD. In order for you to make a data frame, you want to break the csv apart, and to make every entry a Row type, as I do when creating d1. The last step is to make the data frame from the RDD.

| improve this answer | |
$\endgroup$
0
$\begingroup$

You can use the package spark-csv by DataBricks that does a lot of things for you automatically, like taking care of the header, use escape characters, automatic schema inferring etcetera. Starting from Spark 2.0 there is an inbuilt function for dealing with CSVs.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.