如何用Python提取Excel中重复出现的TX_DROP列数据

Problem Context #

I'm working with an Excel table where the header TX_DROP pops up repeatedly. Every time it appears, it's followed by 80-90 rows of related data. The catch? This header shows up in unpredictable row/column positions, and the table's total rows/columns can vary too. I need a Python script to yank out all the data tied to TX_DROP across the entire sheet.

Here's a simplified snippet of what the table looks like:

Granularity ETH_RX RX_DROP TX_DROP
15 Min      2      8       3
15 Min      0      6       12
15 Min      11     5       0
15 Min      1      5       4
Granularity ETH_RX TX_DROP RX_DROP
15 Min      0      1       0
15 Min      0      0       4
15 Min      12     11      8
15 Min      90     23      9
Granularity TX_DROP ETH_RX RX_DROP ETH_TX
15 Min      30      0       0       10
15 Min      4       0       0       11
15 Min      7       0       0       5
15 Min      8       0       0       3

And this is the output I'm aiming for:

TX_DROP
3
12
0
4
1
0
11
23
30
4
7
8

Solution #

We can use pandas—the go-to library for tabular data in Python—to handle this messy structure. The plan is simple: scan each row to spot when TX_DROP is a header, note its column position, then grab all values from that column until we hit the next header row (marked by "Granularity" in our example).

Step 1: Install required tools

First, make sure you have the necessary libraries installed:

pip install pandas openpyxl

openpyxl lets us read modern .xlsx files, so don't skip it.

Step 2: The Python Script

Here's a script that implements the logic:

import pandas as pd

def extract_tx_drop(file_path):
    # Load the entire sheet without assuming fixed headers
    df = pd.read_excel(file_path, header=None)
    
    tx_drop_values = []
    tx_col_index = None
    is_collecting = False
    
    # Loop through every row in the sheet
    for _, row in df.iterrows():
        # Check if this is a new header row (has "Granularity")
        if "Granularity" in row.values:
            # Look for TX_DROP in this header row
            for col_num, cell_value in enumerate(row.values):
                if cell_value == "TX_DROP":
                    tx_col_index = col_num
                    is_collecting = True
                    break
            # If TX_DROP isn't in this header, stop collecting
            else:
                is_collecting = False
        # If we're in collection mode, grab the value from TX_DROP column
        elif is_collecting and tx_col_index is not None:
            cell_val = row[tx_col_index]
            # Skip empty cells to avoid junk entries
            if pd.notna(cell_val) and str(cell_val).strip() != "":
                tx_drop_values.append(cell_val)
    
    # Format the result to match the desired output
    result_lines = ["TX_DROP"] + [str(val) for val in tx_drop_values]
    return "\n".join(result_lines)

# Run the script with your file path
if __name__ == "__main__":
    final_output = extract_tx_drop("your_excel_file.xlsx")
    print(final_output)

How this works:

1. Load without fixed headers: We read the entire sheet as a raw DataFrame since the structure is all over the place—no predefined column labels here.
2. Detect header rows: Any row with "Granularity" is a header. We check if TX_DROP is in that row, and if so, note which column it's in.
3. Collect values: Once we know where TX_DROP lives, we start pulling values from that column in every subsequent row until we hit the next header.
4. Clean up output: We assemble the collected values into the clean, list-like format you want.

Test it with the example

If you plug the sample table into an Excel file and run the script, you'll get exactly the output you're looking for.

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