python - Pandas 无法在索引 0 处添加列

Question

我有一个 Pandas 数据框，转置后有 2 列。如果我导出到 Excel，第一列的格式无法由 xlsxwriter 控制(所有其他列都可以格式化)。如果我尝试在 Pandas 数据帧中的索引 0 处插入新列，它将出现在索引 1 处。为什么第一个数据帧列在 Pandas 中不可更改并且无法由 xlsxwriter 格式化？

这是我的原始代码:

data_xls = pandas.read_excel(excelPath, "FlowTimes", index_col=None)

del data_xls['OBJECTID'] #Remove OBJECTID column
del data_xls['Shape_Length'] #Remove the Shape_Length column
data_xls.drop(data_xls.columns[0], axis =1) #Remove nameless first column

decimals = pandas.Series([0,2,0,2,0,2,0,2], index=['Length FT', 'Length MI','Flow Minutes - Mean Annual','Flow Hours - Mean Annual')
data_xls2 = data_xls.round(decimals)

data_export = data_xls2.transpose() #Transpose columns to rows
data_export.reset_index()
data_export.insert(0,"Temp", None)  #THIS IS A TESTING LINE TO SEE WHERE THE COLUMN INSERTS - IT SHOULD INSERT AS THE FIRST COLUMN AT INDEX 0

print data_export
os.remove(excelPath)

writer = pandas.ExcelWriter(excelPath + "x")
data_export.to_excel(writer, sheet_name = 'FlowTimes', startcol = 0, startrow = 0, header=False) #header = false removes the 0 index in the first row
workbook = writer.book
worksheet = writer.sheets['FlowTimes']
format1 = workbook.add_format()
format1.set_align('right')
format1.set_border(0)

format2 = workbook.add_format()
format2.set_bold()

worksheet.set_column("A:A", 30, format1)
worksheet.set_column("B:Z", 12, format1)
worksheet.set_row(0,None, format2)

writer.save()

如果我不包含用于测试的 data_export.insert 列，则输出如下所示:

                                    0
Key Strategy Location       Location A
Length FT                         3551
Length MI                         0.67
Flow Minutes - Mean Annual          35
Flow Hours - Mean Annual          0.58

但是，如果我在索引 0 处包含 data_export.insert 列，它将在第一列之后插入，如下所示:

                           Temp           0
Key Strategy Location       None  Location A
Length FT                   None        3551
Length MI                   None        0.67
Flow Minutes - Mean Annual  None          35
Flow Hours - Mean Annual    None        0.58

如何在使用 xlsxwriter 导出时在索引 0 处插入新列或控制第一列的格式？

Answer 1

事实证明，xlsxwriter 导入的 pandas 索引(行名称)的格式无法更改。我通过将索引复制到第一列，然后导出到不带标题或索引的 Excel 来解决此问题。所有列和行的 xlsxwriter 格式现在都可以使用。这是工作代码:

data_xls = pandas.read_excel(excelPath, "FlowTimes" + gdbNum, index_col=None)
del data_xls['OBJECTID'] #Remove OBJECTID column
del data_xls['Shape_Length'] #Remove the Shape_Length column
data_xls.drop(data_xls.columns[0], axis =1) #Remove nameless first column

decimals = pandas.Series([0,2,0,2,0,2,0,2], index=['Length FT', 'Length MI','Flow Minutes - Mean Annual','Flow Hours - Mean Annual')

data_export = data_xls2.transpose() #Transpose columns to rows
data_export.reset_index()
data_export.columns = data_export.iloc[0]
data_export['Key Strategy Location'] = data_export.index #adds index names (row names) as last column

cols = data_export.columns.tolist() #Create list of columns
cols.insert(0, cols.pop(-1)) #Move column with index names (row names) to be the first column
data_export = data_export[cols]

os.remove(excelPath)

writer = pandas.ExcelWriter(excelPath + "x")
data_export.to_excel(writer, sheet_name = 'FlowTimes', startcol = 0, startrow = 0, header=False, index=False) #header = false removes index in the first row, index=false removes first column
workbook = writer.book
worksheet = writer.sheets['FlowTimes']
format1 = workbook.add_format()
format1.set_align('right')
format1.set_border(0)

format2 = workbook.add_format()
format2.set_bold()
format2.set_align('center')

worksheet.set_column("B:Z", 20, format1)
worksheet.set_row(0,None, format2)
worksheet.set_column("A:A", 30, format1)

writer.save()