如何将带连字符、逗号的端口范围列表转换为单值一维数组（Excel/Python）

Got it, let's tackle this problem with two straightforward solutions—one using Excel (great for folks who prefer a GUI tool) and another with Python (perfect for automation or more complex data handling). Both will convert your port/range list into a flat, single-column format while preserving frequency information.

This method uses Power Query (built into modern Excel versions) to efficiently expand ranges and clean up the data, then we'll add frequency counts.

1. Prepare the raw data
   Paste your original port list into cell A1 of a new Excel sheet. It should look like:

   3074 88, 3074 1935, 3478-3480 3074, 3478-3479 27015-27030, 27036-27037 4380, 27000-27031, 27036

2. Split entries into separate rows

   • Select cell A1, go to the Data tab, click Text to Columns.
   • Choose Delimited, click Next, check Comma as the delimiter, then Finish. Now each port/range group will be in its own cell in row 1.
   • Select all cells in row 1, right-click, choose Paste Special > Transpose to move them into column A (so each group is a row).

3. Use Power Query to expand ranges

   • Select column A, go to Data > From Table/Range (check "My table has headers" if you added a header like "Port Groups").
   • In Power Query Editor:
     1. Go to Transform > Split Column > By Delimiter, choose Space as the delimiter, split into Rows (this breaks each group into individual port/range entries).
     2. Add a custom column: Go to Add Column > Custom Column, paste this formula:

        = if Text.Contains([Port Groups], "-") then List.Numbers(Number.From(Text.BeforeDelimiter([Port Groups], "-")), Number.From(Text.AfterDelimiter([Port Groups], "-")) - Number.From(Text.BeforeDelimiter([Port Groups], "-")) + 1) else {Number.From([Port Groups])}
        

        Rename this column to Expanded Ports.
     3. Click the expand icon (🔽) next to Expanded Ports and choose Expand to New Rows.
     4. Go to Home > Close & Load to bring the flat list back to Excel.

4. Add frequency counts

   • Now you have a single column of all ports (with duplicates to show frequency). To get a summary:
     • Select the column, go to Insert > PivotTable.
     • Drag the port column to the Rows area and again to the Values area (set Values to "Count"). You'll get a table showing each unique port and how many times it appears.

This is ideal if you need to automate this process or work with larger datasets. We'll use Python's built-in collections.Counter to handle frequency tracking.

from collections import Counter

# Your original port data as a string
raw_port_data = "3074 88, 3074 1935, 3478-3480 3074, 3478-3479 27015-27030, 27036-27037 4380, 27000-27031, 27036"

# Step 1: Split the raw data into individual port/range groups
port_groups = [group.strip() for group in raw_port_data.split(',')]

# Step 2: Define a helper function to expand a range string into a list of integers
def expand_port_range(range_str):
    if '-' in range_str:
        start, end = map(int, range_str.split('-'))
        return list(range(start, end + 1))
    else:
        return [int(range_str)]

# Step 3: Collect all ports into a single flat list
all_ports = []
for group in port_groups:
    # Split each group into individual port/range entries
    port_entries = group.split()
    for entry in port_entries:
        all_ports.extend(expand_port_range(entry))

# Step 4: Get frequency counts
port_frequencies = Counter(all_ports)

# Output the flat single-column list (preserves duplicates for frequency)
print("Flat single-column port list (duplicates show frequency):")
for port in all_ports:
    print(port)

# Optional: Output unique ports with their counts
print("\nUnique ports and their frequencies:")
for port, count in sorted(port_frequencies.items()):
    print(f"{port}: {count}")

How this works: #

• We first split the raw data into manageable groups, then break each group into individual port/range strings.
• The expand_port_range function turns strings like "3478-3480" into [3478, 3479, 3480].
• We collect all expanded ports into a single list (duplicates are kept, so the number of times a port appears is its frequency).
• Counter gives us a neat summary of each unique port and how often it shows up.

内容的提问来源于stack exchange，提问作者espogian