import csv
import matplotlib
matplotlib.use('Qt5Agg')
from collections import Counter
import matplotlib.pyplot as plt
import xlrd
def getgo(sku , bestprice):
result = []
ebaylist = []
with open("sku\\window regulator-oe\\feedback_"+sku+".csv", newline="") as f:
reader = csv.reader(f)
k = 0
prices = []
for row1 in reader:
if k == 0: # 去掉列名
k = 1
continue
if len(row1) == 0:
break
if round(float((row1[5]))) == bestprice:
sku = row1[0]
kind = row1[1]
ebayId = row1[2]
ebayno = row1[3]
price = row1[5]
currency = row1[6]
shop_name = row1[8]
ebay_close_p = row1[9]
ebay_close_c = row1[10]
if len(ebaylist) != 0 and ebayno in ebaylist:
continue
ebaylist.append(ebayno)
result.append([sku , kind , ebayId , ebayno,price , currency , shop_name, ebay_close_p, ebay_close_c])
return result
out = open("figure\\feedback.csv", "w", newline="") #写入数据
csv_writer = csv.writer(out)
csv_writer.writerow(["sku", "kind" , "ebayId","ebayno", "price", "currency", "shop_name","ebay_close_p","ebay_close_c"])
data = xlrd.open_workbook("window regulator-OE.xlsx") # 打开excel,读入sku
table = data.sheet_by_index(0) # 读sheet
nrows = table.nrows # 获得行数
result = []
for i in range(1, nrows): #
rows = table.row_values(i) # 行的数据放在数组里
sku = rows[0]
print(sku)
with open("sku\\window regulator-oe\\feedback_"+sku+".csv", newline="") as f:
reader = csv.reader(f)
k = 0
prices = []
for row1 in reader:
if k == 0: # 去掉列名
k = 1
continue
if len(row1) == 0:
break
y = round(float((row1[5]))) #四舍五入
prices.append(y) #将价格提取出来成列表
prices.sort() #对价格排序
count = Counter(prices) #返回counter对象,是字典的子类,可以采用字典的方法
price = []
sale = []
maxsale = 0
for key, value in count.items():
price.append(key)
sale.append(value)
if value > maxsale:
maxsale = value
bestprice = key
print(bestprice) #找到销量最高的价格
re = getgo(sku, bestprice)
for i in range(len(re)): #把销量最好的价格对应的ebayno写入数据
row = re[i]
csv_writer.writerow([row[0], row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8]])
#画图
plt.figure(str(sku))
p1 = plt.plot(price, sale, 'r', label="sales")
for xy in zip(price, sale):
plt.annotate(xy[0], xy=xy, xytext=(-10, 10), textcoords='offset points')
plt.grid(True)
plt.xlabel("price")
plt.ylabel("sale")
plt.title(sku)
plt.legend(loc=1) # 图例
plt.savefig("figure\\"+str(sku)+".png")
pandas读入excel表格数据
import pandas as pd
import numpy as np
from collections import Counter
import matplotlib.pyplot as plt
df = pd.read_csv("together_tag.csv")
print(df.head())
skunodf = pd.read_excel("skuno_tag.xlsx" , sheetname="Sheet1")
print(skunodf.head())
for rows in skunodf.values:
print(rows)
id = rows[1]
skuno = rows[2]
motor = rows[3]
group = rows[4]
fr = rows[5]
lr = rows[6]
print(skuno , motor , group , fr , lr)
#在数据表中寻找满足条件的数据
dfsku = df[(df.sku == skuno) & (df.motor == str(motor)) &(df.group == group) &(df.fr == fr) & (df.lr == lr)]
sumlen = len(dfsku)
# print(dfsku.describe())
prices = dfsku["price"].values
prices = np.round(prices,0)
# print(prices)
prices.sort() # 对价格排序
count = Counter(prices) # 返回counter对象,是字典的子类,可以采用字典的方法
price = []
sale = []
maxsale = 0
for key, value in count.items():
price.append(key)
sale.append(value)
if value > maxsale:
maxsale = value
bestprice = key
print(bestprice) # 找到销量最高的价格
print(sumlen)
# 画图
plt.figure(str(id))
p1 = plt.plot(price, sale, 'r', label="sales")
for xy in zip(price, sale):
plt.annotate(xy[0], xy=xy, xytext=(-10, 10), textcoords='offset points')
plt.grid(True)
plt.xlabel("price")
plt.ylabel("sale--sum:" + str(sumlen))
plt.title(str(id))
plt.legend(loc=1) # 图例
plt.savefig("sku\\tag\\" + str(id) + ".png")