How can one sort a list of dictionaries into an excel sheet such that each unique key and one corresponding key are placed into columns

0 votes

I have a list of dictionaries organized as such:

listofdictionaries = [{'key1': (A, B, C), 'key2':[1, 2, 3]}, 
                      {'key1': (AA, BB, CC), 'key2':[1, 2, 3]}, 
                      {'key1': (AAA, BBB, CCC), 'key2':[4, 5, 6]}]

The first and second items on this list have the same value for the key2. A different value for key2 is present in the third item. I want the columns arranged as follows using Python:

Group 1 Group 1 Items Group 2 Group 2 Items
[1, 2, 3] (A, B, C) [4, 5, 6] (AAA, BBB, CCC)
(AA, BB, CC)

In addition, I would like the output to be a .csv file.

Feb 6 in Others by Kithuzzz
• 34,760 points

1 answer to this question.

0 votes

With pandas, you can use something like this function

def groupItems(dictList, itemsFrom, groupBy, saveTo=None):
    ik, gk, colsDict = itemsFrom, groupBy, {}
    groups = {str(d.get(gk)): d.get(gk) for d in dictList} 
    itemsList = [ [d.get(ik) for d in dictList if str(d.get(gk))==g] 
                  for g in groups   ]

    maxRows = max(len(li) for li in itemsList) if groups else 0
    for gi, (g, li) in enumerate(zip(groups.keys(), itemsList), 1):
        colsDict[f'Group {gi}'] = [groups[g]] + [None]*(maxRows-1)
        colsDict[f'Group {gi} Items'] = li + [None]*(maxRows-len(li))
    rdf = pandas.DataFrame(colsDict)
    if saveTo and isinstance(saveTo, str):
        print('Saving', maxRows, 'rows for', len(groups),'groups to', saveTo)
        rdf.to_csv(saveTo, index=False)
    return rdf

Calling groupItems(listofdictionaries, 'key1', 'key2', 'x.csv') will save the DataFrame from the screenshot below to x.csv.

dfop1 To demonstrate that the brackets were not lost: opmd1

answered Feb 6 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?


answered Nov 11, 2022 in Others by narikkadan
• 59,740 points
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,458 views
0 votes
1 answer
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18 in Others by narikkadan
• 59,740 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP