Python Forum
matching a row from 2 seperate dataframes and minimg a column
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
matching a row from 2 seperate dataframes and minimg a column
#1
Hello, ok, I have 2 dataframes, dataframe client is a master dataframe and dataframe aca is a partial list.
I need to match each item in DF aca to DF client then get the corresponding phone number thats on DF client and make it a 3rd DF so I can print it out. But I am not having much luck:

import os
import pandas as pd
from pandas.core.groupby.groupby import DataError
from xlrd import XLRDError
import xlrd
import numpy as np


full_data = "C:/Users/User/Downloads/ACA(4).xls"
full_data_2 = "C:/Users/User/Downloads/BCB(3).xls"

def grab_Client():
    try:
        wb = xlrd.open_workbook(full_data, logfile=open(os.devnull, 'w'))
        db = pd.read_excel(wb, engine='xlrd')

        db.columns = ["a",
                      "efin",
                      "id",
                      "lname",
                      "fname",
                      "f",
                      "g",
                      "h",
                      "i",
                      "phone1",
                      "phone2",
                      "l",
                      "m",
                      "n",
                      "o",
                      "p",
                      "q",
                      "r",
                      "s",
                      "t",
                      "u",
                      "v"]

        db = db.drop([0, 1, 2, 3, 4])

        return db

    except XLRDError as ex:
        print("ERROR: " + str(ex))

def grab_Extension():
    try:
        wb = xlrd.open_workbook(full_data_2, logfile=open(os.devnull, 'w'))
        db = pd.read_excel(wb, engine='xlrd')

        db.columns = ["a",
                      "efin",
                      "id",
                      "lname",
                      "fname",
                      "f",
                      "Status",
                      "h",
                      "i",
                      "j",
                      "k"]

        db = db.drop([0, 1, 2, 3, 4, 5])

        return db

    except XLRDError as ex:
        print("ERROR: " + str(ex))


client = grab_Client()
aca = grab_Extension()

aca = aca[aca.Status != 'EXTENSION']

aca['Phone1'] = aca['id'].mask(aca['id'] == client['id'], 0, client['phone1'])
aca['Phone1'] = np.where(aca['id'] == client['id'], 0, client['phone1'], aca['tin'])

aca.to_excel("output.xls", sheet_name='Sheet_name_1')
but hen I run this, i get THIS:

Error:
C:\Users\User\PycharmProjects\TW2020\venv\Scripts\python.exe C:/Users/User/PycharmProjects/TW2020/Comparison.py Traceback (most recent call last): File "C:/Users/User/PycharmProjects/TW2020/Comparison.py", line 77, in <module> aca['Phone1'] = aca['id'].mask(aca['id'] == client['id'], 0, client['phone1']) File "C:\Users\User\PycharmProjects\TW2020\venv\lib\site-packages\pandas\core\ops\common.py", line 64, in new_method return method(self, other) File "C:\Users\User\PycharmProjects\TW2020\venv\lib\site-packages\pandas\core\ops\__init__.py", line 521, in wrapper raise ValueError("Can only compare identically-labeled Series objects") ValueError: Can only compare identically-labeled Series objects
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Matching multiple conditions and mapping multiple results, between two dataframes daveyg33 2 1,758 Jan-02-2021, 10:32 AM
Last Post: daveyg33
  Merging two DataFrames based on indexes from two other DataFrames lucinda_rigeitti 0 1,725 Jan-16-2020, 08:36 PM
Last Post: lucinda_rigeitti
  Sum product multiple Dataframes based on column headers. Lastwizzle 0 3,805 May-21-2019, 04:05 PM
Last Post: Lastwizzle

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020