如何用Pandas基于现有Excel数据的唯一元素生成10^4次随机DataFrame？

First, let's break down your requirements clearly:

• You have an Excel file with columns x and y
• n_nodes = number of unique values in x
• n_arrows = total number of rows (since each row represents a connection between an x element and a y element)
• Need to generate 10,000 random DataFrames that mirror this structure: same number of rows, using the unique x values from your original data, and y values (presumably from your original y's unique set, though I'll cover flexible options).

Step 1: Load and Prepare Original Data #

First, we'll load your Excel file and extract the key components we need:

import pandas as pd
import numpy as np

# Load original data
original_df = pd.read_excel("your_file_path.xlsx")

# Extract key values
unique_x = original_df["x"].unique()
unique_y = original_df["y"].unique()
n_arrows = len(original_df)  # Total connections
n_nodes = len(unique_x)      # Unique nodes

Step 2: Generate Random DataFrames (Basic Version) #

If you just need random x (sampled from unique x values) and random y (sampled from unique y values) with exactly n_arrows rows per DataFrame, here's a straightforward implementation:

random_dfs = []

for _ in range(10**4):
    # Randomly sample x values (with replacement) to match total connections
    random_x = np.random.choice(unique_x, size=n_arrows, replace=True)
    # Randomly sample y values (with replacement)
    random_y = np.random.choice(unique_y, size=n_arrows, replace=True)
    # Create DataFrame and add to list
    random_dfs.append(pd.DataFrame({"x": random_x, "y": random_y}))

Step 3: Optimize for Speed and Memory #

Generating 10,000 DataFrames in a Python loop can be slow. Instead, we can leverage numpy's vectorized operations to pre-generate all data at once, then convert to DataFrames in a list comprehension—this is significantly faster:

# Pre-generate all x samples: shape (10000, n_arrows)
all_x = np.random.choice(unique_x, size=(10**4, n_arrows), replace=True)
# Pre-generate all y samples
all_y = np.random.choice(unique_y, size=(10**4, n_arrows), replace=True)

# Create list of DataFrames efficiently
random_dfs = [pd.DataFrame({"x": all_x[i], "y": all_y[i]}) for i in range(10**4)]

Memory-Saving Alternative #

If you don't need to store all 10,000 DataFrames in memory at once (e.g., you process each one and save it to disk or compute metrics immediately), generate and process them one by one to avoid high memory usage:

for idx in range(10**4):
    random_x = np.random.choice(unique_x, size=n_arrows, replace=True)
    random_y = np.random.choice(unique_y, size=n_arrows, replace=True)
    current_df = pd.DataFrame({"x": random_x, "y": random_y})
    
    # Example processing: save to CSV or compute stats
    current_df.to_csv(f"random_df_{idx}.csv", index=False)
    # Or compute metrics like node connection counts
    node_counts = current_df["x"].value_counts()
    # ... add your processing logic here

Step 4: Preserve Original Node Connection Counts (Optional) #

If you want each random DataFrame to maintain the same number of connections per node as your original data (only randomize which y values they connect to), use this approach:

# Get original connection counts per node
original_node_counts = original_df["x"].value_counts()

random_dfs = []
for _ in range(10**4):
    # Recreate x column with same counts as original
    x_col = []
    for node, count in original_node_counts.items():
        x_col.extend([node] * count)
    # Shuffle to randomize order (optional but recommended)
    np.random.shuffle(x_col)
    
    # Generate random y values
    y_col = np.random.choice(unique_y, size=n_arrows, replace=True)
    
    random_dfs.append(pd.DataFrame({"x": x_col, "y": y_col}))

Key Notes #

• Random Seed: If you need reproducible results, set a random seed with np.random.seed(42) before generating data.
• Data Types: Ensure the random x and y values match the data types of your original columns (numpy's choice preserves types by default).
• Performance: For very large n_arrows (e.g., 100k+ rows per DataFrame), the pre-generated numpy array approach is critical to avoid slow loops.

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