Learn Python - Pandas merge two CSV files - Questions from the comments episode 2

Good by Python for Scott, hello Python for Pete (you!)
In this video, I walk you through how to import two .CSV files with pandas and merge on a column and also merge on the index and compare values for any data errors.

If you would like a video answering your question, please leave a comment below!

#Python #Pandas #Data

[mohammedafsanuddin]

keep up the great work! i think you should keep the series where you go on upwork(or similar freelance websites) and do jobs and show your work throughout! It provides real encourage for developers who want to create some work experience. Also your an excellent teacher ! I think this will be great for your channel

[SignInWithGarry]

Hi adam, the video content is very helpful and i enjoyed learning it. I am into a job where these things run at back-end (matching of data coming in different files format from different sources and then parsing and matching it) and i have a very big curiosity to learn it and hands-on these things. Your video give me a spark(fire) to start it. A BIG THANKS for delivering this content.

[Jayu]

An alternative to adding prefixes, the merge function has the parameter "suffixes". This will identify overlapping column names in the left and right side and allow you to define the suffix you wish to include. e.g. pd.merge(df1, df2, suffixes=("_df1", "_df2")).
A time saver I find myself using often

[maxquin]

Same problem as above with different number of rows. e. g one file has 70 rows with the other file has 120 rows same number of columns in both files.

[vijaybabaria]

Thanks for sharing these tips, is it possible to compare all columns to display only data that has mismatches without hard coding column names in the compare function? thanks a lot

[harishkonakandla]

Sir where can I find this code? Have you created any separate link for uploading code?

[vinothkumar]

Hello, I have a two datasets with each dataset has a different column names. How can I merge those?

[prasannaprakash]

Hi, Thanks for this great video. I have few questions on how to achieve the following.
1. For compare code, instead of I compare only one field with other field of csv files, is there a way that I can compare all the fields running in a array. If so how can I do it.
2. This code has a limitation that columns of both csv files should be same. I am getting exception in case if I add more columns to any one of the file. How can I achieve comparison by ignoring headers/columns? Can you please explain???

3. How can I compare if values in the fields are integers as == does not work when values in field are integers.
Ex: df(price_field1) == df(price_field2)

Price_field1 = 4.8
Price_field2=5.3

I have this questions, can u please explain...

[pramodhsuryajr]

Hi @Make Data Useful

I have an Issue while comparing two CSV Files below is the case.

1. The Structure are same for the both CSV
2. Different number of Records in both CSV ( ex CSV1- 50 Records, CSV2- 40 Recods)
3. I dont have a single Key column (Its a Composite key )

Please provide me a solution I'm new to Python.

I Tried the below way but its working if the CSV has same number of rows but its not working if it has different number of rows:

I'm storing the data is CSV1 and CSV2 in a data frame df1 and df2 correspondingly andthe converting then to objects so that it can be easily compared.

Source_file = str('a.csv')
Target_file = str('b.csv')


Source = pd.read_csv(Source_file)
df1 = pd.DataFrame(Source)

df1 = df1.astype(str)
print(df1)
#df1.dtypes
Target = pd.read_csv(Target_file)
df2 = pd.DataFrame(Target)
df2 = df2.astype(str)
print(df2)

Getting the list of headers in df1

header_list = df1.columns.tolist()

for x in range(len(header_list)) :


df3[header_list[x]] = np.where(df1[header_list[x]] == df2[header_list[x]], 'True', 'False')

print (df3)

df3.to_csv('Output', index=False)



please helpout in this scenario

[videofyph]

How can i combine 2 csv files with different columns bro?

[erykbazela]

Very nice video :) I am struggling with something rn. I have two input files with different data, indexes, column names etc. I extracted them from original tables(they were split, partially uppercase) with original position in column(indices). I have to check if the names are the same. If they are I have to save their index in original file row to the opposite so eg. csv1: 0 Bob Marley, [2, 5, 7] ; csv2: 0 Elon Musk[4, 11]. Pls tell me if it's something difficult and worth showing or maybe I'm just inexperienced... Thank you.

[reinekeerthi]

hii, i have a doubt while comparing one column with other column in the data frame, but the column name is different, for eg: 1st column name is "Column1.subLot", the other column name is "subLot". both the columns are in different dataframe. the dataset one in excel other in json file

can you please a video for my question please.

[vinayakchikkorde]

I have the source file and target file. so in that, I have to compare 140 columns and show the result if it matches or not. for example, there is a column as Country1 in source and in target as Country2. to compare that i will use True else return false. to compare 140+ columns it will take time to compare 140 columns. and in both of the file columns are not in ordered. so how can I solve this?

[sriharshadevraj]

Thank you Adam. I am learning python and this is really helpful. In your compare function you pass in the account_type as the column to be compared, how can we modify this to compare all the columns in the file? In other words, I need to compare and list the differences for each column.
Example:Compare Column Account_type
Expected Output:
Account_num Statement_1_account_type Statement_2_account_type
1 Checking Savings
Compare Column Statement_balance
Expected Output
Account_num Statement_1_Balance Statement_2_balance
3 10 15
and so on till we find differences in each column?

[filo]

Thanks for the video it help checked 18000 lines of data. Is there a way to check multiple rows at the same time.

[esargf]

Really enjoy this tutorial! This is my question:
I want to merge 2 csv files. Each file has some different columns and an id column. When merge, each row must have matching id.
The first csv file's id is in order, the second csv file's id is not in order and there's some rows with the same id.
If there's multiple rows with the same id, copy the same row from csv file 1. Then, sort merged csv file 1 & 2 by id.

csv file 1:
id gender
1 M
2 F
3 M
4 M
5 F

csv file 2:
id order_num color
1 20 blue
3 30 red
2 10 blue
3 40 pink
4 50 blue
5 60 red
5 70 red

merged csv file 1 & 2:
id gender order_num color
1 M 20 blue
2 F 10 blue
3 M 30 red
3 M 40 pink
4 M 50 blue
5 F 60 red
5 F 70 red

I hope you'll make the tutorial on this. Thanks!!

[MrTonie]

Is it possible to make a video to compare two CVS and throw in a config file to exclude certain columns? And possibly option to ignore headers.

[kingofthesummer]

What would be different if the number of columns did not match ?

[ankurmalik]

hi I want to scrap top mba colleges from (Shiksha website) but the problem is that its page changes automatically like from page 0 - 1 by itself by scrolling down
can you help me out how to scrap all pages, it has around 100 colleges but I can scrap only 40 colleges at a time

[anjanabhaskar]

How can we combine or join two datasets without any common fields?
For eg: I have one dataset bank.csv and another dataset salary.csv.
For both datasets there are no common columns. So how can we join them in Python?