Python 3 - 使用熊猫从构建的字典编写到 excel

Question

你好，Pythonic爱好者。

我遇到了一个非常有趣的小问题，由于缺乏经验，我无法解决。我正在根据图形数据库中的一组答案在python中构建字典，我遇到了一个有趣的困境。(我正在运行Python 3

当一切都说完了，我在我的 excel 文件中收到以下示例输出(这是从第 0 列开始的，每个条目都是一行：

实际的 EXCEL 格式：

0/{'RecordNo': 0}
1/{'Dept': 'DeptName'}
2/{'Option 1': 'Option1Value'}
3/{'Option 2': 'Option2Value'}
4/{'Question1': 'Answer1'}
5/{'Question2': 'Answer2'}
6/{'Question3': 'Answer3'}

等。。

预期的 EXCEL 格式：

0/Dept, Option 1, Option 2, Question 1, Question 2, Question 3
1/DeptName, Option1Value, Option2Value, Answer1, Answer2, Answer3

字典的键应该是标题和值，每一行的内容，但由于某种原因，当我使用以下输出代码时，它将其写出为键和值：

EXCEL 编写器代码：

ReportDF = pd.DataFrame.from_dict(DomainDict)
WriteMe = pd.ExcelWriter('Filname.xlsx')
ReportDF.to_excel(WriteMe, 'Sheet1')
try:
WriteMe.save()
print('Save completed')
except:
print('Error in saving file')

为了构建字典，我使用以下代码： EDIT(删除了字典条目的子添加，因为它是相同的，一旦主要工作，将简化为函数调用(。

字典准备代码：

for Dept in Depts:
ABBR = Dept['dept.ABBR']
#print('Department: ' + ABBR)
Forests = getForestDomains(Quarter,ABBR)
for Forest in Forests:
DictEntryList = []
DictEntryList.append({'RecordNo': DomainCount})
DictEntryList.append({'Dept': ABBR})
ForestName = Forest['d.DomainName']
DictEntryList.append({'Forest ': ForestName})
DictEntryList.append({'Domain': ''})
AnswerEntryList = []
QList = getApplicableQuestions(str(SA))
for Question in QList:
FAnswer = ''
QDesc = Question['Question']
AnswerResult = getAnswerOfQuestionForDomainForQuarter(QDesc, ForestName, Quarter)
if AnswerResult:
for A in AnswerResult:
if(str(A['Answer']) != 'None'):
if(isinstance(A, numbers.Number)):    
FAnswer = str(int(A['Answer']))
else:
FAnswer = str(A['Answer'])
else:
FAnswer = 'Unknown'
else:
print('GOBBLEGOBBLE')
FAnswer = 'Not recorded'
AnswerEntryList.append({QDesc: FAnswer})
for Entry in AnswerEntryList:
DictEntryList.append(Entry)
DomainDict[DomainCount] = DictEntryList
DomainCount+= 1
print('Ready to export')

如果有人可以帮助我将数据导出为excel中的正确格式，将不胜感激。

编辑： 打印要导出到 excel 的最终词典：

{0: [{'RecordNo': 0}, {'Dept': 'Clothing'}, {'Forest ': 'my.forest'}, {'Domain': 'my.domain'}, {'Question1': 'Answer1'}, {'Question2': 'Answer2'}, {'Question3': 'Answer3'}], 1: [{...}]}

Answer 1

写入 Excel 的问题是由于最终字典中的值是字典本身的列表，因此您可能想要仔细查看如何构建字典。在当前格式中，将最终字典传递给pd.DataFrame.from_dict会产生如下所示的数据帧：

#                             0
# 0            {u'RecordNo': 0}
# 1      {u'Dept': u'Clothing'}
# 2  {u'Forest ': u'my.forest'}
# 3   {u'Domain': u'my.domain'}
# 4  {u'Question1': u'Answer1'}
# 5  {u'Question2': u'Answer2'}
# 6  {u'Question3': u'Answer3'}

因此，数据帧行中的每个值本身就是一个字典。若要解决此问题，可以在将内部字典传递到数据帧之前，在最终字典中展平/合并内部字典：

modified_dict = {k:{x.keys()[0]:x.values()[0] for x in v} for k, v in final_dict.iteritems()}
# {0: {'Domain': 'my.domain', 'RecordNo': 0, 'Dept': 'Clothing', 'Question1': 'Answer1', 'Question3': 'Answer3', 'Question2': 'Answer2', 'Forest ': 'my.forest'}}

然后，您可以将此字典传递到 Pandas 对象中，并使用附加参数orient=index(以便数据帧使用内部字典中的键作为列(来获取如下所示的数据帧：

ReportDF = pd.DataFrame.from_dict(modified_dict, orient='index')
#       Domain  RecordNo      Dept Question1 Question3 Question2    Forest 
# 0  my.domain         0  Clothing   Answer1   Answer3   Answer2  my.forest

从那里，您可以按照指示写入Excel。

编辑：如果没有示例数据，我无法对此进行测试，但从外观上看，您可以通过构建字典而不是字典列表来简化字典准备。

for Dept in Depts:
ABBR = Dept['dept.ABBR']
Forests = getForestDomains(Quarter,ABBR)
for Forest in Forests:
DictEntry = {}
DictEntry['RecordNo'] = DomainCount
DictEntry['Dept'] = ABBR
DictEntry['Forest '] = Forest['d.DomainName']
DictEntry['Domain'] = ''
QList = getApplicableQuestions(str(SA))
for Question in QList:
# save yourself a line of code and make 'Not recorded' the default value
FAnswer = 'Not recorded'
QDesc = Question['Question']
AnswerResult = getAnswerOfQuestionForDomainForQuarter(QDesc, ForestName, Quarter)
if AnswerResult:
for A in AnswerResult:
# don't convert None to string and then test for inequality to 'None'
# if statements evaluate None as False already
if A['Answer']:
if isinstance(A, numbers.Number):    
FAnswer = str(int(A['Answer']))
else:
FAnswer = str(A['Answer'])
else:
FAnswer = 'Unknown'
else:
print('GOBBLEGOBBLE')
DictEntry[QDesc] = FAnswer
DomainDict[DomainCount] = DictEntry
DomainCount += 1
print('Ready to export')