使用Python拆分CSV文件中含数值与范围的单列数据

Got it, let's figure out how to split that combined CSV column into two separate columns—one for the standalone numbers (like 91.1, 93.9) and one for their corresponding ranges (like [69.6-118.8], [74.5-118.3]). I'll cover both spreadsheet tools (Excel/Google Sheets) and Python with Pandas, since those are the most common approaches:

Using Excel or Google Sheets #

If each cell has a single value-range pair #

Suppose your original data is in column A, with each cell looking like 91.1 [69.6-118.8]:

• Extract standalone numbers (Column B)
  Use this formula to grab everything before the first space:

  =TRIM(LEFT(A1, FIND(" ", A1)-1))
  

  Drag the fill handle down to apply this to all rows. The TRIM function cleans up any accidental extra spaces, just in case.

• Extract range data (Column C)
  This formula grabs everything starting from the [ character to the end of the cell:

  =TRIM(MID(A1, FIND("[", A1), LEN(A1)-FIND("[", A1)+1))
  

  Again, drag down to apply to all rows. This works the same in both Excel and Google Sheets.

If one cell has multiple value-range pairs (like your full example string) #

If you have a single cell with a long string like 91.1 [69.6-118.8] 93.9 [74.5-118.3] ..., here's how to split it into rows and columns:

1. Split the string into individual elements
   In Excel (Office 365+/Excel Online), use TEXTSPLIT to break the string apart by spaces:

   =TEXTSPLIT(A1, " ", , TRUE)
   

   This will spill out each number and range into separate cells in a row.

   In Google Sheets, use SPLIT:

   =SPLIT(A1, " ")
   

2. Separate numbers and ranges into two columns
   For the numbers column (say, starting at B1):

   =INDEX(TEXTSPLIT($A$1, " ", , TRUE), 2*ROW()-1)
   

   For the ranges column (starting at C1):

   =INDEX(TEXTSPLIT($A$1, " ", , TRUE), 2*ROW())
   

   Drag both formulas down until you see #REF! errors (that means you've covered all pairs). For Google Sheets, replace TEXTSPLIT with SPLIT in the formulas.

Using Python with Pandas (great for large datasets) #

If you're working with a big CSV file, coding with Pandas is way more efficient. Here's a step-by-step script:

import pandas as pd

# Read your CSV file
df = pd.read_csv("your_file.csv")

# Split the combined column into two new columns
# The regex captures: (1) the number (digits + decimal), (2) the range (starts with [, ends with ])
df[["standalone_value", "range_data"]] = df["combined_column_name"].str.extract(
    r"(\d+\.\d+) (\[\d+\.\d+-\d+\.\d+\])",
    expand=True
)

# If your cells have multiple value-range pairs, use this instead:
# 1. Find all pairs in each cell
# df["pairs"] = df["combined_column_name"].str.findall(r"(\d+\.\d+) (\[\d+\.\d+-\d+\.\d+\])")
# 2. Expand each pair into its own row
# df = df.explode("pairs").reset_index(drop=True)
# 3. Split the pairs into two columns
# df[["standalone_value", "range_data"]] = pd.DataFrame(df["pairs"].tolist(), index=df.index)

# Save the split data to a new CSV
df.to_csv("split_data.csv", index=False)

Just replace "your_file.csv" with your actual CSV filename, and "combined_column_name" with the name of the column holding your original data.

Hope that helps—pick the method that fits your workflow best!

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