python3使用pandas从构建的字典编写到excel

Question

你好，你们这些Python爱好者。在

我遇到了一个很有趣的小问题，由于缺乏经验，我一直未能解决。我正在根据一个图形数据库中的一组答案用python构建一个字典，我遇到了一个有趣的困境。（我在运行python3

当一切就绪后，我在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格式：

^{pr2}$

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

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')

为了构建字典，我使用以下代码： 编辑（删除了字典条目的子添加，因为它是相同的，一旦主程序运行，它将被简化为一个函数调用）。在

字典准备代码：

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'}

因此，DataFrame行中的每个值本身都是dict。要解决此问题，可以在将内部字典传递到DataFrame之前将其展平/合并到最终dict中：

^{pr2}$

然后，您可以将这个dict传递到Pandas对象中，并使用附加参数orient=index（以便DataFrame使用内部dicts中的键作为列），以获得如下所示的数据帧：

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。在

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

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')