You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Zeming Yu <ze...@gmail.com> on 2017/04/17 12:25:06 UTC

how to add new column using regular expression within pyspark dataframe

I've got a dataframe with a column looking like this:

display(flight.select("duration").show())

+--------+
|duration|
+--------+
|  15h10m|
|   17h0m|
|  21h25m|
|  14h30m|
|  24h50m|
|  26h10m|
|  14h30m|
|   23h5m|
|  21h30m|
|  11h50m|
|  16h10m|
|  15h15m|
|  21h25m|
|  14h25m|
|  14h40m|
|   16h0m|
|  24h20m|
|  14h30m|
|  14h25m|
|  14h30m|
+--------+
only showing top 20 rows



I need to extract the hour as a number and store it as an additional column
within the same dataframe. What's the best way to do that?


I tried the following, but it failed:

import re
def getHours(x):
  return re.match('([0-9]+(?=h))', x)
temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()
temp.select("duration").show()


error message:


---------------------------------------------------------------------------Py4JJavaError
                            Traceback (most recent call
last)<ipython-input-89-1d5bec255302> in <module>()      2 def
getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp
= flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()
  5 temp.select("duration").show()
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py
in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice',
age=1)]     56         """---> 57         return
sparkSession.createDataFrame(self, schema, sampleRatio)     58      59
    RDD.toDF = toDF
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py
in createDataFrame(self, data, schema, samplingRatio, verifySchema)
518     519         if isinstance(data, RDD):--> 520             rdd,
schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)
   521         else:    522             rdd, schema =
self._createFromLocal(map(prepare, data), schema)
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py
in _createFromRDD(self, rdd, schema, samplingRatio)    358         """
   359         if schema is None or isinstance(schema, (list,
tuple)):--> 360             struct = self._inferSchema(rdd,
samplingRatio)    361             converter =
_create_converter(struct)    362             rdd = rdd.map(converter)
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py
in _inferSchema(self, rdd, samplingRatio)    329         :return:
:class:`pyspark.sql.types.StructType`    330         """--> 331
 first = rdd.first()    332         if not first:    333
raise ValueError("The first row in RDD is empty, "
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py
in first(self)   1359         ValueError: RDD is empty   1360
"""-> 1361         rs = self.take(1)   1362         if rs:   1363
       return rs[0]
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py
in take(self, num)   1341    1342             p = range(partsScanned,
min(partsScanned + numPartsToTry, totalParts))-> 1343             res
= self.context.runJob(self, takeUpToNumLeft, p)   1344    1345
    items += res
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py
in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963
    # SparkContext#runJob.    964         mappedRDD =
