使用Python在excel中打印唯一的名字、姓氏、电话号码、电子邮件和地址详细信息列表

Question

我们已经创建了一个python代码来打印“ABCD MNOP 282-567-4285 455 mindspace St.，brisbane GA 35001abcdmnop@gmail.com“除了名字、姓氏、手机号码和地址什么都没有

代码如下

import random
first_names = ['ABCD', 'EFGH', 'IJKL', 'MNOP', 'QRST', 'UVWX', 'YZ']
last_names = ['ABCD', 'EFGH', 'IJKL', 'MNOP', 'QRST', 'UVWX', 'YZ']

street_names = ['indralok', 'mindspace', 'marine drive', 'kora kendra', 'bamdstand', 'mount mary', 
                'milan', 'paris','dubai', 'dindoshi', 'magathane', 'arnala']

cities_names = ['mumbai', 'banglore', 'pune', 'lucknow', 'patna', 'goa', 'ahmedabad', 'sydney', 
               'melbourne', 'brisbane','perth']

states = ['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 
          'KS', 'KY','LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 
          'NY', 'NC', 'ND', 'OH','OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 
          'WV', 'WI', 'WY']

for num in range(100):

    first = random.choice(first_names)
    last = random.choice(last_names)

    phone = f'{random.randint(100, 999)}-567-{random.randint(1000, 9999)}'

    street_num = random.randint(100, 999)
    street = random.choice(street_names)
    city = random.choice(cities_names)
    state = random.choice(states)
    zip_code = random.randint(10000, 99999)
    address = f'{street_num} {street} St., {city} {state} {zip_code}'

    email = first.lower() + last.lower() + '@gmail.com'

    print (f'{first} {last} {phone} {address} {email}\n')

它将结果输出为

First Last Phone Number    Address                     Zip  Email Address

ABCD MNOP 282-567-4285 455 mindspace St., brisbane GA 35001 abcdmnop@gmail.com

在控制台窗口中

我们要做的是，一旦定义了范围，就必须打印一个范围或名字、姓氏、地址等的随机组合，作为excel文件的输出

对于我们定义的范围，预期输出类似于excel中的输出：

MNOP MNOP 539-567-9221 106 paris St., goa LA 76016 mnopmnop@gmail.com

ABCD YZ 326-567-9757 173 magathane St., patna NV 52358 abcdyz@gmail.com

EFGH UVWX 507-567-6504 939 paris St., melbourne PA 45311 efghuvwx@gmail.com

如果我们将范围定义为100，那么应该得到100个不同的组合。我们不是python专家，只是刚刚开始，我们非常感谢您的帮助

Answer 1

试试下面的方法

import random
import xlsxwriter

first_names = ['ABCD', 'EFGH', 'IJKL', 'MNOP', 'QRST', 'UVWX', 'YZ']
last_names = ['ABCD', 'EFGH', 'IJKL', 'MNOP', 'QRST', 'UVWX', 'YZ']
street_names = ['indralok', 'mindspace', 'marine drive', 'kora kendra', 'bamdstand', 'mount mary', 'milan', 'paris','dubai', 'dindoshi', 'magathane', 'arnala']
cities_names = ['mumbai', 'banglore', 'pune', 'lucknow', 'patna', 'goa', 'ahmedabad', 'sydney', 'melbourne', 'brisbane','perth']
states = ['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY','LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH','OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']

N = 10

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

def get_random_value(input_list):
    return input_list[random.randint(0, len(input_list)-1)]

worksheet.write(0, 0, "First Name")
worksheet.write(0, 1, "Last Name")
worksheet.write(0, 2, "Street")
worksheet.write(0, 3, "City")
worksheet.write(0, 4, "State")

for i in range(1,N+1):    
    worksheet.write(i, 0, get_random_value(first_names))
    worksheet.write(i, 1, get_random_value(last_names))
    worksheet.write(i, 2, get_random_value(street_names))
    worksheet.write(i, 3, get_random_value(cities_names))
    worksheet.write(i, 4, get_random_value(states))

workbook.close()

Answer 2

也许有更好的解决方案，但您可以检查您的随机组合是否在已创建的随机组合列表中

Answer 3

您可以将数据存储为pandas数据框，然后将该数据框写入excel文件（.csv）

例如：

import pandas as pd

data_list = []

for num in range(100):

    first = random.choice(first_names)
    last = random.choice(last_names)
    phone = f'{random.randint(100, 999)}-567-{random.randint(1000, 9999)}'
    street_num = random.randint(100, 999)
    street = random.choice(street_names)
    city = random.choice(cities_names)
    state = random.choice(states)
    zip_code = random.randint(10000, 99999)
    address = f'{street_num} {street} St., {city} {state} {zip_code}'
    email = first.lower() + last.lower() + '@gmail.com'

    data_list.append([first, last, phone, address, email])

data_df = pd.DataFrame(data_list)
data_df.columns = ['First', 'Last', 'Phone Number', 'Address', 'Email Address']
data_list.to_csv('data_excel.csv')