对从微信上复制的数据进行格式化，按列重新存储表格

import pandas
import re

with open(r'C:\Users\Administrator\Desktop\密码(1).txt', 'rb') as r1:
    rlist = r1.readlines()

list_yuanshi = []
ip_list = []
password_list = []

# 处理读取的txt文件数据
for i in rlist:
    rstr = i.decode()
    r1 = rstr.strip()
    list_yuanshi.append(r1)
print(list_yuanshi)


for i in list_yuanshi:
    ip1 = re.match('^10[.]{1}18[.]{1}[0-9]{,3}[.]{1}[0-9]{,3}', i).group()
    ip_list.append(ip1)
    passward = i.split(ip1)[-1].strip()
    password_list.append(passward)
print(ip_list)
print(password_list)
# 读取表格
filename = '虚拟机ip-test.xlsx'
df = pandas.read_excel(filename, sheet_name=0)
hanghao=df.shape[0]

ip_load = df.get('ip地址')
#print(ip_load)
for i in ip_load:
    if i in ip_list:
        print(i)
        try:
            index1 = ip_list.index(str(i))
            password_list.pop(index1)
            ip_list.remove(str(i))
            print("索引：" + str(index1))
        except Exception as e:
            print(e)


if ip_list:
    new_row = pandas.DataFrame({'ip地址': ip_list, '部门': '', '使用人': '', '普通用户': '', '密码': password_list})
    print(new_row)
    with pandas.ExcelWriter('虚拟机ip-test.xlsx',engine='openpyxl',mode='w') as w:
         new_row.to_excel(w,index=False,sheet_name='aa2')
         print('更新成功')