Home > Enterprise >  how to use multiindex columns in new Pandas column formula
how to use multiindex columns in new Pandas column formula

Time:09-23

Background

I have a time series dataframe consisting of windspeed values from 4 different grid points (lat/lon). Originally it had 3 index fields (time, lat, lon) but I've unstacked the dataframe, so that lat/lon are now columns.

Example Data

                         time   wind_speed_100m                      
latitude                        -23.5           -23.25          
longitude                       150.00  150.25  150.00  150.25
0         2019-01-01 10:00:00   6.7556  6.4520  6.3142  6.4910
1         2019-01-01 11:00:00   6.9567  6.4570  6.1889  6.3387
2         2019-01-01 12:00:00   7.5713  6.7168  5.9827  6.1586

What I'm trying to do

I am trying to create a new column in my dataframe which uses the windspeed values from the 4 grid point locations as inputs. I want to be able to reference each of the grid points separately

Example desired outcome

Simplified example of what I am trying to do below. The below doesn't work, but sort of shows what I am trying to achieve.

df['sum'] = df[(-23.5, 150.00)].values   
            df[(-23.5, 150.25)].values   
            df[(-23.25, 150.0)].values   
            df[(-23.25, 150.25)].values

What I've tried

I've browsed all over SO and found some useful examples like This but I could only filter by one index (i.e. lat) I couldn't get it to filter by both indexes (lat & lon). Does anyone know a way I can reference a specific lat/lon column in my formula?

CodePudding user response:

Try with pd.IndexSlice

idx = pd.IndexSlice
df.loc[:,idx['wind_speed_100m', -23.5, 150.00]].values
  • Related