triplebig - 11 months ago 84

Python Question

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: http://pastebin.com/rP7tPDse

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`

x

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`

x

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.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]]
```