如何将单列中的相同数值拆分至不同列？

Hey there! Let's figure out how to split those duplicate values from a single column into separate columns—perfect for organizing your data like [1,1,1,1,2,2,3,3,3,3,4,5,6,6,...] into columns where each column holds all instances of one unique number. I'll walk you through two practical methods, depending on whether you prefer using Excel or Python for this task.

This is great if you're working directly in a spreadsheet and don't want to write code. Let's assume your raw data is in column A, starting at cell A1.

• Step 1: Get all unique values
  In cell B1, enter the formula =UNIQUE(A:A) and hit enter. This will spill out all the distinct numbers from your data (1, 2, 3, 4, 5, 6, etc.) into separate cells across the top row—each will become a new column header for your split data.

• Step 2: Extract matching values for each unique number

  • If you're using Excel 365 (or Excel 2021 with dynamic arrays), this is super simple. In cell B2, enter:

    =FILTER($A:$A, $A:$A=B$1)
    

    Hit enter, and Excel will automatically spill all instances of the number in B1 down the column. Just drag this formula across to the other unique value columns, and each will populate with its matching values.
  • For older Excel versions (no dynamic arrays), use an array formula. In cell B2, enter:

    =INDEX($A:$A, SMALL(IF($A:$A=B$1, ROW($A:$A)), ROW(A1)))
    

    Then press Ctrl+Shift+Enter (this tells Excel it's an array formula). Drag this formula down until you see #NUM! (that means there are no more values for that number), then drag it across to the other columns.

This is ideal for larger datasets, or if you want to automate this process. Here's how to do it:

First, make sure you have Pandas installed (pip install pandas if you don't). Then follow these steps:

1. Import Pandas and load your data

   import pandas as pd
   
   # Replace this with your actual data loading (e.g., pd.read_csv("your_file.csv"))
   raw_data = {"values": [1,1,1,1,2,2,3,3,3,3,4,5,6,6]}
   df = pd.DataFrame(raw_data)
   

2. Add a row identifier for each group
   We need to track the position of each value within its group so we can align them into rows later:

   df["row_num"] = df.groupby("values").cumcount()
   

3. Pivot the data to split into columns
   This will turn each unique value into a column, with all its instances aligned by row:

   split_df = df.pivot(index="row_num", columns="values", values="values").reset_index(drop=True)
   
   # Optional: Rename columns to be more readable
   split_df.columns = [f"Value_{val}" for val in split_df.columns]
   
   # Optional: Replace NaN (empty spots) with blank strings
   split_df = split_df.fillna("")
   

Now split_df will have your data split into columns, each holding all instances of one unique number!

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