Joining data using Pandas

Merge 2 tables

$ New_dataframe = dataframe1.merge(dataframe2, on = 'same_column_name', suffixes = ('_dataframe1', '_dataframe2'))

 Merge 3 tables

$ New = df1.merge(df2, on = ['col1', 'col2'])   \

      .merge (df3, on = 'col3', suffixes = ('_df1', '_df2'))  \

      .merge(df4, on = 'col4')

 Left join

$ New_dataframe = dataframe1.merge(dataframe2, on = 'same_column_name', how = ‘left’)

Right join

$ New_dataframe = dataframe1.merge(dataframe2, on = 'same_column_name', how = ‘right’, left_on = ‘id’, right_on = ‘tv_id’)

Outer join

$ New_dataframe = dataframe1.merge(dataframe2, on = 'same_column_name', how = ‘outer’)

$ pd = pd.read_csv(“csv_file.csv”, index =[“idx1”, “idx2”])

Join table vertically 

$ pd. concat([t1,t2,t3 ], ignore_index = True)

$ pd. concat([t1,t2,t3 ], ignore_index = False, keys= [‘k1’,’k2’,’k3’]) : ignore_index = True: index 0 ~ n-1

W/diff col names

$ pd. concat([t1,t2], sort = True)

$ pd. concat([t1,t2], join = ‘inner’)

.append(): support ignore_index, sort , do not support join: always outer

Average by group

$ Avg_by_month = inv.groupby(level = 0).agg({‘total’:‘mean’})

Integrity validation:

.merge(...., validate = ‘one_to_one’)

‘One_to_many’  ‘Many_to_one’  ‘many_to_many’

.concat(verify_integrity = False)  :default is false

Merge_ordered:

 

Pd.merge_ordered(df1,df2, on = ‘sothing’, suffixes =(‘_df1’, ‘_df2’), filll_method = ‘ffill’) : ffill : forward fill

$ df.corr() : returns correlation matrix

Merge_asof()

$ pd.merge_asof(df1,df2, on = ‘date_time’, suffixes =(‘_df1’, ‘_df2’), direction= ‘forward’) 

Get closest value in the right table