How do I convert a pandas pivot table to a dataframe
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22774364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I convert a pandas pivot table to a dataframe
提问by user1718097
I want to use a pivot table to summarise a dataset and then be able to access the information in the pivot table as if it were a DataFrame.
I want to use a pivot table to summarise a dataset and then be able to access the information in the pivot table as if it were a DataFrame.
Consider a hierarchical dataset with patients being treated in a hospital and hospitals situated in regions:
Consider a hierarchical dataset with patients being treated in a hospital and hospitals situated in regions:
import pandas as pd
example_data = {'patient' : ['p1','p2','p3','p4','p5','p6','p7','p8','p9','p10','p11','p12','p13','p14','p15','p16','p17','p18','p19','p20','p21','p22','p23','p24','p25','p26','p27','p28','p29','p30','p31','p32','p33','p34','p35','p36','p37','p38','p39','p40','p41','p42','p43','p44','p45','p46','p47','p48','p49','p50','p51','p52','p53','p54','p55','p56','p57','p58','p59','p60','p61','p62','p63'],
'hospital' : ['h1','h1','h1','h2','h2','h2','h2','h3','h3','h3','h3','h3','h4','h4','h4','h4','h4','h4','h5','h5','h5','h5','h5','h5','h5','h6','h6','h6','h6','h6','h6','h6','h6','h7','h7','h7','h7','h7','h7','h7','h7','h7','h8','h8','h8','h8','h8','h8','h8','h8','h8','h8','h9','h9','h9','h9','h9','h9','h9','h9','h9','h9','h9'],
'region' : ['r1','r1','r1','r1','r1','r1','r1','r1','r1','r1','r1','r1','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r2','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3','r3'] }
example_dataframe = pd.DataFrame(example_data)
print example_dataframe
This produces a simple output as follows:
This produces a simple output as follows:
hospital patient region
0 h1 p1 r1
1 h1 p2 r1
2 h1 p3 r1
3 h2 p4 r1
4 h2 p5 r1
5 h2 p6 r1
6 h2 p7 r1
7 h3 p8 r1
8 h3 p9 r1
9 h3 p10 r1
10 h3 p11 r1
11 h3 p12 r1
12 h4 p13 r2
13 h4 p14 r2
14 h4 p15 r2
15 h4 p16 r2
16 h4 p17 r2
etc.
Now I want to summarise using a pivot table, simply counting the number of patients in each hospital:
Now I want to summarise using a pivot table, simply counting the number of patients in each hospital:
example_pivot_table = pd.pivot_table(example_dataframe, values='patient', rows=['hospital','region'], aggfunc='count')
print example_pivot_table
This produces the following output:
This produces the following output:
hospital region
h1 r1 3
h2 r1 4
h3 r1 5
h4 r2 6
h5 r2 7
h6 r2 8
h7 r3 9
h8 r3 10
h9 r3 11
Name: patient, dtype: int64
As far as I understand it, this is actually a multi-indexed series.
As far as I understand it, this is actually a multi-indexed series.
How can I use this data to find out what region hospital h7 is in? If hospital, regionand the patient count data were separate columns in a DataFrame, it would be easy. But I think hospital and region are indices. I've tried lots of things but haven't been able to get it to work.
How can I use this data to find out what region hospital h7 is in? If hospital, regionand the patient count data were separate columns in a DataFrame, it would be easy. But I think hospital and region are indices. I've tried lots of things but haven't been able to get it to work.
回答by waitingkuo
You can use get_level_valuesto get the hospital column. You can pass either the number of level or the name of level, i.e. 0or hospital
You can use get_level_valuesto get the hospital column. You can pass either the number of level or the name of level, i.e. 0or hospital
Then you can get what you want by:
Then you can get what you want by:
In [38]: example_pivot_table[ example_pivot_table.index.get_level_values('hospital') == 'h7' ]
Out[38]:
hospital region
h7 r3 9
Name: patient, dtype: int64
Update
Update
To get the regions, you can do
To get the regions, you can do
example_pivot_table[ example_pivot_table.index.get_level_values('hospital') == 'h7' ]['regions']
回答by LondonRob
First up, this isn't a pivot table job, it's a groupbyjob.
First up, this isn't a pivot table job, it's a groupbyjob.
Pivot tables are for re-shaping your data when you haven't set an index (see this doc article), stackand unstackare for reshaping when you haveset an index, and groupbyis for aggregating (which is what this is) and split-apply-combineoperations.
Pivot tables are for re-shaping your data when you haven't set an index (see this doc article), stackand unstackare for reshaping when you haveset an index, and groupbyis for aggregating (which is what this is) and split-apply-combineoperations.
Here's how to get your patient counts using groupby:
Here's how to get your patient counts using groupby:
>>> patient_count = df.groupby(['hospital', 'region']).count()
>>> print patient_count
patient
hospital region
h1 r1 3
h2 r1 4
h3 r1 5
h4 r2 6
h5 r2 7
h6 r2 8
h7 r3 9
h8 r3 10
h9 r3 11
To select certain rows in a multi-index, I usually use ixas follows:
To select certain rows in a multi-index, I usually use ixas follows:
>>> h7 = patient_count.ix['h7']
>>> print h7
patient
region
r3 9
And now you can use get_level_values
And now you can use get_level_values
>>> h7.index.values[0]
'r3'
Or, if you don't want the multi-indexed version (and, for your purposes you probably don't) you can do:
Or, if you don't want the multi-indexed version (and, for your purposes you probably don't) you can do:
>>> patient_count = patient_count.reset_index()
Which allows you to find which region hospital h7is in as follows:
Which allows you to find which region hospital h7is in as follows:
>>> patient_count.region[patient_count.hospital == 'h7']
6 r3
Name: region, dtype: object
If you just want r3you can do:
If you just want r3you can do:
>>> patient_count.region[patient_count.hospital == 'h7'].values[0]
'r3'
Note that reset_indexdoesn't happen in-place which makes it great for chaining methods like this:
Note that reset_indexdoesn't happen in-place which makes it great for chaining methods like this:
>>> patient_count.ix['h7'].reset_index().region[0]
'r3'
回答by adithya krishnan
This will do the trick :
This will do the trick :
levels = example_pivot_table.columns.levels
labels = example_pivot_table.columns.labels
example_pivot_table.columns = levels[1][labels[1]]
example_pivot_table.reset_index(inplace=True)
example_pivot_table
So,find the levels and labels in your pivot table assign column names and reset index in it. The final result should be the result dataframe of the pivot.
So,find the levels and labels in your pivot table assign column names and reset index in it. The final result should be the result dataframe of the pivot.
