How To Read Pivot Table From Excel Document In Python Pandas?

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

Solution 1:

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.

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

Example from Documentation

>>> df
   A   B   C      D
0  foo one small  11  foo onelarge22  foo onelarge23  foo two small  34  foo two small  35  bar onelarge46  bar one small  57  bar two small  68  bar two large7>>>table= pivot_table(df, values='D', index=['A', 'B'],
...                     columns=['C'], aggfunc=np.sum)
>>>table
          small  large
foo  one14
     two  6      NaN
bar  one54
     two  67

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

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)

output

       Sports     Name  Address  Age
0  basketball  Abhijit  129 ABC   201  basketball   Rajesh  128 ABC   202     Cricket   Mahesh  123 ABC   203     Cricket   Ramesh  126 ABC   204     Cricket   Suresh  124 ABC   205    Football   Riyash  125 ABC   206    Football    suraj  127 ABC   20