Search code examples
pythonpandasexcel

How to add the data in the same row index but in different cells in each column

I got a dictionary list:

data_list = [
{"ID":id},
{"HomeOwnersDwellingCoverageALimitof":HomeOwnersDwellingCoverageALimitof},
{"HomeOwnersPersonalPropetyReplacemntCostOpt":HomeOwnersPersonalPropetyReplacemntCostOpt},
{"HomeOwnersLossOfUserCoverageDeduc":HomeOwnersLossOfUserCoverageDeduc},
{"HomeOwnersOtherStructuresCoverageBLimit":HomeOwnersOtherStructuresCoverageBLimit},
{"HomeOwnersPersonalpropety":HomeOwnersPersonalpropety},
{"HomeOwnersLossOfUserCoverageD":HomeOwnersLossOfUserCoverageD},
{"HomeOwnersPersonalLiabilityCovarageE":HomeOwnersPersonalLiabilityCovarageE},
{"HomeOwnersMedicalPayments":HomeOwnersMedicalPayments},
{"HomeOwnersOtherStructuresCoverageBLimitDeduc":HomeOwnersOtherStructuresCoverageBLimitDeduc},
{"HomeOwnersPersonalpropetyDeduc":HomeOwnersPersonalpropetyDeduc},
{"HomeOwnersPersonalLiabilityCovarageEDeduc":HomeOwnersPersonalLiabilityCovarageEDeduc},
{"HomeOwnersMedicalPaymentsDeduc":HomeOwnersMedicalPaymentsDeduc},
{"HomeOwnersFullValueReplacementCostOpt":HomeOwnersFullValueReplacementCostOpt},
{"HomeOwnersInflationGuardOpt":HomeOwnersInflationGuardOpt},
{"HomeOwnersLossAssessmentOpt":HomeOwnersLossAssessmentOpt},
{"HomeOwnersUnscheduledJewerlyOpt":HomeOwnersUnscheduledJewerlyOpt},
{"HomeOwnersServiceLineOpt":HomeOwnersServiceLineOpt},
{"HomeOwnersIdentityFreaudExpenseOpt":HomeOwnersIdentityFreaudExpenseOpt},
{"HomeOwnersMechanicalBreakdownOpt":HomeOwnersMechanicalBreakdownOpt},
{"HomeOwnersBuildingOrdinanceOpt":HomeOwnersBuildingOrdinanceOpt},
{"HomeOwnersFullValueReplacementCostLimit":HomeOwnersFullValueReplacementCostLimit},
{"HomeOwnersPersonalPropetyReplacemntCostLimit":HomeOwnersPersonalPropetyReplacemntCostLimit},
{"HomeOwnersInflationGuardLimit":HomeOwnersInflationGuardLimit},
{"HomeOwnersLossAssessmentLimit":HomeOwnersLossAssessmentLimit},
{"HomeOwnersUnscheduledJewerlyLimit":HomeOwnersUnscheduledJewerlyLimit},
{"HomeOwnersServiceLineLimit":HomeOwnersServiceLineLimit},
{"HomeOwnersIdentityFreaudExpenseLimit":HomeOwnersIdentityFreaudExpenseLimit},
{"HomeOwnersMechanicalBreakdownLimit":HomeOwnersMechanicalBreakdownLimit},
{"HomeOwnersBuildingOrdinanceLimit":HomeOwnersBuildingOrdinanceLimit},
{"HomeOwnersFullValueReplacementCostDeduc": HomeOwnersFullValueReplacementCostDeduc},
{"HomeOwnersPersonalPropetyReplacemntCostDeduc": HomeOwnersPersonalPropetyReplacemntCostDeduc},
{"HomeOwnersInflationGuardDeduc": HomeOwnersInflationGuardDeduc},]

I added this to an Excel file, the problem is the data is not below each column, is added with one row down I will share a screenshot of the Excel:

Actual result - Excel file

For this method, I use this line of code:

df = pd.DataFrame(data_list)
name_exel = 'HomeOwners_results.xlsx'

df.to_excel(name_exel,index=False, header=True,startrow=0,startcol=0)

I tried other solutions like:

df = pd.DataFrame(columns=['Column'])


for item in data_list:
    for key, value in item.items():
        df = df.append({'Column': value}, ignore_index=True)

name_excel = 'HomeOwners_results.xlsx'
df.to_excel(name_excel, index=False, header=False)

print(df)

But now the date is added in column A and in the rows for 1 to 10 or to how much data I got in the list.

And I am out of ideas on how to that to get the data like:

Expected result

Solution

  • Use nested dictionary comprehension with DataFrame constructor:

    df = pd.DataFrame([{k: v for d in data_list for k, v in d.items()}])

    Or ChainMap:

    from collections import ChainMap

df = pd.DataFrame([ChainMap(*data_list)])

    df.to_excel(name_exel,index=False, header=True,startrow=0,startcol=0)