python 3.x - Data type conversion in spark

Question

I have an column id which had type int but later changed to bigint. It has both types of values.

from pyspark.sql.functions import *
from pyspark.sql.types import * 

df = spark.read.parquet('hdfs path')
df = df.select("id", "code")

df=df.withColumn("id1", df["id"].cast(LongType()))
res1=df.select("id1", "code")

res1.show(1, False)

It shows me the data frame but when i try to perform some operations on them example:

res1.groupBy('code').agg(countDistinct("id1")).show(1, False)

I get Column: [id], Expected: int, Found: INT64

I tried mergeSchema did not work either.

Answer 1

from pyspark.sql.functions import *
from pyspark.sql.types import * 

df1 = spark.read.parquet('hdfs path')
df2 = df1.select("id", "code")

df3 = df2.withColumn("id1", df2["id"].cast(LongType()))
res1=df3.select("id1", "code")

res1.show(1, False)

res1.groupBy("code").agg(countDistinct("id1")).show(1, False)

This should work. In spark Dataframes are immutable so you should not assign the value of transformation operation to a same df variable, you should use a different variable name. In scala it would give you compile time error but in python its allowed so you don't notice it.

if you want you could also chain all of your transformation and get a single df variable and perform groupby operation on it as below :

df = spark.read.parquet('hdfs path').select("id", "code").withColumn("id1", col("id").cast(LongType())).select("id1", "code")
df.groupBy("code").agg(countDistinct("id1")).show(1, False)