ReadingTSVfiles.py

import pandas as pd
import os
from glob import glob

# Folder containing all TSV files
folder_path = r"G:\ARMS\MRF\ALLRadial125%\60kmph\TabFile"

# Get all .tsv files
files = sorted(glob(os.path.join(folder_path, "*.tsv")))

summary_data = []

for i, file in enumerate(files, start=1):

    try:
        # Read metadata from first 3 lines
        with open(file, 'r', encoding='utf-8', errors='ignore') as f:
            line1 = f.readline()  # Test Name
            line2 = f.readline()  # Date-Time
            line3 = f.readline()  # Data File
        
        # Extract Date-Time from line 2
        date_time_str = line2.split(":", 1)[1].strip() if ":" in line2 else ""
        
        # Read data: skip rows 0-4 (metadata) and row 6 (units), keep row 5 as header
        # Row 5 has: Lead Channel, Time, GPS1.speed, Distance_In_meters, FCL_FuelConsumption
        # Row 6 has: Unit, "s", "", "m", "l" (skip this)
        df = pd.read_csv(file, sep="\t", skiprows=[0, 1, 2, 3, 4, 6])

        # Clean column names (strip whitespace)
        df.columns = df.columns.str.strip()
        
        # Debug: print columns for first file
        if i == 1:
            print(f"Columns found: {df.columns.tolist()}")
            print(f"First data row:\n{df.iloc[0]}\n")

        # Extract last row (maxima row from statistics)
        last_row = df.iloc[-1]

        # Extract required columns
        time_max = last_row.get('Time', None)
        distance_max = last_row.get('Distance_In_meters', None)
        fuel_max = last_row.get('FCL_FuelConsumption', None)

        summary_data.append([
            i,
            file,
            date_time_str,
            time_max,
            distance_max,
            fuel_max
        ])
        
        if i % 10 == 0:
            print(f"Processed {i} files...")

    except Exception as e:
        print(f"Error in {file}: {e}")
        import traceback
        traceback.print_exc()

# Create final summary dataframe
summary_df = pd.DataFrame(summary_data, columns=[
    "SN",
    "Data_File",
    "Date_Time",
    "Max_Time",
    "Max_Distance_m",
    "Max_FCL_FuelConsumption"
])

# Save output to the same directory as this script
script_dir = os.path.dirname(os.path.abspath(__file__))
output_path = os.path.join(script_dir, "CSFC_Summary.csv")
summary_df.to_csv(output_path, index=False)

print(f"\nSummary File Created Successfully ✅")
print(f"Output: {output_path}")
print(f"Total files processed: {len(summary_df)}")
print(f"\nFirst few rows:")
print(summary_df.head())