Join instances

[reza1615]

It would be helpful to have Join two or multiple instances. My suggestion:

• The join window can be similar to description window
• when user select a column to join shows max, min, number of empty or nan, number of unique and duplicate values, datatype of that column
• before applying join based on joining type, show number of expected rows in the merged df

For example there is df1 and df2
df1.shape == (100,3)
df2.shape== (30,5)
df1.A <---- join to ---> df2.B ==> results as df3
before apply join shows

• for inner join: df3.shape => (23,8)
• for left join :df3.shape => (120,8)
• for outer join: df3.shape => (60,8)
• number of duplicates in df1.A
• number of duplicates in df2.B
• For left join preduced # null in df3

Joining options:

• inner
• left
• right
• outer
• append
  ** based on index
  ** based on left and right columns
  ** based on fuzzy match for example match: foo < --> Foo or boo or FOO or foo.

[reza1615]

Some icons







from

https://www.reddit.com/r/SQL/comments/aysflk/sql_join_chart_custom_poster_size/

[reza1615]

for fuzzy join
https://stackoverflow.com/a/56315491/5833945

[reza1615]

To Show df3.shape before join

• Inner Join

rows = sum(df1.A==df2.B)
columns = df1.shape[1]+df2.shape[1]-1

• left Join

# If there is some duplications in the df2. we will have more rows in df3 than df1
dups_values_df2_B = df2.pivot_table(index=['B'], aggfunc='size')
Unique_B_in_A= sum(x in list(dict(dups_values_df2_B)) for x in df1.A)
Total_duplicate_B=sum(list(dups_values_df2_B ))
Add_to_rows=Total_duplicate_B-Unique_B_in_A
rows = df1.shape[0]+Add_to_rows

• Outer Join

rows = df1.shape[0]+df2.shape[0]
columns = df1.shape[1]+df2.shape[1]-1

[reza1615]

For Multiple column Join we can make a new column with concat of all the columns and just join based on the new column

[aschonfeld]

added in v1.35.0 See demo here