I'm struggling with hierarchical indexes in the Python pandas
package. Specifically I don't understand how to filter and compare data in rows after it has been pivoted.
Here is the example table from the documentation:
import pandas as pd
import numpy as np
In [1027]: df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 6,
'B' : ['A', 'B', 'C'] * 8,
'C' : ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 4,
'D' : np.random.randn(24),
'E' : np.random.randn(24)})
In [1029]: pd.pivot_table(df, values='D', rows=['A', 'B'], cols=['C'])
Out[1029]:
C bar foo
A B
one A -1.154627 -0.243234
B -1.320253 -0.633158
C 1.188862 0.377300
three A -1.327977 NaN
B NaN -0.079051
C -0.832506 NaN
two A NaN -0.128534
B 0.835120 NaN
C NaN 0.838040
I would like to analyze as follows:
1) Filter this table on column attributes, for example selecting rows with negative foo
:
C bar foo
A B
one A -1.154627 -0.243234
B -1.320253 -0.633158
three B NaN -0.079051
two A NaN -0.128534
2) Compare the remaining B
series values between the distinct A
series groups? I am not sure how to access this information: {'one':['A','B'], 'two':['A'], 'three':['B']}
and determine which series B
values are unique to each key, or seen in multiple key groups, etc
Is there a way to do this directly within the pivot table structure, or do I need to convert this back in to a pandas
dataframe
?
Update: I think this code is a step in the right direction. It at least lets me access individual values within this table, but I am still hard-coding the series vales:
table = pivot_table(df, values='D', rows=['A', 'B'], cols=['C'])
table.ix['one', 'A']
Pivot table returns a DataFrame so you can simply filter by doing:
In [15]: pivoted = pivot_table(df, values='D', rows=['A', 'B'], cols=['C'])
In [16]: pivoted[pivoted.foo < 0]
Out[16]:
C bar foo
A B
one A -0.412628 -1.062175
three B NaN -0.562207
two A NaN -0.007245
You can use something like
pivoted.ix['one']
to select all A series groups
or
pivoted.ix['one', 'A']
to select distinct A and B series groups
Just to add info at the previous answer. When you try to use pivoted.ix['one']
in python3 you get the following message:
/usr/lib/python3.7/site-packages/ipykernel_launcher.py:7: DeprecationWarning: .ix is deprecated. Please use .loc for label based indexing or .iloc for positional indexing
See the documentation here: http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated import sys
So, in this version of python, use the following code instead:
pivoted.loc['one']
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With