Atharv Thakur
Reputation: 701
Left Outer join for unequla records fro two data frames in spark scala
I have two data frame . Data Frame one
+-------------+-------------------------+--------------+--------+----------+-----------------------+---------------------+-------------------+-----------------------+--------------------------+--------------------------+-----------+
|DataPartition|TimeStamp |OrganizationID|SourceID|_auditorId|sr:AuditorEnumerationId|sr:AuditorOpinionCode|sr:AuditorOpinionId|sr:IsPlayingAuditorRole|sr:IsPlayingCSRAuditorRole|sr:IsPlayingTaxAdvisorRole|FFAction|!||
+-------------+-------------------------+--------------+--------+----------+-----------------------+---------------------+-------------------+-----------------------+--------------------------+--------------------------+-----------+
|Japan |2018-05-03T09:52:48+00:00|4295876589 |195 |null |null |null |null |null |null |null |O|!| |
|Japan |2018-05-03T08:10:19+00:00|4295876589 |196 |null |null |null |null |null |null |null |D|!| |
|Japan |2018-05-03T09:52:48+00:00|4295876589 |194 |null |null |null |null |null |null |null |O|!| |
+-------------+-------------------------+--------------+--------+----------+-----------------------+---------------------+-------------------+-----------------------+--------------------------+--------------------------+-----------+
Data Frame Two is
DataPartition TimeStamp OrganizationID SourceID _auditorId sr:AuditorEnumerationId sr:AuditorOpinionCode sr:AuditorOpinionId sr:IsPlayingAuditorRole sr:IsPlayingCSRAuditorRole sr:IsPlayingTaxAdvisorRole FFAction|!|
Japan 2018-05-03T08:06:06+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T08:06:06+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T09:48:33+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:48:33+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T07:27:10+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:27:10+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:27:10+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:35:42+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:35:42+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:35:42+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T09:34:46+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:34:46+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T08:10:19+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T08:10:19+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T07:28:16+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:28:16+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:28:16+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-02T09:05:04+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-02T09:05:04+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-02T09:05:04+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:31:28+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:31:28+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:31:28+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:22:58+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:22:58+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:22:58+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T09:45:22+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:45:22+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T07:11:26+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:11:26+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:11:26+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:00:45+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:00:45+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:00:45+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:36:47+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:36:47+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:36:47+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:01:52+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:01:52+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:01:52+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-02T10:28:22+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-02T10:28:22+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-02T10:28:22+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T09:52:48+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:52:48+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T09:41:09+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:41:09+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-02T10:30:32+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-02T10:30:32+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-02T10:30:32+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T06:56:32+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T06:56:32+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T06:56:32+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T07:05:04+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:05:04+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:05:04+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Japan 2018-05-03T09:38:59+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T09:38:59+00:00 4295876589 195 16157 1002485247 UWE 3010547 true false false O|!|
Japan 2018-05-03T07:08:14+00:00 4295876589 194 2719 3023331 AOP 3010542 true false true O|!|
Japan 2018-05-03T07:08:14+00:00 4295876589 195 5937 3026578 NOP 3010543 true false true O|!|
Japan 2018-05-03T07:08:14+00:00 4295876589 196 3252 3024053 ONC 3020538 true false true O|!|
Now i want to add all columns of data frame one two data frame except for the records for which three columns TimeStamp ,OrganizationID and SourceID is different .
So in this case Data frame one records will not be added to data frame two .Becase TimeStamp |OrganizationID|SourceID columns are matching in both data frame .
Only 1 row should be added which has SourceId 196 .
Does left_outer join will work in this case ? When i do that i get duplicate columns .
So in short Matching records based on three columns from Data frame 1 will not be added other than than all records will be added to to data frame
Upvotes: 0
Views: 83
Answers (1)
Binzi Cao
Reputation: 1085
You might try leftanti join and then uniondf2,
df1.join(df2, Seq("TimeStamp" ,"OrganizationID", "SourceID"), "leftanti").union(df2)
Upvotes: 1
Related Questions
- how to spark left join two datasets (special case)
- Join two dataframe using Spark Scala
- How correctly to join 2 dataframe in Apache Spark?
- Advanced join two dataframe spark scala
- Dataframe left outer join not working correctly in Spark
- Scala/Spark : How to do outer join based on common columns
- What is the right way to join these 2 Spark DataFrames?
- Join two Dataframe without a common field in Spark-scala
- Equivalent to left outer join in SPARK
- How to do OUTER JOIN in scala