|
import pandas as pd
import numpy as np
df=pd.read_excel('查最大值.xlsx')
df1=pd.read_excel('查最大值.xlsx', sheet_name=1)
df['序号']=pd.Series(np.array(np.arange(2,1493)))
df1['序号1']=pd.Series(np.array(np.linspace(2,10397,10396)))
df1.to_excel('查最大值B.xlsx',index=False)
df1=pd.read_excel('查最大值B.xlsx')
df1 = df1.dropna(axis=0, thresh=3)
df = pd.merge(df,df1,on='cjd+ma',how='left')
df = df.rename(columns={5:'AR',10:'AS',15:'AT',20:'AU',25:'AV',30:'AW'})
df = df.dropna(axis=0, thresh=2)
df2 = df.iloc[:, 2:-1]
df['max'] = df2.max(axis=1).fillna('无')
df.to_excel('查最大值C.xlsx')
df['列标'] = np.select([df['max'] == df['AR'], df['max'] == df['AS'], df['max'] == df['AT'], df['max'] == df['AU'],
df['max'] == df['AV'],df['max'] == df['AW']],['AR','AS','AT','AU','AV','AW'])
df['行标'] = df['序号1']
df['结果']=df['列标']+df['行标'].astype('str')
lst=[]
g = df.groupby('cjd+ma')
for t,d in g:
d=d[d['max']==d['max'].max()]
d=d.head(1)
lst.append(d)
df1=pd.concat(lst)
df1=df1.sort_values(by='序号',ascending=True)
df.to_excel('查最大值A1.xlsx',index=False)
df1.to_excel('查最大值A2.xlsx',index=False)
print(df) |
-
-
|