triplebig triplebig - 1 year ago 156
Python Question

pandas select subset of pivot_table

There are a few questions here on this topic, but none seem to be helpful in my case. Here's a dumbed down version of what I want:

This is the csv file of interest:

I'm creating the pivot table as:

piv = pd.read_csv("test.csv",delimiter = "\s+").pivot_table('z','x','y')

And this returns

y 0.0 1.0 1.3 2.0
0.0 1.0 5.0 NaN 4.0
1.0 3.0 4.0 NaN 6.0
1.5 NaN NaN 7.0 NaN
2.0 3.0 5.0 NaN 7.0

I would like to find a slice of this array as a pivot_table, such as:

y 1.3 2.0
0.0 NaN 4.0
1.0 NaN 6.0

Based on the x and y values. I want to include the NaN's as well, to do processing on them later. Help much appreciated.

EDIT: updating the question to be more specific.

I'm looking to extract a pivot table that has values denoted by the column 'z' and indexed by 'x' and 'y', with the condition that:

  • All x values between arbitrary xmin and xmax

  • All y values between arbitrary ymin and ymax

From piv, as defined above, I want to do something like:

piv.loc[(piv.y <= 2.0) &
(piv.y >= 1.3) &
(piv.x >= 0.0) &
(piv.x <= 1.2)]

And this would yield me the example answer, above.
Also, in the actual dataset, which I did not post here, there are many more columns. 'x', 'y' and 'z' are just some of them.

Answer Source

When I copied dataframe, the columns were strings and rows were floats.
To get the columns as float

df.columns = df.columns.astype(float)

Now you can pd.IndexSlice

df.loc[pd.IndexSlice[0:1], pd.IndexSlice[1.3:2]]

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download