{{tag>python pandas code}}

====== 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:


<code>
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},
...
]

</code>

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:

<code python>
    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)
        )

</code>

This will export it to csv file in reports directory.
====== Tested on ======
  * Python 3.8.10
  * pandas==1.4.3

====== See also ======

====== References ======
  * https://stackoverflow.com/a/40397109/6881647
  * https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html