CODEWITHSUNDEEP
pythonpython-3.xpandas
nomad
nomad

Reputation: 983

How to re-order and rename pandas pivot?

  1. I am trying to re-order the columns exported to my excel. It is currently ordered alphabetically. I want Sales Order Net Value, Billed Net Value and then Open Amount?
  2. Is there a way to rename the columns just for the excel?
  3. How can I rename All to Grand Total at the bottom?

enter image description here

def create_sheet():
    scriptfile = open('script.sql', 'r')
    script = scriptfile.read()
    df2 = pd.read_sql(script, my_connection)
    df2 = df2.where((pd.notnull(df2)), None)
    pivot = pd.pivot_table(df2,
                           index=['Customer_Name'],
                           values=[
                               'Billed_Net_Value',
                               'Sales_Order_Net_Value',
                               'Open_Amount'],
                           aggfunc=np.sum,
                           margins=True)

    writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')
    pivot.to_excel(writer, sheet_name='Pivot')
    workbook = writer.book
    ws = writer.sheets['Pivot']

Upvotes: 3

Views: 853

Answers (1)

piRSquared
piRSquared

Reputation: 294218

def create_sheet():
    scriptfile = open('script.sql', 'r')
    script = scriptfile.read()
    df2 = pd.read_sql(script, my_connection)
    df2 = df2.where((pd.notnull(df2)), None)
    pivot = pd.pivot_table(df2,
                           index=['Customer_Name'],
                           values=[
                               'Billed_Net_Value',
                               'Sales_Order_Net_Value',
                               'Open_Amount'],
                           aggfunc=np.sum,
                           margins=True)

    col_order = ['Sales_Order_Net_Value', 'Billed_Net_Value', 'Open_Amount']
    pivot = pivot[col_order].rename(index=dict(All='Grand Total'))

    writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')
    pivot.to_excel(writer, sheet_name='Pivot')
    workbook = writer.book
    ws = writer.sheets['Pivot']

Upvotes: 4

Related Questions