Old dogs, new tricks

Taming XPSWMM file bloat

One fun aspect of retaining old builds of XPSWMM is that it requires you to have the dongle and license file. If you need to rerun an old build, such as with new inflow values for a large model, it could be a daunting task.

The buttons in XP are unusual to say the least. The Time Series Inflow Flag must be enabled to access the time series data editor. Copy and paste doesn’t cut the mustard anymore.

If you export out a node with user inflow, you will notice two lines.

DATA TEO 0 4321 0.00 0.02 0.03 ... 72.00
DATA QCARD "1_A" 0 4321 0.00 0.00 0.02 ... 72.00
DATA QCARD "2_A" 0 4321 0.00 0.01 0.03 ... 71.50

Let’s jump into how to approach this issue.

Hydrologic modeling often involves processing large datasets from tools like HEC-HMS and converting them into formats compatible with other software, such as XPSWMM. One common task is transforming HEC-HMS output in Excel format into XPX files, which contain time series data for hydraulic models. In this post, I’ll walk through a Python solution to convert an HEC-HMS Excel file containing 5-year and 25-year flow data into two separate XPX files, each with TEO (time) and QCARD (flow) entries for multiple nodes. I’ll also cover the challenges faced and how they were resolved.

The Problem

The HEC-HMS output Excel file (25 and 5 yr flows.xlsx) contains two sheets: one for 5-year storm flows and one for 25-year storm flows. Each sheet has:

• Time Column: Column B (rows 6–4326), with 4321 time steps in datetime format (e.g., "07Jun2025 0000" to "10Jun2025 0000"), representing 0 to 4320 minutes.

• Node Names: Row 2 (Columns C–AW), listing 46 nodes (e.g., "0", "1", ..., "NODE_A”)

• Flow Data: Rows 6–4326, containing flow values in cubic feet per second (cfs).

The goal was to create two XPX files:

• output_5yr.xpx: For the 5-year flows.

• output_25yr.xpx: For the 25-year flows.

Each XPX file needed:

• A TEO card per node (DATA TEO "<node_name>" 0 4321 <time_values>), listing 4321 time steps in hours (0.00 to 72.00).

• A QCARD per node (DATA QCARD "<node_name>" 0 4321 <flow_values>), with flow values formatted to two decimal places.

Challenges Encountered

Converting the Excel data to XPX format presented several challenges:

1. Datetime Parsing: The time column was read by pandas as Timestamp objects, causing errors when the script expected strings for datetime.strptime.

2. Non-Numeric Headers: Row 5 contained 'CFS' labels, which caused ValueError: could not convert string to float: 'CFS' when processing flow values.

3. Multiple Sheets: The Excel file had separate sheets for 5-year and 25-year flows, requiring the script to process each independently.

4. Large Data Volume: With 46 nodes, 4321 time steps, and two cards per node, the output files were large, necessitating robust error handling.

The Solution

The final Python script, built with pandas and datetime, processes both sheets and generates the required XPX files. Here’s how it works:

Key Features

• Sheet Processing: Reads both the 5-year and 25-year sheets (assumed names: "5 YR" and "25 YR") and creates separate XPX files.

• Time Conversion: Converts datetime values to hours (0.00 to 72.00) relative to the start time, handling both Timestamp and string formats.

• Non-Numeric Handling: Skips the 'CFS' header in Row 5 by filtering out non-numeric values during flow extraction.

• Output Format: Generates 46 TEO cards and 46 QCARDs per file, with time and flow values formatted to two decimal places.

How It Works

1. Reading the Excel File:

   • Uses pandas.read_excel with sheet_name to read each sheet.

   • Skips headers (header=None) to handle the raw structure.

2. Time Processing:

   • Extracts time values from Column B (rows 6–4326).

   • Handles both Timestamp (Excel’s default) and string formats, converting to hours relative to the start time.

   • Ensures the last time step is exactly 72.00 hours.

3. Flow Processing:

   • Extracts node names from Row 2 (Columns C–AW).

   • Skips the 'CFS' header in Row 5 by attempting to convert each value to a float and ignoring non-numeric entries.

   • Formats flow values to two decimal places.

4. Output Generation:

   • Creates output_5yr.xpx and output_25yr.xpx, each with 46 TEO cards and 46 QCARDs.

   • Each TEO card lists 4321 time steps (0.00 to 72.00 hours).

   • Each QCARD lists 4321 flow values in cfs.

Example Output

For the 5-year flows (all 0.00 in the provided excerpt), output_5yr.xpx looks like (abridged):

The output_25yr.xpx file follows the same structure, with flow values from the 25-year sheet.

Overcoming Challenges

• Timestamp Error: The strptime() argument 1 must be str, not Timestamp error was fixed by checking for Timestamp objects and converting them to datetime using to_pydatetime().

• CFS Header: The 'CFS' error could not convert string to float was resolved by explicitly filtering out non-numeric values during flow extraction, ensuring only numeric data from Row 6 onward was processed.

• Multiple Sheets: The script was extended to process both sheets independently, with separate output files specified via the output_file parameter.

How to Use

Install Dependencies:

pip install pandas openpyxl

Update Sheet Names:

• Check the sheet names in the Excel file:

import pandas as pd
print(pd.ExcelFile("25 and 5 yr flows.xlsx").sheet_names)

Update sheet_name="5 YR" and sheet_name="25 YR" in the script if needed.

Run the Script:

Check Output:

• output_5yr.xpx and output_25yr.xpx will be created in the same directory.

Tips and Considerations

• Sheet Names: If the sheet names differ, update them in the script to avoid errors.

• Non-Zero Flows: The script handles non-zero flows in the whole dataset; however, verify the output to ensure actual flows are present.

• TEO Card Format: The script uses DATA TEO "<node_name>" 0 4321 <time_values>. Confirm this matches your XPX requirements (e.g., compare total time in minutes).

• Scalability: The script handles 46 nodes and 4321 time steps, but can be adapted for different node counts or time steps.

Conclusion

This Python script offers a robust solution for converting HEC-HMS Excel data into XPX files, addressing common pitfalls such as non-numeric headers and datetime formats. By processing both 5-year and 25-year flows into separate files, it streamlines integration with XPSWMM or similar tools. Whether you’re a hydrologist or engineer, this approach can save time and reduce errors in your workflow.

Source Code

If you encounter issues or require customization (e.g., different sheet structures or TEO card formats), please feel free to modify the script or reach out for assistance. Happy modeling!