python excel按列名选取多列_Python-按多列分组并获得最大或总和

I am interested in getting max value of the Product price.

Here is the input data.Download Sales Data

I want to group by State,Country

How do I go about grouping these two columns get the max value of Price.

import csv

import locale

from itertools import groupby

locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' )

total_price = 0

max_price = 0

reader = csv.DictReader(open('/Users/myuser/Downloads/SalesData.csv', 'rU'), dialect='excel')

groups = groupby(reader, lambda d: d['State'])

result = [max(g, key=lambda d: d['State']) for k, g in groups]

for row in reader:

print row["State"], row["Country"], locale.atoi(row["Price"])

max_price = max(row.iteritems(), key=operator.itemgetter(1))

total_price += locale.atoi(row["Price"])

With use of panda's. Can I get this without using pandas.

import pandas as pd

from pandas import DataFrame

import locale

locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' )

df = pd.read_csv('/Users/myuser/Downloads/SalesData.csv', index_col=False, header=0,thousands=',')

print df.groupby(["Country","State"]).max()["Price"]

解决方案

itertools.groupby only works on lists sorted using the same key function used for groupby , as given in the documentation -

itertools.groupby(iterable[, key])

Make an iterator that returns consecutive keys and groups from the iterable. The key is a function computing a key value for each element. If not specified or is None, key defaults to an identity function and returns the element unchanged. Generally, the iterable needs to already be sorted on the same key function.

So for achieving what you want with itertools.groupby , you would most probably need to sort the data first based on both 'Country' and 'State' , and then take groupby on it.

also, when taking max() you should use 'Price' not 'State' . Example -

reader = csv.DictReader(open('/Users/myuser/Downloads/SalesData.csv', 'rU'), dialect='excel')

sortedreader = sorted(reader, key=lambda d: (d['Country'], d['State']))

groups = groupby(sortedreader, key=lambda d: (d['Country'], d['State']))

result = [(k, max(g, key=lambda d: d['Price'])) for k, g in groups]

I added the key to the result , to identify which Country/State each max corresponds to. After this you can iterate over result and print each if that is what you really want.