如何从python pandas的Excel文档中读取数据透视表? [英] How to read pivot table from excel document in python pandas?

问题描述

我有一个Excel文档，其中包含体育"列，其中有体育名称"和体育人士"名称.如果我单击体育名称"，则体育人物名称会消失，即体育人物名称是该体育名称的孩子名称.

I have one excel document which contains sport column, in which sports name and sports persons names are available. If I clicked on sports name sports persons names are disappears i.e. sports persons names are children's of the sports name.

请查看以下数据:

如果我点击板球然后拉梅什(Resh)，suresh，mahesh的名字就会消失，即板球是拉梅什(reshesh)的父母，suresh和mahesh就像是同一足球一样，是pankaj，riyansh和suraj的父母.

If I clicked on cricket then ramesh, suresh,mahesh names are disappears i.e. cricket is the parent of ramesh, suresh and mahesh like same football is the parent of pankaj, riyansh, suraj.

我想阅读此excel文档并在python pandas Dataframe中进行转换.我尝试使用 pandasivot_table 进行阅读，但我没有获得任何成功.

I want to read this excel document and convert in the python pandas Dataframe. I tried to read it with pandas pivot_table but I'm not getting any success.

我试图阅读这张excel表并将其转换为数据框.

I tried to read this excel sheet and converted into a dataframe.

df = pd.read_excel("sports.xlsx",skiprows=7,header=0)
d = pd.pivot_table(df,index=["sports"])
print d

但是我将所有体育项目的值都放在一栏中，我想按体育名及其对应的体育人士名称来对其进行拆分.

But I'm getting all the sports values in single column I want to split it by sports name and it's corresponding sports persons name.

预期输出:

sports_name player_name   age address
cricket     ramesh        20  aaa
cricket     suresh        21  bbb
cricket     mahesh        22  ccc
football    pankaj        24  eee
football    riyansh       25  fff
football    suraj         26  ggg
basketball  rajesh        28  iii
basketball  abhijeet      29  jjj

推荐答案

pandas.pivot_table is there to support data analysis and helps you to create pivot tables similar to excel, not to read excel pivot tables.

创建电子表格样式的数据透视表作为DataFrame.数据透视表中的级别将存储在结果DataFrame的索引和列上的MultiIndex对象(分层索引)中

Create a spreadsheet-style pivot table as a DataFrame. The levels in the pivot table will be stored in MultiIndex objects (hierarchical indexes) on the index and columns of the result DataFrame

文档中的示例

>>> df
   A   B   C      D
0  foo one small  1
1  foo one large  2
2  foo one large  2
3  foo two small  3
4  foo two small  3
5  bar one large  4
6  bar one small  5
7  bar two small  6
8  bar two large  7


>>> table = pivot_table(df, values='D', index=['A', 'B'],
...                     columns=['C'], aggfunc=np.sum)
>>> table
          small  large
foo  one  1      4
     two  6      NaN
bar  one  5      4
     two  6      7

现在为了帮助您解决这个问题，我创建了一个示例数据集和一个数据透视表.

Now to help you on the problem, I created a sample data set and a pivot table.

然后将excel表读入pandas数据框.此数据框包含要使用 df.fillna( method ='ffill')

Then read the excel sheet into pandas dataframe. This dataframe contains nans to be replaced using df.fillna(method='ffill')

df = pd.read_excel(pviotfile,skiprows=12,header=0)
df=df.fillna(method='ffill')
print (df)

输出

       Sports     Name  Address  Age
0  basketball  Abhijit  129 ABC   20
1  basketball   Rajesh  128 ABC   20
2     Cricket   Mahesh  123 ABC   20
3     Cricket   Ramesh  126 ABC   20
4     Cricket   Suresh  124 ABC   20
5    Football   Riyash  125 ABC   20
6    Football    suraj  127 ABC   20

这篇关于如何从python pandas的Excel文档中读取数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！