基于Pandas的Python列数据读写及Mac序列号Excel批量查询脚本咨询

Hey there! Since you’ve already got the command-line logic to look up a single Mac serial number’s model using its last 3 or 4 characters, let’s expand that to handle Excel files with Pandas. This solution will read serial numbers from a column, run your lookup on each one, and write the results to an adjacent column in a new Excel file with headers intact.

First, Let’s Outline the Workflow #

• Use Pandas to load your input Excel file into a DataFrame (for easy column-based data handling)
• Apply your existing serial-to-model lookup function to every entry in the serial number column
• Add the model results as a new column next to the serials
• Export the updated DataFrame to a new Excel file, preserving all original headers

Step 1: Install Required Dependencies #

First, make sure you have the necessary libraries installed. Pandas handles the Excel work, and openpyxl lets it read/write modern .xlsx files:

pip install pandas openpyxl

(If you’re working with older .xls files, replace openpyxl with xlrd instead.)

Step 2: Full Script Implementation #

Below is a complete script—just replace the placeholder get_mac_model function with your existing command-line logic.

import pandas as pd

# ------------------------------
# Replace this with YOUR existing lookup logic
def get_mac_model(serial_number):
    # Example logic (swap this out with your actual code!)
    # Grab last 4 chars if serial is long enough, else last 3
    serial_str = str(serial_number).strip()
    if not serial_str:
        return "No Serial Provided"
    
    suffix = serial_str[-4:] if len(serial_str) >= 4 else serial_str[-3:]
    
    # Your actual model mapping/database lookup goes here
    model_database = {
        "X123": "MacBook Air 13-inch 2022",
        "Y4567": "Mac Studio 2023",
        "Z890": "iMac Pro 2017"
    }
    
    return model_database.get(suffix, "Unknown Model")
# ------------------------------

def process_serial_excel(input_path, output_path, serial_column_name="Serial Number"):
    # Load the input Excel file (Pandas auto-detects headers)
    df = pd.read_excel(input_path)
    
    # Make sure the serial column exists in the input file
    if serial_column_name not in df.columns:
        raise ValueError(f"Oops! Couldn't find a column named '{serial_column_name}' in your Excel file.")
    
    # Run the lookup on every serial number and create a new "Device Model" column
    df["Device Model"] = df[serial_column_name].apply(get_mac_model)
    
    # Save the results to a new Excel file (skip Pandas' default index column)
    df.to_excel(output_path, index=False, engine="openpyxl")
    print(f"Done! Results saved to {output_path}")

# Run the script with your file paths
if __name__ == "__main__":
    INPUT_EXCEL = "your_serial_list.xlsx"  # Replace with your input file path
    OUTPUT_EXCEL = "serials_with_models.xlsx"  # Replace with your desired output path
    process_serial_excel(INPUT_EXCEL, OUTPUT_EXCEL)

Key Details Explained #

• Reading Excel: pd.read_excel() automatically parses the first row as headers, so your existing column names stay intact.
• Applying the Lookup: df[serial_column_name].apply(get_mac_model) loops through every entry in the serial column, runs your lookup function, and stores the results in a new "Device Model" column.
• Saving the Output: to_excel(index=False) ensures we don’t add an extra Pandas index column to the output file, keeping your data clean.

Quick Tips for Edge Cases #

• Missing Serial Numbers: The example script already handles empty/blank serials, but you can tweak the return message to fit your needs.
• Large Datasets: If you’re processing thousands of serials, the apply method is fast enough, but you can install swifter (pip install swifter) and replace apply with swifter.apply for automatic parallelization.
• API Lookups: If your model lookup calls an external API, wrap the logic in a try/except block to avoid crashing the script if one lookup fails:

  def get_mac_model(serial_number):
      try:
          # Your API call logic here
      except Exception as e:
          return f"Lookup Failed: {str(e)}"
  

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