Finding Matching Values Between Multiple Dataframes

0binary Name1 0
Viewed 46

So i wanna create a dataframe based on the matching values between 11 others with the reference dataframe. And the 11 dataframes has a column named 'Serial Number' which i wanna compare with the Serial ID column on the reference dataframe.

Due to the fact that i didn't thought of way to loop all 12 excel files into separated variables. I just writed the below code.

Imported all libraries needed

import pandas as pd

from matplotlib import pyplot as plt

from google.colab import drive

drive.mount('/content/drive')

Specified the path in which all the excel files are stored

directory = '/content/drive/MyDrive/Colab Notebooks/Ursa project'

And did filename = pd.read_excel('path') for all 12 files

Now that i created all dataframe objects i need to find which rows of these 11 dataframes has matching values between the Serial Number column with the Serial ID column in the reference dataframe.

My failed attempt was to do the following for each dataframe:

for i in reference_df['Serial ID']:

  df_matches = df1[df1['Serial number'] == i]

df_matches

P.S.: i'm using Colab

One of the excel files i'm using is too long so here's a drive link to both Serial ID and Serial Number: https://drive.google.com/drive/folders/1BBTnQY1Be6vHtrrPQyXE18eVPkGU_X5N?usp=sharing

dataframe pandas python
1 Answers

If what you are trying to match is a part of a string, you can use df.Series.str.contains.

df_matches = []
for i in reference_df['Serial ID']:
    df_matches.append(df1[df1['Serial number'].str.contains(i)])
Kevin Choon Liang Yew0
Related