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

Question

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:

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

returns this:

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?

Anurag Dabas · Accepted Answer · 2021-05-15 08:32:32Z

1

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

answered May 15, 2021 at 8:32

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

6 Comments

HM14 Over a year ago

Hmm I tried the above and it is still returning empty. I would think it would return the windspeeds per time for the given location.

Anurag Dabas Over a year ago

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

HM14 Over a year ago

df=df.query('latitude == 24.549999 and longitude== -126.870003') still returns empty

Anurag Dabas Over a year ago

since it is working on my side....so let's try out with loc accessor.....try: df.loc[(24.549999,-126.870003)]

HM14 Over a year ago

This gives me a "KeyError: 24.549999"

|

HM14 · Accepted Answer · 2021-05-15 18:36:07Z

0

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

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

df.index.values

and then the following works! Thanks!

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

answered May 15, 2021 at 18:36

HM14

7091 gold badge12 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related