0

I am new to Python. I started working on excel. I have two sheets. 1st sheet is 'system' and second sheet is 'groups'.

system sheet:

name         pdn_group   cflash
RDAFAN00053  pdn_group1  cflash_group1
RDAFAN00054  pdn_group2  cflash_group1

or refer (image format): enter image description here

groups sheet:

group_name            pdn_group1                         pdn_group2           pdn_group3
profile        1                    2                        1                    1
apn        190007.fn/190008.fn 170007.fn/170008.fn        180007.fn           180007.fn 
description  TEST1                TEST2                      TEST3               TEST3


cflash            cflash_group1         
rising-thresold     500000          
falling-threshold   468700          
interval             3600

or refer(image format):

enter image description here

So, here I am trying to take 'name'(RDAFAN00053) from system sheet and checking its pdn_group(pdn_group1). Taking that pdn_group(pdn_group1) and matching this name in groups sheet. (same for other name)

As in groups sheet there is pdn_group1, so i want to capture its profile (1,2), its apn(19007.fn/190008.fn,170007.fn/170008.fn) ,its description (Test1, Test2) and cflash group data. (same for other group)

Output Should be like:

name = RDAFAN00053

profile = [1,2],
apn = [190007.fn/190008.fn, 170007.fn/170008.fn],
description = [test1, test2],

rising-thresold = 500000,
falling-threashold = 4687000,
interval = 3600

name = RDAFAN00054

profile = [1],
apn = [180007.fn],
description = [TEST3],

My Tried code:

import pandas as pd

#Reading excel file name
excel_file = "Copy of Book1.xlsx"

#Reading Excel sheets data and storing into data frame 
system_df = pd.read_excel(excel_file, sheet_name='system')
group_df = pd.read_excel(excel_file, sheet_name='groups')


#Looping excel data based on Name
for i in range(0, len(system_df['name'])):
    group = system_df['pdn_group'][i]
    print(group_df.columns.get_loc(group))

I am stuck here in this step.

Please need help as am finding it very complex. Or any suggestions on how to do. It would be very helpful for my college project.

NOTE: Not to take index based as the data is dynamic(pdn_group can be more than 3) so consider based on headings(row+column both).

sonali
  • 219
  • 2
  • 11
  • Why not use pandas, It will ease the working with excel. – Yash Kumar Atri Apr 07 '20 at 12:15
  • load the sheets by pandas and use iterrows() or similar functions to compare the values. – Yash Kumar Atri Apr 07 '20 at 12:19
  • @YashKumarAtri please can you give any example or any link – sonali Apr 07 '20 at 12:20
  • This will read the excel pandas.read_excel('filename', sheet_name='sheet'), You will need additional package 'xlrd' I think, Later you can convert the pandas dataframe to 2D list or json or simply use df.iterrows() and can compare values easily. https://www.dataquest.io/blog/excel-and-pandas/, https://stackoverflow.com/questions/16476924/how-to-iterate-over-rows-in-a-dataframe-in-pandas – Yash Kumar Atri Apr 07 '20 at 12:25
  • data is dynamic. Nothing is fixed in the excel @YashKumarAtri – sonali Apr 07 '20 at 13:15
  • For dynamic data, you can re-read the sheets again after a specific interval but I think the rows and column names should be identifiable. – Yash Kumar Atri Apr 08 '20 at 08:42

0 Answers0