将openpyxl数据传递给Pandas

Question

我将从excel文件中的数据将“全名”字段拆分为“名字”、“中间名”和“姓氏”字段。我不知道如何在熊猫身上做到这一点，所以我转向了openpyxl。我按我的要求把变量分开了。但是，since adding columns to openpyxl for the new fields is not easy，我想我会把这些值传递给熊猫。

我正在生成运行代码时所需的数据帧，但一旦将df发送到ExcelWriter，只有最后一行被添加到Excel文件中。不过，数据放在正确的位置。

代码如下：

for cellObj in range(2, sheet.max_row+1):
    #print cellObj
    id = sheet['A' + str(cellObj)].value
    fullname = sheet['B' + str(cellObj)].value.strip()
    namelist = fullname.split(' ')  
    for i in namelist:
        firstname = namelist[0]
        if len(namelist) == 2:
            lastname = namelist[1]
            middlename = ''
        elif len(namelist) == 3:
            middlename = namelist[1]
            lastname = namelist[2]
        elif len(namelist) == 4:
            middlename = namelist[1]
            lastname = namelist[2] + " " + namelist[3]
        if (namelist[1] == 'Del') | (namelist[1] == 'El') | (namelist[1] == 'Van'):
            middlename = ''
            lastname = namelist[1] + " " + namelist[2]
    df = pd.DataFrame({'personID':id,'lastName':lastname,'firstName':firstname,'middleName':middlename}, index=[id])

    writer = pd.ExcelWriter('output.xlsx')
    df.to_excel(writer,'Sheet1', columns=['ID','lastName','firstName','middleName'])
    writer.save()

有什么想法吗？

谢谢

Answer 1

有几件事。首先，您的代码只能得到一行，因为每次通过if测试时都会覆盖这些值。例如

  if len(namelist) == 2:
        lastname = namelist[1]

这将为变量lastname分配一个字符串。您没有追加到列表，您只是分配了一个字符串。当你制作数据帧时， df = pd.DataFrame({'personID':id,'lastName':lastname,...使用此值，因此数据帧将只保存该字符串。有道理？如果必须使用openpyexcel执行此操作，请尝试以下操作：

lastname = [] #create an empty list
if len(namelist) == 2:
    lastname.append(namelist[1]) #add the name to the list

不过，我认为如果你能想出如何处理熊猫的方法，你的生活最终会容易得多。其实很容易。试试这样的：

import pandas as pd
#read excel
df = pd.read_excel('myInputFilename.xlsx', encoding = 'utf8')
#write to excel
df.to_excel('MyOutputFile.xlsx')

Answer 2

FWIW openpyxl 2.4使得将Excel工作表的全部或部分转换为Pandas数据框变得非常容易：ws.values是工作表中所有值的迭代器。它还有一个新的ws.iter_cols()方法，允许您直接处理列。

它目前（2016年4月）作为alpha版本提供，可以使用pip install -U --pre openpyxl安装

代码看起来有点像这样：

sheet["B1"] = "firstName"
sheet["C1"] = "middleName"
sheet["D1"] = "lastName"

for row in sheet.iter_rows(min_row=2, max_col=2):
    id_cell, name = row

    fullname = name.value.strip()
    namelist = fullname.split()
    firstname = namelist[0]
    lastname = namelist[-1]
    middlename = ""
    if len(namelist) >= 3:
        middlename = namelist[1]
    if len(namelist) == 4:
        lastname = " ".join(namelist[-2:])
    if middlename in ('Del', 'El', 'Van', 'Da'):
        lastname = " ".join([middlename, lastname])
        middlename = None

    name.value = firstname
    name.offset(column=1).value = middlename
    name.offset(column=2).value = lastname

wb.save("output.xlsx")