rdd.mapPartitions(partitionFunc)--> 965         port =
self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd,
partitions)    966         return list(_load_from_socket(port,
mappedRDD._jrdd_deserializer))    967
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py
in __call__(self, *args)   1131         answer =
self.gateway_client.send_command(command)   1132         return_value
= get_return_value(-> 1133             answer, self.gateway_client,
self.target_id, self.name)   1134    1135         for temp_arg in
temp_args:
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py
in deco(*a, **kw)     61     def deco(*a, **kw):     62
try:---> 63             return f(*a, **kw)     64         except
py4j.protocol.Py4JJavaError as e:     65             s =
e.java_exception.toString()
C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py
in get_return_value(answer, gateway_client, target_id, name)    317
             raise Py4JJavaError(    318                     "An error
occurred while calling {0}{1}{2}.\n".--> 319
format(target_id, ".", name), value)    320             else:    321
              raise Py4JError(
Py4JJavaError: An error occurred while calling
z:org.apache.spark.api.python.PythonRDD.runJob.
: org.apache.spark.SparkException: Job aborted due to stage failure:
Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task
0.0 in stage 75.0 (TID 1035, localhost, executor driver):
org.apache.spark.api.python.PythonException: Traceback (most recent
call last):
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py",
line 174, in main
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py",
line 169, in process
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py",
line 272, in dump_stream
    bytes = self.serializer.dumps(vs)
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py",
line 427, in dumps
    return pickle.dumps(obj, protocol)
_pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>:
attribute lookup SRE_Match on _sre failed

	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
	at org.apache.spark.scheduler.Task.run(Task.scala:99)
	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

Driver stacktrace:
	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
	at scala.Option.foreach(Option.scala:257)
	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
	at py4j.Gateway.invoke(Gateway.java:280)
	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
	at py4j.commands.CallCommand.execute(CallCommand.java:79)
	at py4j.GatewayConnection.run(GatewayConnection.java:214)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.apache.spark.api.python.PythonException: Traceback
(most recent call last):
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py",
line 174, in main
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py",
line 169, in process
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py",
line 272, in dump_stream
    bytes = self.serializer.dumps(vs)
  File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py",
line 427, in dumps
    return pickle.dumps(obj, protocol)
_pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>:
attribute lookup SRE_Match on _sre failed

	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
	at org.apache.spark.scheduler.Task.run(Task.scala:99)
	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	... 1 more

Re: how to add new column using regular expression within pyspark dataframe

Posted by Павел <kn...@gmail.com>.
On Mon, Apr 17, 2017 at 3:25 PM, Zeming Yu <ze...@gmail.com> wrote:
> I've got a dataframe with a column looking like this:
>
> display(flight.select("duration").show())
>
> +--------+
> |duration|
> +--------+
> |  15h10m|
> |   17h0m|
> |  21h25m|
> |  14h25m|
> |  14h30m|
> +--------+
> only showing top 20 rows
>
>
>
> I need to extract the hour as a number and store it as an additional column
> within the same dataframe. What's the best way to do that?

You don't actually need to either switch to rdd context or use python
regexps here, which are slow. I'd suggest to try the "split" dataframe
sql function and the "getItem" column method. Bear in mind the
boundary case when duration is less than 1 hour, i.e. it might be
either 30m or 0h30m.

--
Pavel Knoblokh

---------------------------------------------------------------------
To unsubscribe e-mail: user-unsubscribe@spark.apache.org


Re: how to add new column using regular expression within pyspark dataframe

Posted by "颜发才 (Yan Facai)" <fa...@gmail.com>.
Don't use udf, as `minute` and `unix_timestamp` are native method of
spark.sql.


scala> df.withColumn("minute", minute(unix_timestamp($"str",
"HH'h'mm'm'").cast("timestamp"))).show





On Tue, Apr 25, 2017 at 7:55 AM, Zeming Yu <ze...@gmail.com> wrote:

> I tried this, but doesn't seem to work. Do you know how ot fix it?
>
> def getMinutes(aString):
>     return minute(unix_timestamp(aString, "HH'h'mm'm'").cast("timestamp"))
>
> udfGetMinutes = udf(getMinutes, IntegerType())
>
> flight2 = (flight2.withColumn('stop_duration1',
> udfGetMinutes(flight2.stop_duration1))
>           )
>
>
>
> On Sat, Apr 22, 2017 at 8:51 PM, 颜发才(Yan Facai) <fa...@gmail.com>
> wrote:
>
>> Hi, Zeming.
>>
>> I prefer to convert String to DateTime, like this:
>>
>> scala> val df = Seq("15h10m", "17h0m", "21h25m").toDF("str")
>>
>> scala> val ts = unix_timestamp($"str", "HH'h'mm'm'").cast("timestamp")
>>
>> scala> df.withColumn("minute", minute(ts)).show
>> +------+------+
>> |   str|minute|
>> +------+------+
>> |15h10m|    10|
>> | 17h0m |      0|
>> |21h25m|    25|
>> +------+------+
>>
>>
>> By the way, check Date-time function section of API:
>> http://spark.apache.org/docs/latest/api/scala/index.html#org
>> .apache.spark.sql.functions$
>>
>>
>>
>>
>> On Sat, Apr 22, 2017 at 6:27 PM, Zeming Yu <ze...@gmail.com> wrote:
>>
>>> Thanks a lot!
>>>
>>> Just another question, how can I extract the minutes as a number?
>>>
>>> I can use:
>>> .withColumn('duration_m',split(flight.duration,'h').getItem(1)
>>>
>>> to get strings like '10m'
>>>
>>> but how do I drop the charater "m" at the end? I can use substr(), but
>>> what's the function to get the length of the string so that I can do
>>> something like substr(1, len(...)-1)?
>>>
>>> On Thu, Apr 20, 2017 at 11:36 PM, Pushkar.Gujar <pushkarvgujar@gmail.com
>>> > wrote:
>>>
>>>> Can be as  simple as -
>>>>
>>>> from pyspark.sql.functions import split
>>>>
>>>> flight.withColumn('hour',split(flight.duration,'h').getItem(0))
>>>>
>>>>
>>>> Thank you,
>>>> *Pushkar Gujar*
>>>>
>>>>
>>>> On Thu, Apr 20, 2017 at 4:35 AM, Zeming Yu <ze...@gmail.com> wrote:
>>>>
>>>>> Any examples?
>>>>>
>>>>> On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:
>>>>>
>>>>>> How about using `withColumn` and UDF?
>>>>>>
>>>>>> example:
>>>>>> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
>>>>>> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
>>>>>> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> I've got a dataframe with a column looking like this:
>>>>>>>
>>>>>>> display(flight.select("duration").show())
>>>>>>>
>>>>>>> +--------+
>>>>>>> |duration|
>>>>>>> +--------+
>>>>>>> |  15h10m|
>>>>>>> |   17h0m|
>>>>>>> |  21h25m|
>>>>>>> |  14h30m|
>>>>>>> |  24h50m|
>>>>>>> |  26h10m|
>>>>>>> |  14h30m|
>>>>>>> |   23h5m|
>>>>>>> |  21h30m|
>>>>>>> |  11h50m|
>>>>>>> |  16h10m|
>>>>>>> |  15h15m|
>>>>>>> |  21h25m|
>>>>>>> |  14h25m|
>>>>>>> |  14h40m|
>>>>>>> |   16h0m|
>>>>>>> |  24h20m|
>>>>>>> |  14h30m|
>>>>>>> |  14h25m|
>>>>>>> |  14h30m|
>>>>>>> +--------+
>>>>>>> only showing top 20 rows
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> I need to extract the hour as a number and store it as an additional
>>>>>>> column within the same dataframe. What's the best way to do that?
>>>>>>>
>>>>>>>
>>>>>>> I tried the following, but it failed:
>>>>>>>
>>>>>>> import re
>>>>>>> def getHours(x):
>>>>>>>   return re.match('([0-9]+(?=h))', x)
>>>>>>> temp = flight.select("duration").rdd.map(lambda
>>>>>>> x:getHours(x[0])).toDF()
>>>>>>> temp.select("duration").show()
>>>>>>>
>>>>>>>
>>>>>>> error message:
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343
>>>>>>>
>>>>>>> ...
>
> [Message clipped]

Re: how to add new column using regular expression within pyspark dataframe

Posted by Zeming Yu <ze...@gmail.com>.
I tried this, but doesn't seem to work. Do you know how ot fix it?

def getMinutes(aString):
    return minute(unix_timestamp(aString, "HH'h'mm'm'").cast("timestamp"))

udfGetMinutes = udf(getMinutes, IntegerType())

flight2 = (flight2.withColumn('stop_duration1',
udfGetMinutes(flight2.stop_duration1))
          )



On Sat, Apr 22, 2017 at 8:51 PM, 颜发才(Yan Facai) <fa...@gmail.com> wrote:

> Hi, Zeming.
>
> I prefer to convert String to DateTime, like this:
>
> scala> val df = Seq("15h10m", "17h0m", "21h25m").toDF("str")
>
> scala> val ts = unix_timestamp($"str", "HH'h'mm'm'").cast("timestamp")
>
> scala> df.withColumn("minute", minute(ts)).show
> +------+------+
> |   str|minute|
> +------+------+
> |15h10m|    10|
> | 17h0m |      0|
> |21h25m|    25|
> +------+------+
>
>
> By the way, check Date-time function section of API:
> http://spark.apache.org/docs/latest/api/scala/index.html#
> org.apache.spark.sql.functions$
>
>
>
>
> On Sat, Apr 22, 2017 at 6:27 PM, Zeming Yu <ze...@gmail.com> wrote:
>
>> Thanks a lot!
>>
>> Just another question, how can I extract the minutes as a number?
>>
>> I can use:
>> .withColumn('duration_m',split(flight.duration,'h').getItem(1)
>>
>> to get strings like '10m'
>>
>> but how do I drop the charater "m" at the end? I can use substr(), but
>> what's the function to get the length of the string so that I can do
>> something like substr(1, len(...)-1)?
>>
>> On Thu, Apr 20, 2017 at 11:36 PM, Pushkar.Gujar <pu...@gmail.com>
>> wrote:
>>
>>> Can be as  simple as -
>>>
>>> from pyspark.sql.functions import split
>>>
>>> flight.withColumn('hour',split(flight.duration,'h').getItem(0))
>>>
>>>
>>> Thank you,
>>> *Pushkar Gujar*
>>>
>>>
>>> On Thu, Apr 20, 2017 at 4:35 AM, Zeming Yu <ze...@gmail.com> wrote:
>>>
>>>> Any examples?
>>>>
>>>> On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:
>>>>
>>>>> How about using `withColumn` and UDF?
>>>>>
>>>>> example:
>>>>> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
>>>>> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
>>>>> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:
>>>>>
>>>>>> I've got a dataframe with a column looking like this:
>>>>>>
>>>>>> display(flight.select("duration").show())
>>>>>>
>>>>>> +--------+
>>>>>> |duration|
>>>>>> +--------+
>>>>>> |  15h10m|
>>>>>> |   17h0m|
>>>>>> |  21h25m|
>>>>>> |  14h30m|
>>>>>> |  24h50m|
>>>>>> |  26h10m|
>>>>>> |  14h30m|
>>>>>> |   23h5m|
>>>>>> |  21h30m|
>>>>>> |  11h50m|
>>>>>> |  16h10m|
>>>>>> |  15h15m|
>>>>>> |  21h25m|
>>>>>> |  14h25m|
>>>>>> |  14h40m|
>>>>>> |   16h0m|
>>>>>> |  24h20m|
>>>>>> |  14h30m|
>>>>>> |  14h25m|
>>>>>> |  14h30m|
>>>>>> +--------+
>>>>>> only showing top 20 rows
>>>>>>
>>>>>>
>>>>>>
>>>>>> I need to extract the hour as a number and store it as an additional
>>>>>> column within the same dataframe. What's the best way to do that?
>>>>>>
>>>>>>
>>>>>> I tried the following, but it failed:
>>>>>>
>>>>>> import re
>>>>>> def getHours(x):
>>>>>>   return re.match('([0-9]+(?=h))', x)
>>>>>> temp = flight.select("duration").rdd.map(lambda
>>>>>> x:getHours(x[0])).toDF()
>>>>>> temp.select("duration").show()
>>>>>>
>>>>>>
>>>>>> error message:
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
>>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
>>>>>> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
>>>>>> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>>>     bytes = self.serializer.dumps(vs)
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>>>     return pickle.dumps(obj, protocol)
>>>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>>>
>>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>>>> 	at java.lang.Thread.run(Unknown Source)
>>>>>>
>>>>>> Driver stacktrace:
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
>>>>>> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
>>>>>> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>>>> 	at scala.Option.foreach(Option.scala:257)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
>>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
>>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
>>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
>>>>>> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
>>>>>> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
>>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
>>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
>>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
>>>>>> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
>>>>>> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
>>>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>>>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>>>>> 	at java.lang.reflect.Method.invoke(Unknown Source)
>>>>>> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
>>>>>> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
>>>>>> 	at py4j.Gateway.invoke(Gateway.java:280)
>>>>>> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
>>>>>> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
>>>>>> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
>>>>>> 	at java.lang.Thread.run(Unknown Source)
>>>>>> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>>>     bytes = self.serializer.dumps(vs)
>>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>>>     return pickle.dumps(obj, protocol)
>>>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>>>
>>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>>>> 	... 1 more
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>
>>
>

Re: how to add new column using regular expression within pyspark dataframe

Posted by "颜发才 (Yan Facai)" <fa...@gmail.com>.
Hi, Zeming.

I prefer to convert String to DateTime, like this:

scala> val df = Seq("15h10m", "17h0m", "21h25m").toDF("str")

scala> val ts = unix_timestamp($"str", "HH'h'mm'm'").cast("timestamp")

scala> df.withColumn("minute", minute(ts)).show
+------+------+
|   str|minute|
+------+------+
|15h10m|    10|
| 17h0m |      0|
|21h25m|    25|
+------+------+


By the way, check Date-time function section of API:
http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$




On Sat, Apr 22, 2017 at 6:27 PM, Zeming Yu <ze...@gmail.com> wrote:

> Thanks a lot!
>
> Just another question, how can I extract the minutes as a number?
>
> I can use:
> .withColumn('duration_m',split(flight.duration,'h').getItem(1)
>
> to get strings like '10m'
>
> but how do I drop the charater "m" at the end? I can use substr(), but
> what's the function to get the length of the string so that I can do
> something like substr(1, len(...)-1)?
>
> On Thu, Apr 20, 2017 at 11:36 PM, Pushkar.Gujar <pu...@gmail.com>
> wrote:
>
>> Can be as  simple as -
>>
>> from pyspark.sql.functions import split
>>
>> flight.withColumn('hour',split(flight.duration,'h').getItem(0))
>>
>>
>> Thank you,
>> *Pushkar Gujar*
>>
>>
>> On Thu, Apr 20, 2017 at 4:35 AM, Zeming Yu <ze...@gmail.com> wrote:
>>
>>> Any examples?
>>>
>>> On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:
>>>
>>>> How about using `withColumn` and UDF?
>>>>
>>>> example:
>>>> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
>>>> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
>>>> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>>>>
>>>>
>>>>
>>>> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:
>>>>
>>>>> I've got a dataframe with a column looking like this:
>>>>>
>>>>> display(flight.select("duration").show())
>>>>>
>>>>> +--------+
>>>>> |duration|
>>>>> +--------+
>>>>> |  15h10m|
>>>>> |   17h0m|
>>>>> |  21h25m|
>>>>> |  14h30m|
>>>>> |  24h50m|
>>>>> |  26h10m|
>>>>> |  14h30m|
>>>>> |   23h5m|
>>>>> |  21h30m|
>>>>> |  11h50m|
>>>>> |  16h10m|
>>>>> |  15h15m|
>>>>> |  21h25m|
>>>>> |  14h25m|
>>>>> |  14h40m|
>>>>> |   16h0m|
>>>>> |  24h20m|
>>>>> |  14h30m|
>>>>> |  14h25m|
>>>>> |  14h30m|
>>>>> +--------+
>>>>> only showing top 20 rows
>>>>>
>>>>>
>>>>>
>>>>> I need to extract the hour as a number and store it as an additional
>>>>> column within the same dataframe. What's the best way to do that?
>>>>>
>>>>>
>>>>> I tried the following, but it failed:
>>>>>
>>>>> import re
>>>>> def getHours(x):
>>>>>   return re.match('([0-9]+(?=h))', x)
>>>>> temp = flight.select("duration").rdd.map(lambda
>>>>> x:getHours(x[0])).toDF()
>>>>> temp.select("duration").show()
>>>>>
>>>>>
>>>>> error message:
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
>>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
>>>>> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
>>>>> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>>     bytes = self.serializer.dumps(vs)
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>>     return pickle.dumps(obj, protocol)
>>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>>
>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>>> 	at java.lang.Thread.run(Unknown Source)
>>>>>
>>>>> Driver stacktrace:
>>>>> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
>>>>> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
>>>>> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>>> 	at scala.Option.foreach(Option.scala:257)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
>>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
>>>>> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
>>>>> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
>>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
>>>>> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
>>>>> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
>>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>>>> 	at java.lang.reflect.Method.invoke(Unknown Source)
>>>>> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
>>>>> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
>>>>> 	at py4j.Gateway.invoke(Gateway.java:280)
>>>>> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
>>>>> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
>>>>> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
>>>>> 	at java.lang.Thread.run(Unknown Source)
>>>>> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>>     bytes = self.serializer.dumps(vs)
>>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>>     return pickle.dumps(obj, protocol)
>>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>>
>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>>> 	... 1 more
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>
>

Re: how to add new column using regular expression within pyspark dataframe

Posted by Zeming Yu <ze...@gmail.com>.
Thanks a lot!

Just another question, how can I extract the minutes as a number?

I can use:
.withColumn('duration_m',split(flight.duration,'h').getItem(1)

to get strings like '10m'

but how do I drop the charater "m" at the end? I can use substr(), but
what's the function to get the length of the string so that I can do
something like substr(1, len(...)-1)?

On Thu, Apr 20, 2017 at 11:36 PM, Pushkar.Gujar <pu...@gmail.com>
wrote:

> Can be as  simple as -
>
> from pyspark.sql.functions import split
>
> flight.withColumn('hour',split(flight.duration,'h').getItem(0))
>
>
> Thank you,
> *Pushkar Gujar*
>
>
> On Thu, Apr 20, 2017 at 4:35 AM, Zeming Yu <ze...@gmail.com> wrote:
>
>> Any examples?
>>
>> On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:
>>
>>> How about using `withColumn` and UDF?
>>>
>>> example:
>>> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
>>> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
>>> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>>>
>>>
>>>
>>> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:
>>>
>>>> I've got a dataframe with a column looking like this:
>>>>
>>>> display(flight.select("duration").show())
>>>>
>>>> +--------+
>>>> |duration|
>>>> +--------+
>>>> |  15h10m|
>>>> |   17h0m|
>>>> |  21h25m|
>>>> |  14h30m|
>>>> |  24h50m|
>>>> |  26h10m|
>>>> |  14h30m|
>>>> |   23h5m|
>>>> |  21h30m|
>>>> |  11h50m|
>>>> |  16h10m|
>>>> |  15h15m|
>>>> |  21h25m|
>>>> |  14h25m|
>>>> |  14h40m|
>>>> |   16h0m|
>>>> |  24h20m|
>>>> |  14h30m|
>>>> |  14h25m|
>>>> |  14h30m|
>>>> +--------+
>>>> only showing top 20 rows
>>>>
>>>>
>>>>
>>>> I need to extract the hour as a number and store it as an additional
>>>> column within the same dataframe. What's the best way to do that?
>>>>
>>>>
>>>> I tried the following, but it failed:
>>>>
>>>> import re
>>>> def getHours(x):
>>>>   return re.match('([0-9]+(?=h))', x)
>>>> temp = flight.select("duration").rdd.map(lambda
>>>> x:getHours(x[0])).toDF()
>>>> temp.select("duration").show()
>>>>
>>>>
>>>> error message:
>>>>
>>>>
>>>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
>>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
>>>> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
>>>> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>     bytes = self.serializer.dumps(vs)
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>     return pickle.dumps(obj, protocol)
>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>
>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>> 	at java.lang.Thread.run(Unknown Source)
>>>>
>>>> Driver stacktrace:
>>>> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
>>>> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
>>>> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
>>>> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>>> 	at scala.Option.foreach(Option.scala:257)
>>>> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
>>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
>>>> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
>>>> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
>>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
>>>> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
>>>> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>>> 	at java.lang.reflect.Method.invoke(Unknown Source)
>>>> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
>>>> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
>>>> 	at py4j.Gateway.invoke(Gateway.java:280)
>>>> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
>>>> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
>>>> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
>>>> 	at java.lang.Thread.run(Unknown Source)
>>>> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>>     bytes = self.serializer.dumps(vs)
>>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>>     return pickle.dumps(obj, protocol)
>>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>>
>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>>> 	... 1 more
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>

Re: how to add new column using regular expression within pyspark dataframe

Posted by "Pushkar.Gujar" <pu...@gmail.com>.
Can be as  simple as -

from pyspark.sql.functions import split

flight.withColumn('hour',split(flight.duration,'h').getItem(0))


Thank you,
*Pushkar Gujar*


On Thu, Apr 20, 2017 at 4:35 AM, Zeming Yu <ze...@gmail.com> wrote:

> Any examples?
>
> On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:
>
>> How about using `withColumn` and UDF?
>>
>> example:
>> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
>> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
>> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>>
>>
>>
>> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:
>>
>>> I've got a dataframe with a column looking like this:
>>>
>>> display(flight.select("duration").show())
>>>
>>> +--------+
>>> |duration|
>>> +--------+
>>> |  15h10m|
>>> |   17h0m|
>>> |  21h25m|
>>> |  14h30m|
>>> |  24h50m|
>>> |  26h10m|
>>> |  14h30m|
>>> |   23h5m|
>>> |  21h30m|
>>> |  11h50m|
>>> |  16h10m|
>>> |  15h15m|
>>> |  21h25m|
>>> |  14h25m|
>>> |  14h40m|
>>> |   16h0m|
>>> |  24h20m|
>>> |  14h30m|
>>> |  14h25m|
>>> |  14h30m|
>>> +--------+
>>> only showing top 20 rows
>>>
>>>
>>>
>>> I need to extract the hour as a number and store it as an additional
>>> column within the same dataframe. What's the best way to do that?
>>>
>>>
>>> I tried the following, but it failed:
>>>
>>> import re
>>> def getHours(x):
>>>   return re.match('([0-9]+(?=h))', x)
>>> temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()
>>> temp.select("duration").show()
>>>
>>>
>>> error message:
>>>
>>>
>>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
>>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
>>> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
>>> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>     bytes = self.serializer.dumps(vs)
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>     return pickle.dumps(obj, protocol)
>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>
>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>> 	at java.lang.Thread.run(Unknown Source)
>>>
>>> Driver stacktrace:
>>> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
>>> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
>>> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
>>> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>>> 	at scala.Option.foreach(Option.scala:257)
>>> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
>>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
>>> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
>>> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
>>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
>>> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
>>> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>>> 	at java.lang.reflect.Method.invoke(Unknown Source)
>>> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
>>> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
>>> 	at py4j.Gateway.invoke(Gateway.java:280)
>>> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
>>> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
>>> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
>>> 	at java.lang.Thread.run(Unknown Source)
>>> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>>     bytes = self.serializer.dumps(vs)
>>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>>     return pickle.dumps(obj, protocol)
>>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>>
>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>>> 	... 1 more
>>>
>>>
>>>
>>>
>>>
>>>
>>

Re: how to add new column using regular expression within pyspark dataframe

Posted by Zeming Yu <ze...@gmail.com>.
Any examples?

On 20 Apr. 2017 3:44 pm, "颜发才(Yan Facai)" <fa...@gmail.com> wrote:

> How about using `withColumn` and UDF?
>
> example:
> + https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
> <https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
> + https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/
>
>
>
> On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:
>
>> I've got a dataframe with a column looking like this:
>>
>> display(flight.select("duration").show())
>>
>> +--------+
>> |duration|
>> +--------+
>> |  15h10m|
>> |   17h0m|
>> |  21h25m|
>> |  14h30m|
>> |  24h50m|
>> |  26h10m|
>> |  14h30m|
>> |   23h5m|
>> |  21h30m|
>> |  11h50m|
>> |  16h10m|
>> |  15h15m|
>> |  21h25m|
>> |  14h25m|
>> |  14h40m|
>> |   16h0m|
>> |  24h20m|
>> |  14h30m|
>> |  14h25m|
>> |  14h30m|
>> +--------+
>> only showing top 20 rows
>>
>>
>>
>> I need to extract the hour as a number and store it as an additional
>> column within the same dataframe. What's the best way to do that?
>>
>>
>> I tried the following, but it failed:
>>
>> import re
>> def getHours(x):
>>   return re.match('([0-9]+(?=h))', x)
>> temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()
>> temp.select("duration").show()
>>
>>
>> error message:
>>
>>
>> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
>> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
>> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
>> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>     bytes = self.serializer.dumps(vs)
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>     return pickle.dumps(obj, protocol)
>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>
>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>> 	at java.lang.Thread.run(Unknown Source)
>>
>> Driver stacktrace:
>> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
>> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
>> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
>> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
>> 	at scala.Option.foreach(Option.scala:257)
>> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
>> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
>> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
>> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
>> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
>> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
>> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
>> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>> 	at java.lang.reflect.Method.invoke(Unknown Source)
>> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
>> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
>> 	at py4j.Gateway.invoke(Gateway.java:280)
>> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
>> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
>> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
>> 	at java.lang.Thread.run(Unknown Source)
>> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>>     bytes = self.serializer.dumps(vs)
>>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>>     return pickle.dumps(obj, protocol)
>> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>>
>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
>> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
>> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
>> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
>> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
>> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
>> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
>> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
>> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
>> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
>> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
>> 	... 1 more
>>
>>
>>
>>
>>
>>
>

Re: how to add new column using regular expression within pyspark dataframe

Posted by "颜发才 (Yan Facai)" <fa...@gmail.com>.
How about using `withColumn` and UDF?

example:
+ https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78
<https://gist.github.com/zoltanctoth/2deccd69e3d1cde1dd78>
+ https://ragrawal.wordpress.com/2015/10/02/spark-custom-udf-example/



On Mon, Apr 17, 2017 at 8:25 PM, Zeming Yu <ze...@gmail.com> wrote:

> I've got a dataframe with a column looking like this:
>
> display(flight.select("duration").show())
>
> +--------+
> |duration|
> +--------+
> |  15h10m|
> |   17h0m|
> |  21h25m|
> |  14h30m|
> |  24h50m|
> |  26h10m|
> |  14h30m|
> |   23h5m|
> |  21h30m|
> |  11h50m|
> |  16h10m|
> |  15h15m|
> |  21h25m|
> |  14h25m|
> |  14h40m|
> |   16h0m|
> |  24h20m|
> |  14h30m|
> |  14h25m|
> |  14h30m|
> +--------+
> only showing top 20 rows
>
>
>
> I need to extract the hour as a number and store it as an additional
> column within the same dataframe. What's the best way to do that?
>
>
> I tried the following, but it failed:
>
> import re
> def getHours(x):
>   return re.match('([0-9]+(?=h))', x)
> temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()
> temp.select("duration").show()
>
>
> error message:
>
>
> ---------------------------------------------------------------------------Py4JJavaError                             Traceback (most recent call last)<ipython-input-89-1d5bec255302> in <module>()      2 def getHours(x):      3   return re.match('([0-9]+(?=h))', x)----> 4 temp = flight.select("duration").rdd.map(lambda x:getHours(x[0])).toDF()      5 temp.select("duration").show()
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in toDF(self, schema, sampleRatio)     55         [Row(name=u'Alice', age=1)]     56         """---> 57         return sparkSession.createDataFrame(self, schema, sampleRatio)     58      59     RDD.toDF = toDF
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in createDataFrame(self, data, schema, samplingRatio, verifySchema)    518     519         if isinstance(data, RDD):--> 520             rdd, schema = self._createFromRDD(data.map(prepare), schema, samplingRatio)    521         else:    522             rdd, schema = self._createFromLocal(map(prepare, data), schema)
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _createFromRDD(self, rdd, schema, samplingRatio)    358         """    359         if schema is None or isinstance(schema, (list, tuple)):--> 360             struct = self._inferSchema(rdd, samplingRatio)    361             converter = _create_converter(struct)    362             rdd = rdd.map(converter)
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\session.py in _inferSchema(self, rdd, samplingRatio)    329         :return: :class:`pyspark.sql.types.StructType`    330         """--> 331         first = rdd.first()    332         if not first:    333             raise ValueError("The first row in RDD is empty, "
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in first(self)   1359         ValueError: RDD is empty   1360         """-> 1361         rs = self.take(1)   1362         if rs:   1363             return rs[0]
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\rdd.py in take(self, num)   1341    1342             p = range(partsScanned, min(partsScanned + numPartsToTry, totalParts))-> 1343             res = self.context.runJob(self, takeUpToNumLeft, p)   1344    1345             items += res
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\context.py in runJob(self, rdd, partitionFunc, partitions, allowLocal)    963         # SparkContext#runJob.    964         mappedRDD = rdd.mapPartitions(partitionFunc)--> 965         port = self._jvm.PythonRDD.runJob(self._jsc.sc(), mappedRDD._jrdd, partitions)    966         return list(_load_from_socket(port, mappedRDD._jrdd_deserializer))    967
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\java_gateway.py in __call__(self, *args)   1131         answer = self.gateway_client.send_command(command)   1132         return_value = get_return_value(-> 1133             answer, self.gateway_client, self.target_id, self.name)   1134    1135         for temp_arg in temp_args:
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\pyspark\sql\utils.py in deco(*a, **kw)     61     def deco(*a, **kw):     62         try:---> 63             return f(*a, **kw)     64         except py4j.protocol.Py4JJavaError as e:     65             s = e.java_exception.toString()
> C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\py4j-0.10.4-src.zip\py4j\protocol.py in get_return_value(answer, gateway_client, target_id, name)    317                 raise Py4JJavaError(    318                     "An error occurred while calling {0}{1}{2}.\n".--> 319                     format(target_id, ".", name), value)    320             else:    321                 raise Py4JError(
> Py4JJavaError: An error occurred while calling z:org.apache.spark.api.python.PythonRDD.runJob.
> : org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 75.0 failed 1 times, most recent failure: Lost task 0.0 in stage 75.0 (TID 1035, localhost, executor driver): org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>     bytes = self.serializer.dumps(vs)
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>     return pickle.dumps(obj, protocol)
> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>
> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
> 	at java.lang.Thread.run(Unknown Source)
>
> Driver stacktrace:
> 	at org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1435)
> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1423)
> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1422)
> 	at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
> 	at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
> 	at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1422)
> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
> 	at org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:802)
> 	at scala.Option.foreach(Option.scala:257)
> 	at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:802)
> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1650)
> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1605)
> 	at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1594)
> 	at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)
> 	at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:628)
> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1918)
> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1931)
> 	at org.apache.spark.SparkContext.runJob(SparkContext.scala:1944)
> 	at org.apache.spark.api.python.PythonRDD$.runJob(PythonRDD.scala:441)
> 	at org.apache.spark.api.python.PythonRDD.runJob(PythonRDD.scala)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> 	at java.lang.reflect.Method.invoke(Unknown Source)
> 	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
> 	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
> 	at py4j.Gateway.invoke(Gateway.java:280)
> 	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
> 	at py4j.commands.CallCommand.execute(CallCommand.java:79)
> 	at py4j.GatewayConnection.run(GatewayConnection.java:214)
> 	at java.lang.Thread.run(Unknown Source)
> Caused by: org.apache.spark.api.python.PythonException: Traceback (most recent call last):
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 174, in main
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\worker.py", line 169, in process
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 272, in dump_stream
>     bytes = self.serializer.dumps(vs)
>   File "C:\spark-2.1.0-bin-hadoop2.7\spark-2.1.0-bin-hadoop2.7\python\lib\pyspark.zip\pyspark\serializers.py", line 427, in dumps
>     return pickle.dumps(obj, protocol)
> _pickle.PicklingError: Can't pickle <class '_sre.SRE_Match'>: attribute lookup SRE_Match on _sre failed
>
> 	at org.apache.spark.api.python.PythonRunner$$anon$1.read(PythonRDD.scala:193)
> 	at org.apache.spark.api.python.PythonRunner$$anon$1.<init>(PythonRDD.scala:234)
> 	at org.apache.spark.api.python.PythonRunner.compute(PythonRDD.scala:152)
> 	at org.apache.spark.api.python.PythonRDD.compute(PythonRDD.scala:63)
> 	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
> 	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
> 	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
> 	at org.apache.spark.scheduler.Task.run(Task.scala:99)
> 	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
> 	... 1 more
>
>
>
>
>
>