我浏览了很多帖子,但没有找到执行以下操作的确切方法。
很抱歉附加屏幕截图(只是为了更好的可见性),我也会写它。
基本上看起来像-
Name_of_the_Man Address_of_Man City
Jordan NC LMN
输入csv看起来像
需要的输出
我有这段代码,可以选择csv并附加为excel中的工作表。
writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):
df = pd.read_csv(f)
df.to_excel(writer, sheet_name=os.path.basename(f))
writer.save()
我想要我的csv文件-列标题之间有很好的空间,并且为列标题设置了颜色。我已经通过了Python - change header color of dataframe and save it to excel file链接,但它没有用-它将表本身与列着色。
更新:
得到了下面的答案。也想知道这是否可能只是一个想法
参考方案
您可以将Pandas Excel output with user defined header format与solution一起使用,以按内容更改宽度:
writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header. Also remove index values by index=False
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
'bold': True,
'fg_color': '#ffcccc',
'border': 1})
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num, value, header_format)
column_len = df[value].astype(str).str.len().max()
# Setting the length if the column header is larger
# than the max column value length
column_len = max(column_len, len(value)) + 3
print(column_len)
# set the column length
worksheet.set_column(col_num, col_num, column_len)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
更改了您的解决方案:
writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):
df = pd.read_csv(f)
df.to_excel(writer, sheet_name=os.path.basename(f))
workbook = writer.book
worksheet = writer.sheets[os.path.basename(f)]
# Add a header format.
header_format = workbook.add_format({
'bold': True,
'fg_color': '#ffcccc',
'border': 1})
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num, value, header_format)
column_len = df[value].astype(str).str.len().max()
# Setting the length if the column header is larger
# than the max column value length
column_len = max(column_len, len(value)) + 3
print(column_len)
# set the column length
worksheet.set_column(col_num, col_num, column_len)
writer.save()
编辑:
writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')
#skip 2 rows
df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
'bold': True,
'fg_color': '#ffcccc',
'border': 1})
#create dictionary for map length of columns
d = dict(zip(range(25), list(string.ascii_uppercase)))
#print (d)
max_len = d[len(df.columns) - 1]
print (max_len)
#C
#dynamically set merged columns in first row
worksheet.merge_range('A1:' + max_len + '1', 'This Sheet is for Personal Details')
for col_num, value in enumerate(df.columns.values):
#write to second row
worksheet.write(1, col_num, value, header_format)
column_len = df[value].astype(str).str.len().max()
column_len = max(column_len, len(value)) + 3
worksheet.set_column(col_num, col_num, column_len)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
R'relaimpo'软件包的Python端口 - python我需要计算Lindeman-Merenda-Gold(LMG)分数,以进行回归分析。我发现R语言的relaimpo包下有该文件。不幸的是,我对R没有任何经验。我检查了互联网,但找不到。这个程序包有python端口吗?如果不存在,是否可以通过python使用该包? python参考方案 最近,我遇到了pingouin库。
如何用'-'解析字符串到节点js本地脚本? - python我正在使用本地节点js脚本来处理字符串。我陷入了将'-'字符串解析为本地节点js脚本的问题。render.js:#! /usr/bin/env -S node -r esm let argv = require('yargs') .usage('$0 [string]') .argv; console.log(argv…
将字符串分配给numpy.zeros数组[重复] - pythonThis question already has answers here: Weird behaviour initializing a numpy array of string data (4个答案) …
Python:传递记录器是个好主意吗? - python我的Web服务器的API日志如下:started started succeeded failed 那是同时收到的两个请求。很难说哪一个成功或失败。为了彼此分离请求,我为每个请求创建了一个随机数,并将其用作记录器的名称logger = logging.getLogger(random_number) 日志变成[111] started [222] start…
Python-Excel导出 - python我有以下代码:import pandas as pd import requests from bs4 import BeautifulSoup res = requests.get("https://www.bankier.pl/gielda/notowania/akcje") soup = BeautifulSoup(res.cont…