Create a pivot table as it in excel

Question

This is the piece of code i tried so far,it is creating a pivot table but not as same we see manually created pivot table is there is any other way to create it. I want to create a pivot table as it is in excel but it is just showing the numbers is there anyother way to create if less please help me ,yesterday also i asked the same question but without having any try. this is the input sample i use

df=df = pd.DataFrame({'Index': ["re","re","re","re"], 
                   'De': ['c', 'c','s','s'],'Hlled':['In','out','In','out'],'V':['In','out','In','out']})
import openpyxl as xl
    import os
    import pandas as pd
    import glob
    import csv
    from openpyxl import load_workbook
    import fnmatch
    from openpyxl.utils.formulas import FORMULAE
    import datetime
    import sys
    import pandas as pd
    import numpy as np



    for root, dirs, files in os.walk(".", topdown=False):
        for filename in files:
            if filename.endswith('.xlsx') and not filename.startswith(('index', 'M')):
                book= load_workbook(filename,data_only=True)
                Read_excel = pd.ExcelFile(filename)

                table1=pd.pivot_table(df[(df["In"]=="In ")], values='index', index=['De'],
                        columns=['V'],aggfunc='count', fill_value=0,margins=True,margins_name = 'Sub-total')

                table2=pd.pivot_table(df[(df["In"]=="In")], values='index', index=['De'],columns=['Hlled'],aggfunc=len, fill_value=0,margins=True,margins_name = 'Sub-total')
                writer = pd.ExcelWriter(filename, engine='openpyxl')
                writer.book = book
                writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
                table1.to_excel(writer, 'po_r',startcol=1,startrow=7)
                table2.to_excel(writer, 'po_r',startcol=6,startrow=7)

                workheading = writer.sheets['epo_r']
                workheading.cell(row=5, column=2).value = 'As'
                workheading.cell(row=6, column=2).value = 'Iv'
                workheading.cell(row=5, column=7).value = 'Aes'
                workheading.cell(row=6, column=7).value = 'IPS'
                ##################epo calculation ########################
                workheading.cell(row=1, column=1).value = 'Ecore'
                workheading.cell(row=1, column=2).value ='=(0.5*B2)+(0.5*B3)'
                workheading.cell(row=1, column=2).number_format = '00.00%'

                workheading.cell(row=2, column=1).value = 'Are'
                workheading.cell(row=2, column=2).value ='=C11/E11'
                workheading.cell(row=2, column=2).number_format = '00.00%'

                workheading.cell(row=3, column=1).value = 'Hi'
                workheading.cell(row=3, column=2).value ='=H11/J11'
                workheading.cell(row=3,column=2).number_format = '00.00%'




                #print(worksheet)
                writer.save()
                print("done")

Can you please show sample of your input table and you want the output to look like. You must make your question in a way that can let someone reproduce your problem on their local machine. — Ukrainian-serge, Feb 26 '20 at 07:20
let me share the input sample,here i am counting index on the basis of de — user12659741, Feb 26 '20 at 07:32
You have to share it in a way that lets me copy and paste it. You have posted an image. Here's tips on making a REPRODUCIBLE bit of code we can help you figure out https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples. You need to type out what the table input is, and type out what you want table to end up looking like. — Ukrainian-serge, Feb 26 '20 at 07:36
I cant help you because you DO NOT have a sample data frame for me to copy/paste and work with. You also don't have a data frame sample of what you would like data frame to look like. Did you even look at [this](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) like I sent? Read it and recreate the way the problems are asked in those formats in the link. — Ukrainian-serge, Feb 26 '20 at 17:16
Did you read [this](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) link? I can help you unless you read this first then edit your question. — Ukrainian-serge, Feb 28 '20 at 04:09

Create a pivot table as it in excel

0 Answers0