最近需要对一批数据进行处理,简单的写了一下pandas 处理的方法
import os, django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'yours project.settings')
django.setup()
from django.db import transaction
import pandas as pd
import re
file_path = "./做市策略市场.xlsx"
df = pd.read_excel(file_path, engine='openpyxl', sheet_name='Sheet1', header=None)
data1 = df.values.tolist()
data2 = [i for i in data1 if "抛单" not in i]
data3 = [i for i in data2 if "做市" not in i]
df = pd.DataFrame(data3, columns=data2[0])
df = df.dropna(how="all")
df = df.dropna(axis=1)
cmc = re.compile(r'([0-9]{1,3}\.){3}[0-9]{1,3}$')
df["服务器ip"] = df["服务器ip"].apply(lambda x: re.search("((?<![\.\d])(?:\d{1,3}\.){3}\d{1,3}(?![\.\d]))", str(x)).group(1))
df = df[df["备注"] == "已完成"]
project = Project.objects.get(name="making")
error_data = []
for k, v in df.iterrows():
print(v.get("服务器ip"), v.get("市场"), )
pa = Pa.objects.filter(name=v.get("市场"))
with transaction.atomic():
save_id = transaction.savepoint()
try:
if pa.exists():
pa.update(status="closing")
Pa.objects.create(
name=v.get("市场"),
status="normal",
ip=v.get("服务器ip"),
project_id=project.id
)
except Exception as e:
transaction.savepoint_rollback(save_id)
error_data.append(f'name:{v.get("市场")} ip:{v.get("服务器ip")} error:{e}')
continue
transaction.savepoint_commit(save_id)
print(f'ip:{v.get("服务器ip")}, name:{v.get("市场")} 处理完成')
print(error_data)
仅供借鉴,如果你有好的方法,可以写在评论区