import pandas as pd
import numpy as np
from itertools import groupby
#导入文件
data1=pd.read_csv("/Users/yin/Desktop/Test/Test1.csv")
#修改SKU
s = data1["sku"]
spu=[]
for row in s:
row = [''.join(list(g)) for k, g in groupby(row, key=lambda x: x.isdigit())]
spu.append(row[0])
spu=pd.DataFrame(spu,columns=["spu"])
spu.to_csv("/Users/yin/Desktop/Test/TestNum3.csv")
data=pd.merge(Test1,TestNum3.csv,letf_index=True,right_index=True)
#此处采用的是合并两个表格的方法。*其他方法我再想
data1["spu"]= [''.join(list(g)) for k, g in groupby(data1["sku"], key=lambda x: x.isdigit())][0] #有空试一下
更新新方法
for I,rowI in enumerate(data1["spu"]):
rowI=rowI.strip()
rowI=["".join(list(g)) for k,g in groupby(rowI,key=lambda x:x.isdigit())]
data1["spu"][I] = rowI[0]
#处理时间
data1["日期"]=pd.to_datetime(data1["日期"],format="%Y/%m/%d")
data1["年份"] = data1['日期'].dt.year
data1 = data1.set_index("日期")
data1["年份"] = data1['日期'].dt.year #设置年份列
#筛选时间
data1=data1['2020-05-01':'2021-05-01']
#透视
userRatings = data1.pivot_table(index = ["日期","sku"], values = "sku销售额(元)" ,aggfunc = np.sum)
#data1.to_excel("/Users/yin/Desktop/Test/TestNum3.xlsx")
#导出
userRatings.to_excel("/Users/yin/Desktop/Test/TestNum3.xlsx")
import pandas as pd
import numpy as np
from itertools import groupby
#导入文件
data1=pd.read_csv("/Users/yin/Desktop/Test/Test1.csv")
#插入一列
#修改
s = data1["sku"]
#print(s)
spu=[]
for row in s:
row = [''.join(list(g)) for k, g in groupby(row, key=lambda x: x.isdigit())]
spu.append(row[0])
spu=pd.DataFrame(spu,columns=["spu"])
#处理时间
data1["日期"]=pd.to_datetime(data1["日期"],format="%Y/%m/%d")
data1["年份"] = data1['日期'].dt.year
data1 = data1.set_index("日期")
#筛选时间
data1=data1['2020-05-01':'2021-05-01']
#透视
userRatings = data1.pivot_table(index = ["日期","sku"], values = "sku销售额(元)" ,aggfunc = np.sum)
#data1.to_excel("/Users/yin/Desktop/Test/TestNum3.xlsx")
#导出
userRatings.to_excel("/Users/yin/Desktop/Test/TestNum3.xlsx")