iterate through rows and columns in excel using pandas-Python 3

Question

I have an excel spreadsheet that I read with this code:

df=pd.ExcelFile('/Users/xxx/Documents/Python/table.xlsx')
ccg=df.parse("CCG")

With the sheet that I want inside the spreadsheet being CCG

The sheet looks like this:

  col1  col2   col3    
x    a     1      2     
x    b     3      4    
x    c     5      6     
x    d     7      8
x    a     9      10
x    b     11     12
x    c     13     14
y    a     15     16
y    b     17     18
y    c     19     20
y    d     21     22
y    a     23     24

How would I write code that gets values of col 2 and col3 for rows that contain both a and x. So the proposed output for this table would be: col1=[1,9], col2=[2,10]

MaxU - stand with Ukraine · Accepted Answer · 2017-01-18 11:45:52Z

2

Try this:

df = pd.read_excel('/Users/xxx/Documents/Python/table.xlsx', 'CCG', index_col=0, usecols=['col1','col2']) \
       .query("index == 'x' and col1 == 'a'")

Demo:

Excel file:

In [243]: fn = r'C:\Temp\.data\41718085.xlsx'

In [244]: pd.read_excel(fn, 'CCG', index_col=0, usecols=['col1','col2']) \
            .query("index == 'x' and col1 == 'a'")
Out[244]:
  col1  col2
x    a     1
x    a     9

edited Jan 18, 2017 at 11:45

answered Jan 18, 2017 at 11:28

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

reuben Over a year ago

its coming up with a syntax error on the new= line

IanS Over a year ago

You forgot to chain .parse("CCG").

reuben Over a year ago

@MaxU, SyntaxError: EOF while scanning triple-quoted string literal

Clusks · Accepted Answer · 2017-01-18 11:45:04Z

1

You can do:

df = pd.read_excel('/Users/xxx/Documents/Python/table.xlsx'),sheetname='CCG', index_col=0)
filter = df[(df.index == 'x') & (df.col1 == 'a')]

Then from here, you can return all the values as a numpy array with:

filter['col2']
filter['col3']

answered Jan 18, 2017 at 11:45

Clusks

5205 silver badges15 bronze badges

Comments

reuben · Accepted Answer · 2017-01-18 13:31:24Z

0

Managed to create a count that iterates until it finds a adds +1 to the count and only appends to the list index if it is between the ranges that x is in, once i have the indices i search through col 2 and 3 and pull the values out for the indices

answered Jan 18, 2017 at 13:31

reuben

1011 gold badge3 silver badges12 bronze badges

Collectives™ on Stack Overflow

iterate through rows and columns in excel using pandas-Python 3

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related