User Tools

Site Tools


Python pandas dictionary grouping

If you have a list of dictionaries that are going to be exported to a csv file and the items need to be divided in groups and subgroups use the snippet below.

Example list of dictionaries:

example_dicts = [
{'RetailerId': 1234, 'Retailer': 'Cool retailer 1', 'custom Retailer': 'no', 'Tag': '', 'Subtag': 'accessoires', 'Click Costs': 0.2, 'Total Costs': 'N/A', 'Brand': 1},
 {'RetailerId': 4321, 'Retailer': 'Cool retailer 1 Clothing', 'custom Retailer': 'no', 'Tag': 'hoodies & sweatshirts', 'Subtag': 'clothing', 'Totals', 'Total Costs': 'N/A', 'Brand': 1},
 {'RetailerId': 2222, 'Retailer': 'Cool retailer 2', 'custom Retailer': 'no', 'Tag': 't-shirts', 'Subtag': 'clothing', 'Totals', 'Total Costs': 'N/A', 'Brand': 1},
 {'RetailerId': 9999, 'Retailer': 'Cool retailer 3', 'custom Retailer': 'no', 'Tag': '', 'Subtag': 'clothing', 'Totals', 'Total Costs': 'N/A', 'Brand': 1},

Now to group by certain keys and count all of the identical rows and put the count in “Totals” column, but leave the rest of the columns as is you can use snippet below:

    rets = (pd.DataFrame(example_dicts)
        .groupby(["Retailer", "Brand", "Tag", "Subtag"], as_index=False)
        .agg({"Totals": "count", "Totals Costs": "first", "RetailerId": "first", "custom Retailer": "first"})
        .to_csv('reports/report.csv', index=False)

This will export it to csv file in reports directory.

Tested on

  • Python 3.8.10
  • pandas==1.4.3

See also


wiki/python_pandas_dictionary_grouping.txt · Last modified: 2022/06/29 13:09 by antisa

Except where otherwise noted, content on this wiki is licensed under the following license: CC0 1.0 Universal
CC0 1.0 Universal Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki