Python - Read CSV files and Report creation in XLSX-CodePudding

Overview: THIS PROGRAM/FUNCTION READS ALL - INDIVIDUAL METRICS FILES AND CREATES A EXCEL REPORT WITH ALL METRICS.

import glob, os, sys
import csv          
import xlsxwriter

from pathlib import Path
import pandas as pd
from openpyxl import Workbook

#reading all csv files into dataframe
f1 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\LOC.csv")
f2 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\COMMENT.csv")
f4 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\BLANKS.csv")
f5 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\CALL.csv")
f6 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\FILEOPS.csv")
f7 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\CONDITIONS.csv")
f8 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\ARITHMETICS.csv") 
f9 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\PERFORM.csv")
f10 = pd.read_csv("C:\\Users\\Desktop\\Cobol\\Outputs\\OTHERSTMTS.csv")

# merging the files
f3 = (f1[["Program Name", 
         "LinesofCode"]].merge(f2[["Program Name", "CommentedLOC"]], on = "Program Name", how = "left")
                        .merge(f4[["Program Name", "BlankLOC"]], on = "Program Name", how = "left")
                        .merge(f5[["Program Name", "CALL Statements"]], on = "Program Name", how = "left")
                        .merge(f6[["Program Name", "FileOps Count"]], on = "Program Name", how = "left")
                        .merge(f7[["Program Name", "Conditional Statements"]], on = "Program Name", how = "left")
                        .merge(f8[["Program Name", "Arithmetic Statements"]], on = "Program Name", how = "left")
                        .merge(f9[["Program Name", "Perform Count"]], on = "Program Name", how = "left")
                        .merge(f10[["Program Name", "Other Statements"]], on = "Program Name", how = "left"))

#Output file name and location 
writer_object = pd.ExcelWriter("C:\\Users\\Desktop\\Cobol\\Outputs\\Results1.xlsx",
                               engine='xlsxwriter')
f3.to_excel(writer_object, sheet_name='Combined Metrics')   #sheet name
workbook_object = writer_object.book
worksheet_object = writer_object.sheets['Combined Metrics']

#format for header object.
header_format_object = workbook_object.add_format({
    'bold': True,
    'italic': True,
    'text_wrap': True,
    'valign': 'top',
    'font_color': 'green',
    'border': 2})

worksheet_object.set_column(1, 10, 11)      #set column width
worksheet_object.write("A1", 'S.No')        #First column S.No

# Write the column headers with the defined format.
for col_number, value in enumerate(f3.columns.values):
    worksheet_object.write(0, col_number   1, value,
                           header_format_object)

writer_object.save()

Input CSV file - for one metric LOC (LOC.csv), remaining all are having same format

Program Name LinesofCode
ABCDEFGH         191
IJKLMNOP         195

Output in Terminal (Success)

PS C:\Users\Python-1> & C:/Users/AppData/Local/Programs/Python/Python310/python.exe c:/Users/Python-1/Report_Generation_v01.py

Actual output of file in Folder:

C:\Users\Desktop\Cobol\Outputs

Name            Date Modified       Type            Size
Results1.xlsx   9/28/2022 time      XLSX File       6 KB

Actual Output in XLSX file

S.No Program Lines  Commented BlankLOC  CALL     FileOps Conditional Arithmetic Perform Other
     Name    ofCode LOC               Statements Count   Statements  Statements Count   Statements
0    ABCDEFGH   191     4       56        1        5            0         2       6        5
1    IJKLMNOP   195     12      54        0        5            1         2       6        5

Problem: Results are good, however the S.No Column in XLSX file [number of programs, starts with zero instead of 1]

S.No

0
1

CodePudding user response：

Have you tried a reindex?

Set the index before write the csv. For example:

f3.index = np.arange(1, len(f3)   1)