+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: matching a row from 2 seperate dataframes and minimg a column (/thread-29466.html)
matching a row from 2 seperate dataframes and minimg a column - randor - Sep-03-2020
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