CODEWITHSUNDEEP
pythonpandasdataframemulti-index
HM14
HM14

Reputation: 709

Python: Pandas Dataframe MultiIndex select data based on Index values gives empty result

I have a pandas dataframe that has multiple index (latitude, longitude, and time) with the data being windspeed. I want to select based on one latitude, longitude location. When I try this, it returns an empty result. What am I doing wrong here?

Here is part of my original dataframe:

enter image description here

df=df.query('latitude =='+str(24.549999)+ 'and longitude=='+str(-126.870003))
df

returns this:

enter image description here

completely empty like it couldn't find what I was looking for. What am I doing wrong here? Also is there a way to round the index values so for example latitude and longitude are two decimal places latitude=24.55 and longitude=-126.87?

Upvotes: 0

Views: 453

Answers (2)

HM14
HM14

Reputation: 709

Ah ok so after I printed the actual values of the dataframe instead of relying on what was displayed, I see that there is much higher precision:

df.index.values

enter image description here

So I decided to alter Anurag Dabas answer above to do the following:

df[['latitude']]=df[['latitude']].astype(float).applymap('{:,.2f}'.format)
df[['longitude']]=df[['longitude']].astype(float).applymap('{:,.2f}'.format)
df['time']=pd.to_datetime(df['time'])
df = df.set_index(['latitude', 'longitude','time'])
df

enter image description here

df.index.values

enter image description here

and then the following works! Thanks!

df.loc[('24.55','-126.87')]

enter image description here

Upvotes: 0

Anurag Dabas
Anurag Dabas

Reputation: 24304

Actually you are facing this problem because the column 'latitude','longitude' and 'time' are of type string so to resolve it:

df=df.reset_index()

Now use astype() method and to_datetime() method:

df[['latitude', 'longitude']]=df[['latitude', 'longitude']].astype(float)
df['time']=pd.to_datetime(df['time'])

Finally:

df = df.set_index(['latitude', 'longitude','time'])

Now If you run your code:

df=df.query('latitude =='+str(24.549999)+ 'and longitude=='+str(-126.870003)

You will get your desired output

Upvotes: 1

Related Questions