CODEWITHSUNDEEP
pythonexcelpandasopenpyxl
ugabuga77
ugabuga77

Reputation: 771

How to access one row out of many founded

I had a problem wit openpyxl but users Eric and H. Trizi solved it. Unfortunately there is another problem. They gave me solution how to select rows that contain certain product name (ABC in this case) but I have no idea how to access search results separately.

Excel report

This is Eric's code:

from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb.active

for row in ws.rows:
   if row[4].value == "ABC":
       for cell in row:
           print(cell.value, end=" ")
       print()

And this is H. Trizi's:

    import pandas as pd

    df = pd.read_excel("path_to_excel_file")
    df_abc = df[df["Products"] == "ABC"] # this will only contain 2,4,6 rows

They both work great when it comes to selecting ALL rows that contain "ABC" product but is there a way to access every single row (and then every cell in it) that contains ABC product name separately?

What I want to achieve ist access every row that has product name ABC separately and then save its cells to variables.

So step by step: 1.Find all rows with ABC product is in rows (2,4,6 in this case)- solved by Eric and H. Trizi 2.Get row 2 and break it down to cells that will be assigned to variables (name, surname, order number etc.) 3. Do the same for row 4 4. Do the same for row 6 5.etc. PS. Data is available in excel report

Upvotes: 2

Views: 112

Answers (1)

jezrael
jezrael

Reputation: 863301

You can use very slow iterrows:

for i, row in df_abc.iterrows():
    print (i)
    name = row['name']
    surname = row['surname']

Or a bit faster itertuples: 

for i in df_abc.itertuples():
    name = i.name
    surname = i.surname

Another solution is convert filtered DataFrame to list of dictionaries by to_dict, it should be fastest:

L = df_abc.to_dict(orient='r')

for x in L:
    print (x)
    name = x['name']
    surname = x['surname']

Sample:

df = pd.DataFrame({'surname':list('abcdef'),
                   'Products':['ABC','ABC','d','d','ABC','f'],
                   'val':[7,8,9,4,2,3],
                   'name':list('DFERTH')})

print (df)
  surname Products  val name
0       a      ABC    7    D
1       b      ABC    8    F
2       c        d    9    E
3       d        d    4    R
4       e      ABC    2    T
5       f        f    3    H

df_abc = df[df["Products"] == "ABC"]
print (df_abc)
  surname Products  val name
0       a      ABC    7    D
1       b      ABC    8    F
4       e      ABC    2    T

L = df_abc.to_dict(orient='r')
print (L)
[{'surname': 'a', 'Products': 'ABC', 'val': 7, 'name': 'D'},
 {'surname': 'b', 'Products': 'ABC', 'val': 8, 'name': 'F'},
 {'surname': 'e', 'Products': 'ABC', 'val': 2, 'name': 'T'}]

for x in L:
    print (x)
    print (x['name'])
    print (x['surname'])

D
a
{'surname': 'b', 'Products': 'ABC', 'val': 8, 'name': 'F'}
F
b
{'surname': 'e', 'Products': 'ABC', 'val': 2, 'name': 'T'}
T
e

EDIT:

For select each value to separately variable - it is not necessary, because is possible use list of dictionary, select each dictionary by indexing and then select each value of dict:

#selected first dictionary of list by indexing ([0]) and then dictionary by key (name)
print (L[0]['name'])
D
print (L[0]['surname'])
a

print (L[1]['name'])
F
print (L[1]['surname'])
b

print (L[2]['name'])
T
print (L[2]['surname'])
e

Upvotes: 2

Related Questions