Adding column from dataframe(df1) to another dataframe (df2)

Question

I need some help with this Apache Spark (pyspark) issue.

I've a dataFrame (df1) which has a single column & a single row, it contains max_timestamp

+------------------+ |max_timestamp | +-------------------+ |2019-10-24 21:18:26| +-------------------+

I've another DataFrame, which contains 2 Columns - EmpId & Timestamp

masterData = [(1, '1999-10-24 21:18:23',), (1, '2019-10-24 21:18:26',), (2, '2020-01-24 21:18:26',)]
df_masterdata = spark.createDataFrame(masterData, ['dsid', 'txnTime_str'])
df_masterdata = df_masterdata.withColumn('txnTime_ts', col('txnTime_str').cast(TimestampType())).drop('txnTime_str')

df_masterdata.show(5, False)

+----+-------------------+
|dsid|txnTime_ts         |
+----+-------------------+
|1   |1999-10-24 21:18:23|
|1   |2019-10-24 21:18:26|
|2   |2020-01-24 21:18:26|
+----+-------------------+

Object is to filter the records in the 2nd Dataframe, based on condition txnTime_ts < max_timestamp

What i'm trying to do -> add the column 'max_timestamp' to the 2nd DataFrame, and filter records by comparing the 2 values.

df_masterdata1 = df_masterdata.withColumn('maxTime', maxTS2['TEMP_MAX'])

Pyspark does not let me add the column from maxTS2 to the dataFrame - df_masterdata

Error -

AnalysisException: 'Resolved attribute(s) TEMP_MAX#207255 missing from dsid#207263L,txnTime_ts#207267 in operator
!Project [dsid#207263L, txnTime_ts#207267, TEMP_MAX#207255 AS maxTime#207280].;;\n!Project [dsid#207263L,
txnTime_ts#207267, TEMP_MAX#207255 AS maxTime#207280]\n+- Project [dsid#207263L, txnTime_ts#207267]\n   +- Project
[dsid#207263L, txnTime_str#207264, cast(txnTime_str#207264 as timestamp) AS txnTime_ts#207267]\n      +- LogicalRDD
[dsid#207263L, txnTime_str#207264], false\n'

Any ideas on how to resolve this issue?

Charlie Flowers Charlie Flowers · Accepted Answer · 2019-10-25T00:38:06

If you actually have a DF with a single row/column, the most efficient way to accomplish this would be to extract the value from the dataframe and then filter df_masterdata against it. If you nevertheless need to do this within the context of a dataframe, you should us join , e.g.:

df_masterdata1 = df_masterdata.join(df1, df_masterdata.txnTime_ts <= df1.max_timestamp)

Adding column from dataframe(df1) to another dataframe (df2)

1 Answers