CODEWITHSUNDEEP
pythonexcelpandas
user32882
user32882

Reputation: 5877

Using Pandas to filter Excel table

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?

Upvotes: 1

Views: 593

Answers (1)

jezrael
jezrael

Reputation: 862396

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

Upvotes: 2

Related Questions