CODEWITHSUNDEEP
pythonlist
Yujing Xu
Yujing Xu

Reputation: 1

How to group nested list by first element of the nested list?

I have a list of data with name, year, amount and result. I want to calculate the total won and the total loss of each year. (Preferably using list functions for beginner) Thank you

I have tried using dictionay, but it seems adding a lot of complexity and have been showing error all the time.

>>> my_list = [ ['a', '2013', '10.22', 'won'], ['b', '2012', '11.23', 'won'], ['c', '2013', '12.62', 'lost']]
>>> headers = ['name', 'year', 'amount', 'result']
>>> my_dict = {k: [x [i] for x in my_list] for i, k in enumerate(headers)}

How to get the total amount won and total amount lost

I expect the return to be in the format of 

Year    Total Won  Total Lost
2012    11.23      0
2013    10.22      12.62

Upvotes: 0

Views: 172

Answers (3)

Chris Charley
Chris Charley

Reputation: 6598

Instead of a dictionary comprehension, (your approach), I would suggest writing some code. This solution does what you are looking for.

from collections import defaultdict
d = defaultdict(dict)

my_list = [ ['a', '2013', '10.22', 'won'], ['b', '2012', '11.23', 'won'], ['c', '2013', '12.62', 'lost']]

for rec in my_list:
    if rec[3] in d[rec[1]]:
        d[rec[1]][rec[3]] += float(rec[2])
    else:
        d[rec[1]][rec[3]] = float(rec[2])

print('Year', "won", "    lost")
for year in sorted(d):
    print(year, '\t'.join([str(d[year].get('won', '0')), \
                           str(d[year].get('lost', '0'))]))

This prints:

Year won     lost
2012 11.23  0
2013 10.22  12.62

Upvotes: 1

Cohan
Cohan

Reputation: 4544

If you're comfortable enough with using pandas, you could crate a pivot table on the data. I'm assuming the resulting table is what you intended to show.

import pandas as pd

headers = ['name', 'year', 'amount', 'result']
my_list = [ ['a', '2013', '10.22', 'won'],
            ['b', '2012', '11.23', 'won'], 
            ['c', '2013', '12.62', 'lost']]

df = pd.DataFrame(my_list, columns=headers)
df.amount = pd.to_numeric(df.amount) # makes amount numeric

df2 = pd.pivot_table(df, index='year', columns='result', values='amount', aggfunc=sum)
# result   lost    won
# year                
# 2012      NaN  11.23
# 2013    12.62  10.22

To change NaN to 0

df2.fillna(0, inplace=True)

From there you can have fun and do some more good stuff like calculate the net change.

df2['net'] = df2.won - df2.lost
# result   lost    won    net
# year                       
# 2012     0.00  11.23  11.23
# 2013    12.62  10.22  -2.40

Upvotes: 1

benvc
benvc

Reputation: 15130

Assuming your expected output for 2012 is a typo (and you meant to show 11.23 as the total won as indicated by your dataset), you could use itertools.groupby and sum to summarize the total won / lost by year. You can modify the output format as needed, but this should get you going.

from itertools import groupby
from operator import itemgetter

results = [['a', '2013', '10.22', 'won'], ['b', '2012', '11.23', 'won'], ['c', '2013', '12.62', 'lost']]
for year, values in groupby(sorted(results, key=itemgetter(1)), key=itemgetter(1)):
    values = list(values)
    won = sum(float(v[2]) for v in values if v[3] == 'won')
    lost = sum(float(v[2]) for v in values if v[3] == 'lost')
    print(f'Year: {year} Total Won: {won} Total Lost: {lost}')

# Year: 2012 Total Won: 11.23 Total Lost: 0
# Year: 2013 Total Won: 10.22 Total Lost: 12.62

Upvotes: 1

Related Questions