How to extract output of specific columns from groupby on pandas dataframe
<p><em>Need to extract output of groupBy pandas dataframe into expected output described below and write to a file:</em></p>

<p><strong><em>Input file testdata.txt:</em></strong></p>

<pre><code>id, distance

1,0.5
1,1.2
1,0.2   &lt;------------this row should be selected
1,1.5
2,2.5
2,0.5   &lt;------------this row should be selected
2,1.0
2,3.0
</code></pre>

<p><strong><em>Expected output:</em></strong>
<em>Find row with shortest distance for each id</em></p>

<pre><code>1 0.2
2 0.5
</code></pre>

<p><strong><em>Actual Output looks as follows: Need to process this output to get expected output described above:</em></strong></p>

<pre><code>_1          
1  2  1  0.2
2  5  2  0.5


**My Python Script:**

lines = sc.textFile("file:///data/testdata.txt")
#RDD to Spark DataFrame
sparkDF = lines.map(lambda x: str(x)).map(lambda w: w.split(',')).toDF()

#Spark DataFrame to Pandas DataFrame
pdsDF = sparkDF.toPandas()

#print dataframe schema
print (pdsDF)

StoreIdGroups = pdsDF.groupby(by=['_1'])
result = StoreIdGroups.apply(lambda g: g[g['_2'] == g['_2'].min()])
print(result)
</code></pre>

<p>Any help is appreciated.
Thanks</p>

<p>This is just a simple case of applying using the <code>.min()</code> method of a <code>DataFrameGroupBy</code> object:</p>

<pre><code>data = [(1,0.5),(1,1.2),(1,0.2),(1,1.5),(2,2.5),(2,0.5),(2,1.0),(2,3.0)]
df = pd.DataFrame(data)
df.columns = ['id', 'distance']
results = df.groupby('id').min()
</code></pre>

<p><code>results</code> has the data you are looking for. If you want some nice printing, try:</p>

<pre><code>for i in results.itertuples():
    print(*tuple(i))
</code></pre>

python pandas apache-spark lambda