sending an email through gmail using python

Pandas DataFrame: Join Two CSVs keeping data of all columns

In this quick tutorial, I will show how we can join two CSV files by keeping the values of every single columns intact.. For this we will require two CSV files. Below I have the contents of a CSV file:

Name, Age, Gender
The, 11, NA
Quick, 12, M
Blog, 13, F

Save the content in a file as name.csv. Next I have the contents for another CSV file:

Occupation, Job Type, Salary
Blogger, Freelance, 30000
Vlogger, Freelance, 40000
Digital Marketer, Contract, 50000

Save the contents in a file as job.csv. Now that we have two CSV files what we want to do is get the following output:

Name, Age, Gender, Occupation, Job Type, Salary
The, 11, NA, Blogger, Freelance, 30000
Quick, 12, M, Vlogger, Freelance, 40000
Blog, 13, F, Digital Marketer, Contract, 50000

We not only want to merge the columns but keep the values of the rows as is and combined in our new CSV.

HOW TO JOIN TWO CSV FILES KEEPING DATA OF ALL COLUMNS?

In this section I will be displaying the code of getting the above mentioned output. We are using pandas library as shown below:

import pandas as pd

"""Read two CSV as dataframe"""
name = pd.read_csv('name.csv')
job = pd.read_csv('job.csv')

"""Join the DataFrame"""
new_file = name.join(job, lsuffix="_left", rsuffix="_right")

"""Save the Pandas Dataframe to a CSV file"""
new_file.to_csv('new_file.csv', index=False)

With the above code you will get the following output saved in new_file.csv file:

Name, Age, Gender, Occupation, Job Type, Salary
The, 11, NA, Blogger, Freelance, 30000
Quick, 12, M, Vlogger, Freelance, 40000
Blog, 13, F, Digital Marketer, Contract, 50000

This is the exact output we expected. the operation name.join() combines two columns of a dataframe. The parameters “lsuffix”, and “rsuffix” keep the column names of both CSVs. You can also perform a left join, right join, and other operations by adding another parameter “how”. The how parameter takes how : {‘left’, ‘right’, ‘outer’, ‘inner’} options. The default one is left join.