用数据框写一个列表到新的excel xlsx [英] Writing a list to new excel xlsx with dataframes

问题描述

我遇到了一些麻烦，找到写入列表到加载的Excel表格的最佳方法，然后将结果保存为xlsx。我想让我的代码做的是把我创建的列表，并把它们放在加载工作簿的特定列。我认为有可能使用数据框更简单的方法，但我不知道如何。理想情况下，我想保存加载的工作簿的格式。

  col_test = [1L，2L，3L，4L，5L ] 
  

我的代码是这个

<$ p $从$ x导入xlrt
导入open_workbook
rb = open_workbook（Excel FDT Master_01_update.xlsx）
s = rb.sheet_by_name（'INPUT'）$ b $在col_test中测试：
s.cell（row = r，column = 1）.value = test
r + = 1
rb.save（'didthiswork。 xlsx'）

解决方案

这是一个没有额外安装的版本在水蟒之上。
这不是保持样式，但你可以修复复制/'粘贴值'回到原来的xlsx。

大多数excel操纵器有保留问题原始文件完好无损。也有办法，但是如果你想要它防水，你基本上最终为你提供了一个特定的解决方案，或多或少重新编码所有的库，所以这是不值得的努力。 b
$ b熊猫在扩展现有数据框的时候可能有点棘手，但是总有几种方法可以做到这一点。这里是用assign来完成的，所以只需要确保数据框的rowcount足够长就可以添加。

  import pandas as pd 
 
＃read the excel 
 df = pd.read_excel（'Geen titel 1.xlsx'）＃有选项可以选择表单
 print（'原来的df'）
 print（df）
＃你的数据是无效的Python语法，所以我们假设它是字符串
 col_test = ['1L'，'2L'，'3L'，'4L '，'5L'] 
 new_idx = range（max（len（col_test），len（df.index）））
 df = df.reindex（new_idx）＃现在它将适应不同的长度
 print（'reindexed df'）
 print（df）
 df = df.assign（new_col = col_test）＃在右侧添加新列
 print（'modified df'）
 print（df）
 df.to_excel（'the_new.xlsx'）
  

打印输出：

 原始df 
ab 
 0 1 c 
 1 2 d 
 2 3 e 
重新索引df 
ab 
 0 1.0 c 
 1 2.0 d 
 2 3.0 e 
 3 NaN NaN 
 4 NaN NaN 
修改过的df 
ab new_col 
 0 1.0 c 1L 
 1 2.0 d 2L 
 2 3.0 e 3L 
 3 NaN NaN 4L 
 4 NaN NaN 5L 
  

I am having some trouble finding the best way to write a list to a loaded excel sheet and then saving the result as an xlsx. What I want my code to do is to take the lists that I have created and put them in specific columns of the loaded workbook. I think there is probably an easier way to do this using dataframes, but I do not know how. Ideally, I would like to save the formatting of the loaded workbook.

col_test = [1L, 2L, 3L, 4L, 5L]

My code is this

import xlrt
from xlrd import open_workbook
rb = open_workbook("Excel FDT Master_01_update.xlsx")
s = rb.sheet_by_name('INPUT')
r = 5
for test in col_test:
    s.cell(row = r, column = 1).value = test
    r += 1
rb.save('didthiswork.xlsx')

解决方案

Here is one version with no extra installs on top of anaconda. It is not keeping styling, but that you can fix with a copy/'paste values' back to original xlsx.

Most excel manipulators have issues with keeping the original file intact. There are ways around that as well, but if you want it to be waterproof, you basically end up with a specific solution for you, or more or less recode all libraries out there, so it's not worth the effort.

Pandas can be a bit tricky to get right when extending existing dataframes, but there are always several alternative ways to do it. Here it's done with assign, so then one only needs to make sure that the dataframe's rowcount is long enough for what one wants to add.

import pandas as pd

# read the excel
df = pd.read_excel('Geen titel 1.xlsx') # there are options to choose sheet
print('original df')
print(df)
# your data is not valid python syntax so let's assume it's strings
col_test = ['1L', '2L', '3L', '4L', '5L']
new_idx = range(max(len(col_test), len(df.index)))
df = df.reindex(new_idx) # now it will accommodate different lengths
print('reindexed df')
print(df)
df = df.assign(new_col=col_test) # new column added at right side
print('modified df')
print(df)
df.to_excel('the_new.xlsx')

The printouts:

original df
   a  b
0  1  c
1  2  d
2  3  e
reindexed df
     a    b
0  1.0    c
1  2.0    d
2  3.0    e
3  NaN  NaN
4  NaN  NaN
modified df
     a    b new_col
0  1.0    c      1L
1  2.0    d      2L
2  3.0    e      3L
3  NaN  NaN      4L
4  NaN  NaN      5L

这篇关于用数据框写一个列表到新的excel xlsx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！