How to get rows with the max value by using Python?
<p>I have R code to use data table to merger the rows with same FirstName and LastName but selecting the max value for specified columns(e.g. Score1, Score2, Score3). The input/output is as follows:</p>

<p><Strong>Input:</Strong></p>

<pre>
FirstName LastName Score1 Score2 Score3
fn1       ln1      41      88     50
fn1       ln1      72      66     77
fn1       ln1      69      72     90
fn2       ln2      80      81     73
fn2       ln2      59      91     66
fn3       ln3      75      80     66
</pre>

<p><Strong>Output:</Strong></p>

<pre>
FirstName LastName Score1 Score2 Score3
fn1       ln1      72      88     90
fn2       ln2      80      91     73
fn3       ln3      75      80     66
</pre>

<p>Now I want to migrate the R program to Spark. How can I do this by using Python?</p>

<p>Here is the way to do it with in-built packages of python:</p>

<pre><code>import csv
from collections import OrderedDict

newdata = OrderedDict()
with open('test.csv', 'rb') as testr:
    testreader = csv.reader(testr)
    for row in testreader:
        name = row[0]+ '-' + row[1]
        if name in newdata:
            newdata[name] = [max(existdata, readdata) for existdata, readdata in zip(newdata[name], row[2:])]
        else:
            newdata[name] = row[2:]

    with open('newdata.csv', 'wb') as testw:
        testwriter = csv.writer(testw)
        for name, data in newdata.iteritems():
            testwriter.writerow(name.split('-') + data)
</code></pre>

<p>Best way is to do it is with Pandas, will post in a while.</p>

<h1>EDIT:</h1>

<p>Here is the pandas code:</p>

<pre><code>import pandas
readfile = pandas.read_csv('test.csv') # assuming your CSV is same directory as program
print readfile
</code></pre>

<p><a href="http://i.stack.imgur.com/cGDIK.png" rel="nofollow"><img src="http://i.stack.imgur.com/cGDIK.png" alt="actual data"></a></p>

<pre><code>max_readfile = readfile.groupby(['FirstName', 'LastName']).max()
print max_readfile
</code></pre>

<p>output:</p>

<p><a href="http://i.stack.imgur.com/ledll.png" rel="nofollow"><img src="http://i.stack.imgur.com/ledll.png" alt="pandas output"></a></p>

<p>** @user2241910 quickly posted the pandas solution :)</p>

python apache-spark