0

I have a list of dictionaries with the following keys: country, points, price. I need to get an average of points and price for each country. Here is the list: 

0: {country: "US", points: 96, price: 235}
1: {country: "Spain", points: 96, price: 110}
2: {country: "US", points: 96, price: 90}
3: {country: "US", points: 96, price: 65}

And I need a list of dictionaries back with country and their averages.

I have gotten to a point where I have a list of dictionaries with the sum of price and points:

[{'country': 'Albania', 'points': 176, 'price': 40.0}, {'country': 'Argentina', 'points': 480488, 'price': 116181.0}, {'country': 'Australia', 'points': 430092, 'price': 152979.0}

Now I need to get averages. I was thinking to create another key for country.length and then in the for loop perform a basic calculation. But not sure if this is the right approach... Thanks for help!

My code below:

count_dict = country_count.to_dict()

# Output
{'US': 62139,
 'Italy': 18784,
 'France': 14785,
 'Spain': 8160} 

# Get the sum of points and price for each country
grouped_data = wine_data.groupby('country').agg({'points':'sum', 'price':'sum'})

# Reset the index in order to convert df into a list of dictionaries
country_data = grouped_data.reset_index()
country_list = country_data.to_dict('records')

# Output
[{'country': 'Albania', 'points': 176, 'price': 40.0}, {'country': 'Argentina', 'points': 48048 etc]```
Chique_Code
  • 1,422
  • 3
  • 23
  • 49
  • You mean you have a nested dictionary? – yatu Aug 28 '19 at 13:45
  • Show us your code so far... – match Aug 28 '19 at 13:49
  • @match `country_count = wine_data['country'].value_counts() count_dict = country_count.to_dict() # Output {'US': 62139, 'Italy': 18784, 'France': 14785, 'Spain': 8160} # Get the sum of points and price for each country grouped_data = wine_data.groupby('country').agg({'points':'sum', 'price':'sum'}) # Reset the index in order to convert df into a list of dictionaries country_data = grouped_data.reset_index() country_list = country_data.to_dict('records') # Output [{'country': 'Albania', 'points': 176, 'price': 40.0}, {'country': 'Argentina', 'points': 48048 etc]` – Chique_Code Aug 28 '19 at 14:06
  • Where you use the agg method on grouped wine_data, you can use mean directly in place of sum: wine_data.groupby('country').agg({'points':'mean', 'price':'mean'}) – Charles Gleason Aug 28 '19 at 14:18
  • @CharlesGleason I have overcomplicated my code. Your approach worked just exactly how I needed it. Thank you very much! – Chique_Code Aug 28 '19 at 14:53

1 Answers1

0

Have you tried passing your data into a Pandas DataFrame and work with it there?

You can do it as follows, first to make a DataFrame:

import pandas as pd
import numpy as np

d = {
    0: {'country': "US", 'points': 96, 'price': 235},
    1: {'country': "Spain", 'points': 96, 'price': 110},
    2: {'country': "US", 'points': 96, 'price': 90},
    3: {'country': "US", 'points': 96, 'price': 65}
}

#
df = pd.DataFrame(d).Transpose()

Out:
    country points  price
0   US      96      235
1   Spain   96      110
2   US      96      90
3   US      96      65

Then groupby country

# just to make sure they are numeric
df[['points','price']] = df[['points','price']].astype('float64')

df.groupby('country').mean()

Out:
        points  price
country     
Spain   96.0    110.0
US      96.0    130.0
stahamtan
  • 848
  • 6
  • 10
  • My code is in Pandas df. I have edited my code in the body. I am struggling with putting one dictionary into a list of dictionaries now. – Chique_Code Aug 28 '19 at 14:13