python - 编写一个算法,使用 Python 脚本为 Excel 工作表中的值形成一对多关系
问题描述
我有一个 Excel 有几张表,如下所示:
sheet 1 contains :
SOURCE-KEY PAYER-NAME PAYER-CODE-TYPE PAYER-CODE
1 INDUSTRY PAY 123485
2 LEADING PAY 123422
Sheet 2 contains :
SOURCE-KEY RECIEVER-KEY RECIEVER-KEY-TYPE RECIEVER-NAME RECIEVER-CODE
1 1 PERSON CEO A222222221
1 2 PERSON CO-FOUNDER A222222221
2 3 PERSON CFO A222222221
现在我想在两张表之间生成一对多算法,这意味着对于 SOURCE_KEY 的每个值打印 RECEIVER SHEET 中的所有值
方法 :
INFORMATION SOURCE KEY 1: parent
SERVICE PROVIDER KEY 1: child
SERVICE PROVIDER KEY 2: child
INFORMATION SOURCE KEY 2: parent
SERVICE PROVIDER KEY 3: child
SERVICE PROVIDER KEY 4: child
以下是编写的代码:
def loop_2100A(self,source_keys):
Information_Reciever = pd.read_excel(filename, sheet_name=1, index_col=0)
Reciever_keys=list(Information_Reciever["RECIEVER KEY"])
Source_key_in_Reciever=list(Information_Reciever.index.values)
elem =1
elems_in_both_lists = set(Source_key_in_Reciever) & set(Reciever_keys
if elem in elems_in_both_lists:
print("Value of Source key inside if ", elem)
print("Value of Reciever Key inside if", elem)
res = dict(zip(Source_key_in_Reciever, Reciever_keys))
for p in source_keys:
print("Value of P is ",p)
for x,y in res.items():
print("COMPARING of p is {} and Value of x is {} IF EQUAL GO AHEAD ".format(p,x))
if[p==x]:
print("Value of Source key passed in 2100A is", x)
print("Value of Reciever key passed to 2100B is", y)
# CALLING SOME FUNCTION TO PERFORM OPERATION ONLY WHEN P==X
else:
print("Return back to Parent tag")
return len(source_keys)
def run(self):
Read_Excel = pd.read_excel(filename, sheet_name=0,index_col=0)
source_keys = list(Read_Excel.index.values)
segs = self.loop_2100A(Parser, filename,source_keys)
输出:
*********Inside 2100A loop*********
Value of Source key inside if 1
Value of Reciever Key inside if 1
Dictionary formed is {1: 2, 2: 3}
Value of P is 1
COMPARING of p is 1 and Value of x is 1 IF EQUAL GO AHEAD
Value of Parent Source key passed in 2100A is 1
Value of Child Reciever key passed to 2100B is 2
COMPARING of p is 1 and Value of x is 2 IF EQUAL GO AHEAD
****Value of Parent Source key passed in 2100A is 2
Value of Child Reciever key passed to 2100B is 3****
Value of P is 2
COMPARING of p is 2 and Value of x is 1 IF EQUAL GO AHEAD
**Value of Parent Source key passed in 2100A is 1
Value of Child Reciever key passed to 2100B is 2**
COMPARING of p is 2 and Value of x is 2 IF EQUAL GO AHEAD
Value of Parent Source key passed in 2100A is 2
Value of Child Reciever key passed to 2100B is 3
Process finished with exit code 0
预期输出:
*********Inside 2100A loop*********
Value of Source key inside if 1
Value of Reciever Key inside if 1
Dictionary formed is {1: 2, 2: 3}
Value of P is 1
COMPARING of p is 1 and Value of x is 1 IF EQUAL GO AHEAD
Value of Parent Source key passed in 2100A is 1
Value of Child Reciever key passed to 2100B is 2
COMPARING of p is 1 and Value of x is 2 IF EQUAL GO AHEAD
NOT EQUAL Return back to Parent tag
Value of P is 2
COMPARING of p is 2 and Value of x is 1 IF EQUAL GO AHEAD
NOT EQUAL Return back to Parent tag
COMPARING of p is 2 and Value of x is 2 IF EQUAL GO AHEAD
Value of Parent Source key passed in 2100A is 2
Value of Child Reciever key passed to 2100B is 3
Process finished with exit code 0
输出中的粗体是错误的。因此,即使 P 和 X 的比较失败,它也会进入 If 条件,或者我的 be for 循环和 if 条件错误。
解决方案
推荐阅读
- r - 如何将数据集中的两个变量放在 R 中堆叠的条形图上
- excel - 已解决 - Excel 文档丢失所有数据
- python - 如何对连接到 websocket 的每个客户端使用唯一查询
- sql - BigQuery/SQL:如何连接两个表并使用列值作为列名?
- asp.net - 如何从字符串中解析 ODataQueryOptions?
- php - 如何调试泄漏参考?
- r - 当您需要按变量分组时,为什么 data.table 操作比 tidyverse 操作更快?
- javascript - Vue如何将forloop按钮表分成表中的不同行
- android - 为什么同一个本机线程似乎从不同的 Java 线程调用方法?
- typescript - 带有 Typescript 3.8.3 的 Angular 9 是默认支持 IE 中的可选链接,而无需更改 polyfill (core-js)?