这是我的dfe
:-
ID CATEG LEVEL COLS VALUE COMMENTS
1 A PG Apple 428 comment1
1 A CD Apple 175 comment1
1 C PG Apple 226 comment1
1 C AB Apple 884 comment1
1 C CD Apple 288 comment1
1 B PG Apple 712 comment1
1 B AB Apple 849 comment1
2 B CD Apple 376 comment1
2 C None Orange 591 comment1
2 B CD Orange 135 comment1
2 D None Orange 423 comment1
2 A AB Orange 1e13 comment1
2 D PG Orange 1e15 comment2
我创建pivot
这样的:
df=pd.pivot_table(dfe,index=['ID','CATEG','LEVEL'],columns='COLS',values=['VALUE'])
我想将df按照索引的特定顺序写入excel,CATEG
并且LEVEL
我想CATEG
根据另一个df来对df进行排序order
:-
sl set
1 C
2 B
3 A
4 D
并LEVEL
按此顺序:- PG AB CD
。此顺序对于所有dfe
df都不会更改并保持不变,但是CATEG
更改和order
仅需根据df进行设置。
Apple Orange
CATEG PG AB CD PG AB CD
C
B
A
D
我尝试了很多事情,但错过了一些东西,因为dfe
在写excel时被转换为不同的顺序:-
df= pd.merge(df,order[['sl','set']].rename({'set':'CATEG'}, axis=1), how='left',on='CATEG')
df = df.sort_values(['sl']).drop('sl', axis=1)
with pd.ExcelWriter('file.xlsx',options={'nan_inf_to_errors': True}) as writer :
df.groupby('ID').apply(lambda x: x.dropna(how='all', axis=1).to_excel(writer,sheet_name=str(x.name),na_rep=0,index=True))
writer.save()
即使更改了它的解决方法,它也会按自己的顺序打印?我也无法将scientifc格式写为数字,即使尝试了诸如float_format="%.0f"
或转换为object
or之类的各种方法后pd.set_option('display.float_format', lambda x: '%.0f' % x)
,是否也有解决方法,我必须在excel中手动更改数字格式以获取几列的数字
您可以通过按所需顺序合并数据帧中的列来对值进行排序,然后在以后删除这些列。然后,创建一个数据透视表,注意您要设置为index
和columns
的顺序-否则,如果顺序s1
和s2
列不正确,则可能会弄乱顺序。
df1 = pd.DataFrame({'s1': {0: 1, 1: 2, 2: 3, 3: 4}, 'set': {0: 'C', 1: 'B', 2: 'A', 3: 'D'}})
df2 = pd.DataFrame({'s2': {0: 1, 1: 2, 2: 3}, 'LEVEL': {0: 'PG', 1: 'AB', 2: 'CD'}})
dfe = (dfe[['ID','CATEG','LEVEL','COLS','VALUE']]
.merge(df1.rename({'set' : 'CATEG'}, axis=1),how='left',on='CATEG')
.merge(df2, how='left', on='LEVEL'))
dfe = pd.pivot_table(dfe,index=['ID','s1','CATEG'],columns=['COLS','s2','LEVEL'],values=['VALUE'])
dfe.columns = dfe.columns.droplevel([0,2])
dfe = dfe.reset_index().drop(('s1',''), axis=1).set_index('CATEG')
dfe
Out[1]:
COLS ID Apple Orange
LEVEL PG AB CD PG AB CD
CATEG
C 1 226.0 884.0 288.0 NaN NaN NaN
B 1 712.0 849.0 NaN NaN NaN NaN
A 1 428.0 NaN 175.0 NaN NaN NaN
B 2 NaN NaN 376.0 NaN NaN 135.0
A 2 NaN NaN NaN NaN 1.000000e+13 NaN
D 2 NaN NaN NaN 1.000000e+15 NaN NaN
或不在ID
透视表中(只需更改下面的代码行,并通过此更改在上面的代码运行):
dfe = pd.pivot_table(dfe,index=['s1','CATEG'],columns=['COLS','s2','LEVEL'],values=['VALUE'])
COLS Apple Orange
LEVEL PG AB CD PG AB CD
CATEG
C 226.0 884.0 288.0 NaN NaN NaN
B 712.0 849.0 376.0 NaN NaN 135.0
A 428.0 NaN 175.0 NaN 1.000000e+13 NaN
D NaN NaN NaN 1.000000e+15 NaN NaN
然后,只需使用:
dfe.to_excel('test.xlsx')
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句