How to insert Dashed Lines in between Rows of a tabulate output

[Mudassir1987]

Hi Im trying to format my report in python as per SAS proc report output. the expected output is shown below
Any idea how can i insert 1.dashed lines between rows. and 2. double dash in last two total rows?

my code below

import pandas as pd
from tabulate import tabulate

# Sample data (you should replace this with your actual data)
data = pd.read_csv(r"C:\\Users\\M68\\Downloads\\RV1.csv",delimiter="|")

# Create a DataFrame from the sample data
input_df = pd.DataFrame(data)

# Renaming the columns and uppercase
input_df.rename(columns={'_type_':'type'},inplace=True)
input_df.columns=input_df.columns.str.upper()

# Calculate the sums for each variable by 'TYPE' and 'SHPDT'
output_df = input_df.groupby(['TYPE', 'SHPDT']).agg({
    'REVENUE': 'sum',
    'REVPRTY': 'sum',
    'REVECON': 'sum',
    'C': 'sum',
    'TP': 'sum',
    'TE': 'sum'
}).reset_index()


# Rename the columns for the output DataFrame
output_df.rename(columns={
    'SHPDT' : 'SHIPPED DATE',
    'REVENUE': 'REVENUE\nALL SERVICES',
    'REVPRTY': 'REVENUE\nPRIORITY',
    'REVECON': 'REVENUE\nECONOMY',
    'C': 'PROS\nALL\nSERVICES',
    'TP': 'PROS\nPRIORITY',
    'TE': 'PROS\nECONOMY'
}, inplace=True)

# Convert the 'SHPDT' column to a date format
output_df['SHIPPED DATE'] = pd.to_datetime(output_df["SHIPPED DATE"]).dt.strftime('%d%b%y').str.upper()

# Sort the DataFrame by 'TYPE' and 'SHPDT'
output_df.sort_values(by=['TYPE', 'SHIPPED DATE'], inplace=True)

# Display the output DataFrame
print(output_df)

# Generate the tabular report
table = tabulate(output_df, headers='keys', tablefmt='grid')

# Print the tabular report
print(table)

# Generate the summary statistics (you can customize this part based on your needs)
summary_stats = output_df.groupby('TYPE').agg({
    'REVENUE\nALL SERVICES': 'sum',
    'REVENUE\nPRIORITY': 'sum',
    'REVENUE\nECONOMY': 'sum',
    'PROS\nALL\nSERVICES': 'sum',
    'PROS\nPRIORITY': 'sum',
    'PROS\nECONOMY': 'sum'
}).reset_index()

summary_stats1 = output_df.groupby('TYPE').agg({
    'REVENUE\nALL SERVICES': 'sum',
    'REVENUE\nPRIORITY': 'sum',
    'REVENUE\nECONOMY': 'sum',
    'PROS\nALL\nSERVICES': 'sum',
    'PROS\nPRIORITY': 'sum',
    'PROS\nECONOMY': 'sum'
}).reset_index()

# Display the summary statistics
print(summary_stats)

summary_stats['SHIPPED DATE'] = ''
summary_stats1['SHIPPED DATE'] = ''

report = pd.concat([output_df,summary_stats],keys=['output_df','summary_stats'])

ids = report['TYPE']

report['flag'] = ((ids!=ids.shift(1)) | (ids!=ids.shift(-1))).astype(int)

report.loc[report.flag == 0, 'TYPE'] = " "

report1 = pd.concat([report.reset_index(drop=True),summary_stats1])

# Keeping TYPE in last row as blank
report1.loc[report1.flag.isnull(), 'TYPE'] = " "

report1 = report1.drop(columns=['flag'])

# Addning comma between numeric values
report1['REVENUE\nALL SERVICES'] = report1['REVENUE\nALL SERVICES'].map('{:,.2f}'.format)
report1['REVENUE\nPRIORITY'] = report1['REVENUE\nPRIORITY'].map('{:,.2f}'.format)
report1['REVENUE\nECONOMY'] = report1['REVENUE\nECONOMY'].map('{:,.2f}'.format)

   

deanhystad write Sep-27-2023, 04:19 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.