1

How create pivot table using openxlpy library which is equivalent to the following pandas pivot table function?

import pandas as pd 

import numpy as np 


# creating a dataframe 

df = pd.DataFrame({'A': ['John', 'Boby', 'Mina', 'Peter', 'Nicky'], 

      'B': ['Masters', 'Graduate', 'Graduate', 'Masters', 'Graduate'], 

      'C': [27, 23, 21, 23, 24]}) 

table = pd.pivot_table(df, values ='A', index =['B', 'C'], 
                         columns =['B'], aggfunc = np.sum)
sahil makandar
  • 131
  • 12
  • https://openpyxl.readthedocs.io/en/stable/pivot.html – APhillips Feb 11 '20 at 15:29
  • I already seen this. They are not mentioned equivalent function of this. – sahil makandar Feb 11 '20 at 16:41
  • Have you attempted any code in openpyxl? Please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) for us to aid you. Please review [how to ask](https://stackoverflow.com/help/how-to-ask) for a better response. – APhillips Feb 11 '20 at 16:52
  • I wrote code into pandas I want to do same thing by using openpyxl. In documentation they not mentioned clearly and the pivote example is also not very usefilul which they given in documentation of openpyxl. The pandas code I am updated. Thank you for responce. – sahil makandar Feb 11 '20 at 18:02

1 Answers1

1

Does this reach your desired result?

import pandas as pd 
import numpy as np 

# creating a dataframe 

df = pd.DataFrame({'A': ['John', 'Boby', 'Mina', 'Peter', 'Nicky'], 

      'B': ['Masters', 'Graduate', 'Graduate', 'Masters', 'Graduate'], 

      'C': [27, 23, 21, 23, 24]}) 

table = pd.pivot_table(df, values ='A', index =['B', 'C'], 
                         columns =['B'], aggfunc = np.sum)

table.to_excel("filename.xlsx")

Outputs

Excel Output

APhillips
  • 1,175
  • 9
  • 17