如何基于名称片段对CSV文件中的计算机名称分组并统计数量

Hey there, let's tackle this problem together—you've got a CSV with 11k computers, each named with a location/department prefix like XUSAIT, and you need to group them by that prefix and count how many are in each group. Here are a few practical, easy-to-implement methods depending on what tools you're comfortable with:

1. Excel/Google Sheets (No-Code, Quick Setup) #

This is perfect if you're already working with spreadsheets:

• Step 1: Extract the prefix
  Assume your computer names are in column A. In cell B2, enter this formula to grab the first 6 characters of each name:
  =LEFT(A2,6)
  Drag the fill handle down to apply this to all rows—you'll now have a column of prefixes like XUSAIT, XCANHR, etc.
• Step 2: Count groups
  Two simple options here:
  1. Use COUNTIF for row-level counts: In cell C2, enter =COUNTIF(B:B,B2) and drag down. This shows the total count for each row's prefix.
  2. Use a Pivot Table for a clean summary:
     • Select both the computer name column (A) and prefix column (B)
     • Go to Insert > Pivot Table
     • Drag the "Prefix" field to the Rows area, and "ComputerName" to the Values area (set the value field to "Count")
     • You'll get a sorted, deduplicated list of all prefixes with their total counts—easy to filter or sort by size.

2. Python with Pandas (Fast for Large Datasets) #

If your CSV is bulky or you want to automate this for future use, Python's pandas library is ideal:

• First, install pandas if you haven't:
  pip install pandas
• Then run this short script:

import pandas as pd

# Load your CSV file (replace "your_computers.csv" with your actual file path)
df = pd.read_csv("your_computers.csv")

# Extract the first 6 characters as the prefix column
df["Prefix"] = df["ComputerName"].str[:6]

# Group by prefix and count the number of computers in each group
prefix_counts = df.groupby("Prefix")["ComputerName"].count().sort_values(ascending=False)

# Print the result to console, or save to a new CSV
print(prefix_counts)
prefix_counts.to_csv("computer_prefix_counts.csv")

This will output counts sorted from largest to smallest, and save the results to a new CSV for easy sharing.

3. Excel Power Query (Visual, No-Code for Complex Data) #

If you want more control than basic formulas without writing code, Power Query is a great middle ground:

• Select your data range, then go to Data > From Table/Range to open the Power Query Editor.
• Select the computer name column, go to Add Column > Custom Column, and enter this formula to create a prefix column:
  Text.Start([ComputerName],6)
• Name the column "Prefix" and click OK.
• Go to Transform > Group By, then set:
  • Group by: Prefix
  • New column name: Count
  • Operation: Count rows
• Click OK, then Close & Load to bring the grouped counts back into your Excel sheet.

4. Command Line (awk, for Linux/macOS/WSL Users) #

If you're comfortable with terminal commands, this is the fastest way for one-off processing:
Run this command (replace your_computers.csv with your file path, and adjust $1 if your computer names are in a different column):

awk -F ',' '{print substr($1,1,6)}' your_computers.csv | sort | uniq -c | sort -nr

Breakdown of what this does:

• awk -F ',' '{print substr($1,1,6)}': Extracts the first 6 characters from the first column (split by commas)
• sort: Sorts the prefixes alphabetically
• uniq -c: Counts occurrences of each unique prefix
• sort -nr: Sorts the final results by count (highest to lowest)

Pick the method that fits your workflow—all of these will get you the group counts you need. For 11k rows, even Excel should handle it smoothly, but Python or command line will be faster if you need to repeat this task regularly.

内容的提问来源于stack exchange，提问作者IT-Tech