Using Pandas to filter Excel table

Question

I have imported an Excel table using Pandas. The table contains four columns representing Nodes, X, Y and Z data. I used the following script:

import pandas as pd
SolidFixity = pd.read_excel('GeomData.xlsx', sheetname = 'SurfaceFixitySolid')

What I would like to do next is filter this dataframe using the filter table to select the Node of interest. I used this command:

SolidFixity.filter(like = '797', axis = 'Nodes')

This did not work and threw the following error:

ValueError: No axis named Nodes for object type

I know there is an axis named Nodes because the following command:

In[17]SolidFixity.axes

Outputs the following:

Out[17]: 
[RangeIndex(start=0, stop=809, step=1),
 Index(['Nodes', 'X', 'Y ', 'Z'], dtype='object')]

Nodes is right there, shimmering like the sun.

What am I doing wrong here?

While the answer below is getting updated, edited and fleshed out in general: what you are doing wrong is that you are using a label filter to scan for contents. 'Nodes' is a label of axis=1, not an axis by itself. — Uvar
– Uvar, Commented Sep 22, 2017 at 13:33
That returns SolidFixity.filter(like = '797', axis = 1) Out[29]: Empty DataFrame Columns: [] Index: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, ...] [809 rows x 0 columns] which is an empty dataframe. I know node 797 is in there — user32882
– user32882, Commented Sep 22, 2017 at 13:37

Community · Accepted Answer · 2020-06-20 09:12:55Z

It seems you need boolean indexing or query with mask by contains or compare with 797 for exact match:

SolidFixity = pd.DataFrame({'Nodes':['797','sds','797 dsd','800','s','79785'],
                            'X':[5,3,6,9,2,4]})

print (SolidFixity)
     Nodes  X
0      797  5
1      sds  3
2  797 dsd  6
3      800  9
4        s  2
5    79785  4

a = SolidFixity[SolidFixity.Nodes.str.contains('797')]
print (a)
     Nodes  X
0      797  5
2  797 dsd  6
5    79785  4

b = SolidFixity[SolidFixity.Nodes == '797']
print (b)
  Nodes  X
0   797  5

b = SolidFixity.query("Nodes =='797'")
print (b)
 Nodes  X
0   797  5

filter function have possible axis only values:

axis : int or string axis name

The axis to filter on. By default this is the info axis, index for Series, columns for DataFrame

and return all columns by parameters like, regex and items:

df = pd.DataFrame({'A':list('abcdef'),
                   'B':[4,5,4,5,5,4],
                   'C797':[7,8,9,4,2,3],
                   '797':[1,3,5,7,1,0],
                   'E':[5,3,6,9,2,4],
                   'F':list('aaabbb')})

print (df)
   797  A  B  C797  E  F
0    1  a  4     7  5  a
1    3  b  5     8  3  a
2    5  c  4     9  6  a
3    7  d  5     4  9  b
4    1  e  5     2  2  b
5    0  f  4     3  4  b

a = df.filter(like = '797', axis = 1)
#same as
#a = df.filter(like = '797', axis = 'columns')
print (a)
   797  C797
0    1     7
1    3     8
2    5     9
3    7     4
4    1     2
5    0     3

c = df.filter(items = ['797'], axis = 1)
#same as
#c = df.filter(items = ['797'], axis = 'columns')
print (c)
   797
0    1
1    3
2    5
3    7
4    1
5    0

can I add boolean operators to that query statement? Say i wanted to select by X and Y value...
Sure b = SolidFixity.query("Nodes =='797' & X == 1 & Y == 5")
Nice... I opted for that since it has solved my problem. Accepting answer now

Collectives™ on Stack Overflow

Using Pandas to filter Excel table

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related