Pyspark: how to add a column to a dataframe from another dataframe?

Question

I have two dataframes with 10 rows.

df1.show()
+-------------------+------------------+--------+-------+
|                lat|               lon|duration|stop_id|
+-------------------+------------------+--------+-------+
|  -6.23748779296875| 106.6937255859375|     247|      0|
|  -6.23748779296875| 106.6937255859375|    2206|      1|
|  -6.23748779296875| 106.6937255859375|     609|      2|
| 0.5733972787857056|101.45503234863281|   16879|      3|
| 0.5733972787857056|101.45503234863281|    4680|      4|
| -6.851855278015137|108.64261627197266|     164|      5|
| -6.851855278015137|108.64261627197266|     220|      6|
| -6.851855278015137|108.64261627197266|    1669|      7|
|-0.9033176600933075|100.41548919677734|   30811|      8|
|-0.9033176600933075|100.41548919677734|   23404|      9|
+-------------------+------------------+--------+-------+

I would like to add the column bank_and_post from df2 to df1.

df2 comes from a function.

def assignPtime(x, mu, std):
  mu = mu.values[0]
  std = std.values[0]
  x1 = np.random.normal(mu, std, 100000) 
  a1, b1 = np.histogram(x1, density=True)
  val = x / 60
  for k, v in enumerate(val):
    prob = 0
    for i,j in enumerate(b1[:-1]):
      v1 = b1[i]
      v2 = b1[i+1]
      if (v >= v1) and (v < v2):
        prob = a1[i]
    x[k] = prob
  return x

ff = pandas_udf(assignPtime, returnType=DoubleType())
df2 = df1.select(ff(col("duration"), lit(15), lit(15)).alias("ptime_bank_and_post"))
df2.show()
+--------------------+
|       bank_and_post|
+--------------------+
|0.021806558032484918|
|0.014366417828826784|
|0.021806558032484918|
|                 0.0|
|                 0.0|
|0.021806558032484918|
|0.021806558032484918|
|0.014366417828826784|
|                 0.0|
|                 0.0|
+--------------------+

If I try

df2 = df2.withColumn("stop_id", monotonically_increasing_id())

I get the error

ValueError: assignment destination is read-only

notNull · Accepted Answer · 2020-05-14T19:19:52.920

Use row_number() window function to add new column to the df1,df2 dataframes then join the dataframes on the row_number column.

Example:

1. Using row_number function:

df1=spark.createDataFrame([(0,),(1,),(2,),(3,),(4,),(5,),(6,),(7,),(8,),(9,)],["stop_id"])

df2=spark.createDataFrame([("0.021806558032484918",),("0.014366417828826784",),("0.021806558032484918",),("                 0.0",),("                 0.0",),("0.021806558032484918",),("0.021806558032484918",),("0.014366417828826784",),("                 0.0",),("                 0.0",)],["bank_and_post"])

from pyspark.sql import *
from pyspark.sql.functions import *

w=Window.orderBy(lit(1))

df4=df2.withColumn("rn",row_number().over(w)-1)
df3=df1.withColumn("rn",row_number().over(w)-1)

df3.join(df4,["rn"]).drop("rn").show()

#+-------+--------------------+
#|stop_id|       bank_and_post|
#+-------+--------------------+
#|      0|0.021806558032484918|
#|      1|0.014366417828826784|
#|      2|0.021806558032484918|
#|      3|                 0.0|
#|      4|                 0.0|
#|      5|0.021806558032484918|
#|      6|0.021806558032484918|
#|      7|0.014366417828826784|
#|      8|                 0.0|
#|      9|                 0.0|
#+-------+--------------------+

2. Using monotonically_increasing_id() function:

df1.withColumn("mid",monotonically_increasing_id()).\
join(df2.withColumn("mid",monotonically_increasing_id()),["mid"]).\
drop("mid").\
orderBy("stop_id").\
show()
#+-------+--------------------+
#|stop_id|       bank_and_post|
#+-------+--------------------+
#|      0|0.021806558032484918|
#|      1|0.014366417828826784|
#|      2|0.021806558032484918|
#|      3|                 0.0|
#|      4|                 0.0|
#|      5|0.021806558032484918|
#|      6|0.021806558032484918|
#|      7|0.014366417828826784|
#|      8|                 0.0|
#|      9|                 0.0|
#+-------+--------------------+

3. Using row_number() on monotonically_increasing_id() function:

w=Window.orderBy("mid")
df3=df1.withColumn("mid",monotonically_increasing_id()).withColumn("rn",row_number().over(w) - 1)
df4=df2.withColumn("mid",monotonically_increasing_id()).withColumn("rn",row_number().over(w) - 1)
df3.join(df4,["rn"]).drop("rn","mid").show()

#+-------+--------------------+
#|stop_id|       bank_and_post|
#+-------+--------------------+
#|      0|0.021806558032484918|
#|      1|0.014366417828826784|
#|      2|0.021806558032484918|
#|      3|                 0.0|
#|      4|                 0.0|
#|      5|0.021806558032484918|
#|      6|0.021806558032484918|
#|      7|0.014366417828826784|
#|      8|                 0.0|
#|      9|                 0.0|
#+-------+--------------------+

4. Using zipWithIndex:

df3=df1.rdd.zipWithIndex().toDF().select("_1.*","_2")
df4=df2.rdd.zipWithIndex().toDF().select("_1.*","_2")
df3.join(df4,["_2"]).drop("_2").orderBy("stop_id").show()
#+-------+--------------------+
#|stop_id|       bank_and_post|
#+-------+--------------------+
#|      0|0.021806558032484918|
#|      1|0.014366417828826784|
#|      2|0.021806558032484918|
#|      3|                 0.0|
#|      4|                 0.0|
#|      5|0.021806558032484918|
#|      6|0.021806558032484918|
#|      7|0.014366417828826784|
#|      8|                 0.0|
#|      9|                 0.0|
#+-------+--------------------+

I get an error if I try. `df2 = df2.withColumn("stop_id", monotonically_increasing_id())`. `ValueError: assignment destination is read-only`. I wrote the function I am using to generate `df2` — emax, May 14 '20 at 22:12
@emax, Don't think the error is caused by `monotonically_increasing_id()`... but the error it seems related to `numpy` https://stackoverflow.com/questions/39554660/np-arrays-being-immutable-assignment-destination-is-read-only — notNull, May 14 '20 at 22:57
monotonically_increasing_id() doesn't guarantee the number is consecutive. The two dataframes are not joined correctly using the method. — Salty Gold Fish, Jun 03 '21 at 02:04

Pyspark: how to add a column to a dataframe from another dataframe?

1 Answers1

Linked