在Python 3.0中使用 pandas 在循环中映射Excel工作表时,如何reset_index? [英] How to reset_index when mapping excel sheet in a loop using pandas in Python 3.0?
本文介绍了在Python 3.0中使用 pandas 在循环中映射Excel工作表时,如何reset_index?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在使用pandas和reset_index函数时,我遇到了一个小问题. 以下是我要映射的Excel和3张纸.
I am facing a little issue while working with pandas and reset_index function. Below is the excel and 3 sheet I am trying to map.
请在下面找到代码:
filename='C:\\HPTiB\\HPTib_Test_Cases\\template_276.xlsx'
data=pd.read_excel(filename,sheet_name=['INFORMATION SOURCE','INFORMATION RECEIVER','SERVICE PROVIDER'],dtype=str)
sequence=0
segments_276=[]
N_info_src=len(data['INFORMATION SOURCE'])
N_info_recv=len(data['INFORMATION RECEIVER'])
N_svc_prv=len(data['SERVICE PROVIDER'])
N_sub=len(data['SUBSCRIBER'])
for i in range(N_info_src):
print("Value of i",i)
#Currently iterating over the info source loop
sequence=sequence+1
source_parent=sequence
#Write the HL segment
segments_276.append('HL*'+str(sequence)+'**20*1')
#Write all the loop segments for this row
# loop_segments=Parser.build_loop('2100A',i,data['INFORMATION SOURCE'])
# segments_276=segments_276+loop_segments
#Get the KEY for this info source and related keys in the next table
SOURCE_KEY=data['INFORMATION SOURCE'].loc[i,'SOURCE KEY']
subset_info_recv=data['INFORMATION RECEIVER'][data['INFORMATION RECEIVER']['SOURCE KEY']==SOURCE_KEY]
#Reset index to avoid key errors
subset_info_recv.reset_index(drop=True,inplace=True)
N_info_recv=len(subset_info_recv)
for j in range(N_info_recv):
print("value of j \n {} and value of subset_info_recv \n {}".format(j,subset_info_recv))
#Currently itesrating over the info recv loop
sequence=sequence+1
recv_parent=sequence
#Write the HL segment
# segments_276.append('HL*'+str(sequence)+'*'+str(source_parent)+'*21*1')
#Write all the loop segments for this row
# loop_segments=Parser.build_loop('2100B',j,subset_info_recv)
# segments_276=segments_276+loop_segment
# Get the KEY for this info receiver and related keys in the next table
RECEIVER_KEY = data['INFORMATION RECEIVER'].loc[j, 'RECEIVER KEY']
subset_info_provider = data['SERVICE PROVIDER'][data['SERVICE PROVIDER']['RECEIVER KEY'] == RECEIVER_KEY]
# Reset index to avoid key errors
subset_info_provider.reset_index(drop=True, inplace=True)
N_svc_prv = len(subset_info_provider)
print("Lengh of provider sheet", N_svc_prv)
for k in range(N_svc_prv):
print("value of k \n {} and value of subset_info_provider \n {}".format(k,subset_info_provider))
# Currently iterating over the info Provider loop
sequence = sequence + 1
provider_parent = sequence
# Write the HL segment
segments_276.append('HL*' + str(sequence) + '*' + str(recv_parent) + '*19*1')
#Write all the loop segments for this row
#print("Value of k {} and \n subset_info_provider \n {} ".format(k,subset_info_provider))
# loop_segments=Parser.build_loop('2100C',k,subset_info_provider)
# segments_276=segments_276+loop_segments
#Print the result
#for segment in segments_276:
# print(segment)
输出:
Value of i 0
value of j 0
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 1 1 PERSON CEO A222222221
1 1 2 PERSON CO-FOUNDER A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
value of k 1
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
value of j 1
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 1 1 PERSON CEO A222222221
1 1 2 PERSON CO-FOUNDER A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 2 3 Microsoft NPI 123453756
Value of i 1
value of j 0
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 2 3 PERSON CFO A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
value of k 1
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
期望的输出:
Value of i 0
value of j 0
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 1 1 PERSON CEO A222222221
1 1 2 PERSON CO-FOUNDER A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
value of k 1
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 1 1 Tesla Provider Number 123456789
1 1 2 Apple TIN 123453234
value of j 1
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 1 1 PERSON CEO A222222221
1 1 2 PERSON CO-FOUNDER A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 2 3 Microsoft NPI 123453756
Value of i 1
value of j 0
and value of subset_info_recv
SOURCE KEY RECEIVER KEY RECIEVER KEY TYPE RECIEVER NAME RECIEVER CODE
0 2 3 PERSON CFO A222222221
value of k 0
and value of subset_info_provider
RECEIVER KEY PROVIDER KEY PROVIDER NAME PROVIDER ID TYPE PROVIDER ID
0 3 4 Google Provider Number 675453756
因此,如果您在输出"中看到服务提供商"表,则它没有打印RECEIVER KEY 3的值, 相反,它正在重置并打印前两个值.
SO , if you see in OUTPUT for Service provider sheet , it is not printing the value of RECEIVER KEY 3 , instead it is getting reset and printing first two values.
能否请您指出问题,我是否循环不正确?
Could you please help me point out the issue , am I not looping it correctly?
谢谢!!
推荐答案
更改行
RECEIVER_KEY = data['INFORMATION RECEIVER'].loc[j, 'RECEIVER KEY']
到
RECEIVER_KEY = subset_info_recv.loc[j, 'RECEIVER KEY']
因为j
的for
循环位于range(len(subset_info_recv))
中.
这篇关于在Python 3.0中使用 pandas 在循环中映射Excel工作表时,如何reset_index?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文