使用Pandas DataFrame时TradePrice列求和出现异常值求助

问题详情 #

作为Python新手，我尝试将CSV数据导入Pandas DataFrame后，按Symbol和Buy/Sell分组对Quantity、TradePrice、IBCommission列求和。其他列结果正常，但TradePrice列出现了奇怪的科学计数法数值。

原代码 #

import pandas as pd

df = pd.read_csv("TradesSEK.csv")
df["IBCommission"] = abs(df["IBCommission"])

sum_df = df.groupby(["Symbol", "Buy/Sell"]).sum()[["Quantity", "TradePrice", "IBCommission"]].reset_index()

# 打印新DataFrame
print(sum_df)
# 写入CSV文件
sum_df.to_csv("SumTrades.csv", index=False)

示例数据 #

Symbol,Buy/Sell,Quantity,TradePrice,IBCommission,CurrencyPrimary,TradeDate
FDXS MAR 22,SELL,-1,164561.432,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164561.432,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164561.432,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164684.576,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164674.314,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164674.314,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164684.576,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164684.576,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164684.576,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164571.69400000002,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164571.69400000002,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164571.69400000002,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164469.074,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164469.074,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164469.074,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164366.454,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164571.69400000002,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164571.69400000002,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164171.476,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164171.476,-3.89956,SEK,2022-01-03
FDXS MAR 22,BUY,1,164171.476,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164458.812,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164684.576,-3.89956,SEK,2022-01-03
FDXS MAR 22,SELL,-1,164499.86000000002,-3.89956,SEK,2022-01-03
MESH2,BUY,1,43729.370899999994,-4.729712,SEK,2022-01-04
MESH2,BUY,1,43729.370899999994,-4.729712,SEK,2022-01-04
MESH2,SELL,-1,43688.4407,-4.729712,SEK,2022-01-04
MESH2,SELL,-1,43688.4407,-4.729712,SEK,2022-01-04
MESH2,SELL,-1,43702.08409999999,-4.729712,SEK,2022-01-04
MESH2,SELL,-1,43702.08409999999,-4.729712,SEK,2022-01-04
MESH2,BUY,1,43665.7017,-4.729712,SEK,2022-01-04
MESH2,BUY,1,43711.17969999999,-4.729712,SEK,2022-01-04
MESH2,BUY,1,43297.79,-4.715152000000001,SEK,2022-01-05
MESH2,BUY,1,43297.79,-4.715152000000001,SEK,2022-01-05
MESH2,SELL,-1,43334.0604,-4.715152000000001,SEK,2022-01-05
MESH2,SELL,-1,43340.8611,-4.715152000000001,SEK,2022-01-05

异常输出 #

Symbol Buy/Sell  Quantity    TradePrice  IBCommission
0       EUR.USD     SELL -617.1651  3.196508e+01     18.271867
1   FDXS DEC 22      BUY  110.0000  1.561461e+07    455.386870
2   FDXS DEC 22     SELL -110.0000  1.529547e+07    455.386870
3   FDXS JUN 22      BUY   24.0000  3.523879e+06     94.577592
4   FDXS JUN 22     SELL  -24.0000  3.528523e+06     94.577592
5   FDXS MAR 22      BUY  172.0000  2.690855e+07    682.416426
6   FDXS MAR 22     SELL -172.0000  2.662888e+07    682.416426
7   FDXS MAR 23      BUY    3.0000  4.688854e+05     12.682500
8   FDXS MAR 23     SELL   -3.0000  4.684181e+05     12.682500
9   FDXS SEP 22      BUY   47.0000  6.480417e+06    189.812090
11  FESX MAR 22      BUY    9.0000  3.750484e+05    119.088396
12  FESX MAR 22     SELL   -9.0000  3.748532e+05    119.088396
13  FSXE MAR 22      BUY    5.0000  1.178318e+05     18.331270
14  FSXE MAR 22     SELL   -5.0000  1.175731e+05     18.331270
15        M2KH2      BUY    3.0000  5.665107e+04     15.401632
16        M2KH2     SELL   -3.0000  5.653878e+04     15.401632
17        MESH2      BUY   63.0000  2.597867e+06    301.419600
18        MESH2     SELL  -63.0000  2.512345e+06    301.419600

解决方案 #

出现科学计数法是因为Pandas默认对较大的浮点数采用科学计数法显示，以下三种方法可以解决：

方法1：全局禁用科学计数法 #

设置Pandas全局显示格式，强制所有浮点数以普通小数形式展示，可指定保留小数位数：

import pandas as pd

# 全局设置：禁用科学计数法，保留两位小数
pd.set_option('display.float_format', lambda x: '%.2f' % x)

df = pd.read_csv("TradesSEK.csv")
df["IBCommission"] = abs(df["IBCommission"])

sum_df = df.groupby(["Symbol", "Buy/Sell"]).sum()[["Quantity", "TradePrice", "IBCommission"]].reset_index()

print(sum_df)
sum_df.to_csv("SumTrades.csv", index=False)

方法2：仅格式化指定列 #

如果不想修改全局设置，仅针对TradePrice列调整显示格式：

import pandas as pd

df = pd.read_csv("TradesSEK.csv")
df["IBCommission"] = abs(df["IBCommission"])

sum_df = df.groupby(["Symbol", "Buy/Sell"]).sum()[["Quantity", "TradePrice", "IBCommission"]].reset_index()

# 将TradePrice列格式化为保留两位小数的字符串
sum_df['TradePrice'] = sum_df['TradePrice'].apply(lambda x: '%.2f' % x)

print(sum_df)
sum_df.to_csv("SumTrades.csv", index=False)

方法3：导出CSV时指定格式 #

如果只是导出CSV文件时不想出现科学计数法，可以在to_csv方法中直接设置浮点格式：

import pandas as pd

df = pd.read_csv("TradesSEK.csv")
df["IBCommission"] = abs(df["IBCommission"])

sum_df = df.groupby(["Symbol", "Buy/Sell"]).sum()[["Quantity", "TradePrice", "IBCommission"]].reset_index()

print(sum_df)
# 导出CSV时设置所有浮点数保留两位小数
sum_df.to_csv("SumTrades.csv", index=False, float_format='%.2f')

内容的提问来源于stack exchange，提问作者Pontus Mathisen