pyspark,spark: how to select last row and also how to access pyspark dataframe by index
<p>from a pyspark sql dataframe like </p>

<pre><code>name age city
abc   20  A
def   30  B
</code></pre>

<p>How to get the last row.(Like by df.limit(1) I can get first row of dataframe into new dataframe).</p>

<p>And how can I access the dataframe rows by index.like row no. 12 or 200 .</p>

<p>In pandas I can do</p>

<pre><code>df.tail(1) # for last row
df.ix[rowno or index] # by index
df.loc[] or by df.iloc[]
</code></pre>

<p>I am just curious how to access pyspark dataframe in such ways or alternative ways.</p>

<p>Thanks</p>

<blockquote>
  <p>How to get the last row.</p>
</blockquote>

<p>Long and ugly way which assumes that all columns are oderable:</p>

<pre><code>from pyspark.sql.functions import (
    col, max as max_, struct, monotonically_increasing_id
)

last_row = (df
    .withColumn("_id", monotonically_increasing_id())
    .select(max(struct("_id", *df.columns))
    .alias("tmp")).select(col("tmp.*"))
    .drop("_id"))
</code></pre>

<p>If not all columns can be order you can try:</p>

<pre><code>with_id = df.withColumn("_id", monotonically_increasing_id())
i = with_id.select(max_("_id")).first()[0]

with_id.where(col("_id") == i).drop("_id")
</code></pre>

<p>Note. There is <code>last</code> function in <code>pyspark.sql.functions</code>/ `o.a.s.sql.functions but considering <a href="https://github.com/apache/spark/blob/68b4020d0c0d4f063facfbf4639ef4251dcfda8b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Last.scala#L26-L29" rel="nofollow">description of the corresponding expressions</a> it is not a good choice here.</p>

<blockquote>
  <p>how can I access the dataframe rows by index.like</p>
</blockquote>

<p>You cannot. Spark <code>DataFrame</code> and accessible by index. <a href="http://stackoverflow.com/q/32760888/1560062">You can add indices using <code>zipWithIndex</code></a> and filter later. Just keep in mind this <em>O(N)</em> operation. </p>

python apache-spark pyspark pyspark-sql