通过excel文件中的平均数，生成方差为指定大小，和指定数量的随机数

import numpy as np
import pandas as pd


def generate_numbers(mean, variance, count):
    if count < 2:
        raise ValueError("请至少生成两个数，以计算方差。")

    while True:
        numbers = np.random.normal(mean, np.sqrt(variance), count)
        calc_mean = np.mean(numbers)
        calc_variance = np.var(numbers)

        if abs(mean - calc_mean) <= 0.1 and calc_variance <= variance:
            break

    return numbers.tolist()


def create_random(full_path, sheet_name, variance, count):
    """
    生成数据
    :param full_path: 文件地址
    :param sheet_name: 表格名
    :param variance: 方差
    :param count: 需要生成的几个数
    :return:
    """
    frame = pd.read_excel(io='{}.xlsx'.format(full_path), sheet_name=sheet_name)
    list_all = [[] for _ in range(count)]
    compute_variance_list = []
    for v in frame.itertuples():
        rand_ls = generate_numbers(mean=v.平均数, variance=variance, count=count)
        compute_variance = sum([(rand_ls[j] - v.平均数) ** 2 for j in range(count)])/count
        [list_all[j].append(rand_ls[j]) for j in range(count)]
        compute_variance_list.append(compute_variance)
    frame['variance'] = compute_variance_list
    for v in range(count):
        frame['random_{}'.format(v + 1)] = list_all[v]

    return frame


def create_file(create_file_name, full_path, sheet_name, variance, count):
    """
    生成文件
    :param create_file_name: 生成的文件名
    :param full_path: 文件地址
    :param sheet_name: 表格名
    :param variance: 方差
    :param count: 随机数个数
    :return:
    """
    writer = pd.ExcelWriter('{}.xlsx'.format(create_file_name))
    frame = create_random(full_path=full_path, sheet_name=sheet_name, variance=variance, count=count)
    frame.to_excel(writer, float_format='%.5f')
    writer.save()


if __name__ == '__main__':
    full_path = input('输入文件名：')
    sheet_name = input('输入表格名（例如：Sheet1）：')
    variance = int(input('输入方差：'))
    count = int(input('输入随机数：'))
    create_file_name = input('输入生成的文件名：')
    create_file(create_file_name=create_file_name, full_path=full_path, sheet_name=sheet_name, variance=variance,
                count=count)