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)