How to modify/transform the column of a dataframe?

Question

I have an instance of pyspark.sql.dataframe.DataFrame created using

dataframe = sqlContext.sql("select * from table").

One column is 'arrival_date' and contains a string.

How do I modify this column so as to only take the first 4 characters from it and throw away the rest?

How would I convert the type of this column from string to date?

In graphlab.SFrame, this would be:

dataframe['column_name'] = dataframe['column_name'].apply(lambda x: x[:4] )

and

dataframe['column_name'] = dataframe['column_name'].str_to_datetime()

@Orions I just had not had time to test out the proposed solutions yet. Thanks for your help. — Semihcan Doken, Aug 22 '16 at 03:46
I wanted to give myself enough time to test each answer and find one that works before accepting one. Thank you for your reminder. I will do this now. And thanks for your help again. — Semihcan Doken, Aug 22 '16 at 06:22

Daniel de Paula · Accepted Answer · 2016-08-22T07:19:01.270

6

As stated by Orions, you can't modify a column, but you can override it. Also, you shouldn't need to create an user defined function, as there is a built-in function for extracting substrings:

from pyspark.sql.functions import *
df = df.withColumn("arrival_date", df['arrival_date'].substr(0, 4))

To convert it to date, you can use to_date, as Orions said:

from pyspark.sql.functions import *
df = df.withColumn("arrival_date", to_date(df['arrival_date'].substr(0, 4)))

However, if you need to specify the format, you should use unix_timestamp:

from pyspark.sql.functions import *
format = 'yyMM'
col = unix_timestamp(df['arrival_date'].substr(0, 4), format).cast('timestamp')
df = df.withColumn("arrival_date", col)

All this can be found in the pyspark documentation.

edited Aug 22 '16 at 07:19

answered Aug 21 '16 at 14:38

Daniel de Paula

17,362
9
71
72

Thanks! I tried this and got no error but all the values in the new column became 'null' instead of a meaningful number. Do you think the issue was the format which I chose as "mm/dd/yyyy"? (Dates are in the format of 5/16/2016) – Semihcan Doken Aug 22 '16 at 06:58
@Semihcan you need to adapt the format based on what is in the string. How are the values in the original column like? – Daniel de Paula Aug 22 '16 at 06:59
May 16th 2016 would be like 5/16/2016, which looks like m/dd/yyyy but December 15 2016 is 12/16/2016 which is like mm/dd/yyyy So it is tricky for me to know whether to enter mm/dd/yyyy or m/dd/yyyy etc – Semihcan Doken Aug 22 '16 at 07:02
@Semihcan. So you need more than just 4 characters. And you can use "M/dd/yyyy" as the format – Daniel de Paula Aug 22 '16 at 07:04
I am using the entire string in the column now instead of just 4 characters. (I had originally asked for 4 characters to separate year) Will using M/D/yyyy work also? Since 12/3/2016 is also a possible date? – Semihcan Doken Aug 22 '16 at 07:10
@Semihcan, you can try "M/d/yyyy". I can't test it right now, but I think it would work – Daniel de Paula Aug 22 '16 at 07:12
Also in your answer you seem to use `df` and `old_df`. I am guessing `df` also refers to the old df? – Semihcan Doken Aug 22 '16 at 07:13
1

@Semihcan, yes df=old_df. Sorry – Daniel de Paula Aug 22 '16 at 07:14
I tried `format = 'm/d/yyyy' col = unix_timestamp(dataframe['arrival_date'], format).cast('timestamp') new_df = dataframe.withColumn("arrival_date", col)` For some reason this yields to 1/5/2016 being converted to 1970-01-17T19:19:12.060+0000 – Semihcan Doken Aug 22 '16 at 07:20
@Semihcan, have you tried with uppercase "M"? Lowercase is for minutes – Daniel de Paula Aug 22 '16 at 07:22
Yes, I tried both Upper "M" and lower case "m". Same with 'd'. Still getting 1970. – Semihcan Doken Aug 22 '16 at 07:27
@Semihcan, are you using spark version 1.6 or higher? – Daniel de Paula Aug 22 '16 at 07:28
I am using 1.5.2. (this is what databricks offers me) – Semihcan Doken Aug 22 '16 at 07:28
1

@Semihcan, ok. There is a bug in your version. You must multiply the result of Unix_timestamp() by 1000 before casting – Daniel de Paula Aug 22 '16 at 07:30

score 4 · Answer 2 · edited May 23 '17 at 12:30

To extract first 4 characters from the arrival_date (StringType) column, create a new_df by using UserDefinedFunction (as you cannot modify the columns: they are immutable):

from pyspark.sql.functions import UserDefinedFunction, to_date

old_df = spark.sql("SELECT * FROM table")
udf = UserDefinedFunction(lambda x: str(x)[:4], StringType())
new_df = old_df.select(*[udf(column).alias('arrival_date') if column == 'arrival_date' else column for column in old_df.columns])

And to covert the arrival_date (StringType) column into DateType column, use the to_date function as show below:

new_df = old_df.select(old_df.other_cols_if_any, to_date(old_df.arrival_date).alias('arrival_date'))

Sources:
https://stackoverflow.com/a/29257220/2873538
https://databricks.com/blog/2015/09/16/apache-spark-1-5-dataframe-api-highlights.html

See [Daniel de Paula's answer](https://stackoverflow.com/a/39065383/2738164) for a much more efficient way to accomplish a new substring column. Declaring and using a UDF for this is extremely inefficient, and you can easily add a column to an existing dataframe without needing to build an entirely new one as well. — bsplosion, Dec 16 '19 at 16:56

How to modify/transform the column of a dataframe?

2 Answers2