topic Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python in Data Engineering

Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

desai_n_3 — Thu, 03 Oct 2019 08:41:05 GMT

Hi All,

I am trying to convert a dataframe column which is in the format of string to date type format yyyy-MM-DD?

I have written a sql query and stored it in dataframe.

df3 = sqlContext.sql(sqlString2)
df3.withColumn(df3['CalDay'],pd.to_datetime(df3['CalDay'],format = '%Y%m%d'))

I get this error

ValueError: Cannot convert column into bool: please use '&' for 'and', '|' for 'or', '~' for 'not' when building DataFrame boolean expressions.

How is this happening?

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

shyam_9 — Thu, 03 Oct 2019 09:56:44 GMT

Hi @desai.n.3, In scala please use coalesce function to convert Date format,

df.withColumn("Date", coalesce(
  to_timestamp($"Date", "yyyyMMdd"))).show<br>

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

desai_n_3 — Thu, 03 Oct 2019 10:58:56 GMT

Hi Thanks for the reply,

It says coalesce fucntion not defined, plus gives invalid syntax with $

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

shyam_9 — Thu, 03 Oct 2019 15:12:38 GMT

If you are using the Databricks notebook the above code works and for Pandas use below code,

df['Date']= pd.to_datetime(df['Date'])

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

JoshuaJames — Fri, 25 Oct 2019 05:56:54 GMT

You gave him scala code, his code is obviously python

"Databricks notebooks" can be python, scala even SQL so my advice is check the question and answer in the language they are using at least (both if you know it for future people)

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

JoshuaJames — Fri, 25 Oct 2019 06:06:21 GMT

Registered to post this so forgive the formatting nightmare

This is a python databricks script function that allows you to convert from string to datetime or date and utilising coalesce

from pyspark.sql.functions import coalesce, to_date

def to_datetime_(col, format): # Spark 2.2 or later syntax, for < 2.2 use unix_timestamp and cast return coalesce(to_date(col, format))

df3 = sqlContext.sql(sqlString2)df3.withColumn(df3['CalDay'],pd.to_datetime_(df3['CalDay'],format = '%Y%m%d'))

Re: Cannot Convert Column to Bool Error - When Converting dataframe column which is in string to date type in python

JoshuaJames — Fri, 25 Oct 2019 06:08:23 GMT

the resulting comment is looking nothing like it does in edit mode

this is the function

def to_datetime_(col, format): 
# Spark 2.2 or later syntax, for < 2.2 use unix_timestamp and cast 
    return coalesce(to_date(col, format))