bind repeated ID with Incremental Number

user_8596498 0
Viewed 33

I pre-processing the data i have dataframe like 

id     ref          text
+------+------+--------------------+
|  8309|  3129|3 MO F/U HAIR LOS...|
|  8309|  3129|        4 MO SKIN CK|
|  8309|  3129|      4 MO F/U LM AG|
|  8309|  3129|HAIR LOSS AND SPO...|
|  8309|  3129|    2 MO F/U CONF KC|
|  8309|  3129|SSR AND DISCUSS H...|
|  4569|  1101|F/U LM TO CONFIRM...|
|  4569|  1101|F/U (LF) LM TO CO...|
|  4569|  1101|        FU CONFIRMED|
|  4569|  1101|F/U MRI RESULTS  ...|
|  4569|  1101|F/U AFTER MRI JC ...|
|  4569|  1101|                  FU|
|  4569|  1101|F/U AND NEW PROBL...|
|  4569|  1101|                 F/U|
|  4569|  1101|        FU CONFIRMED|
|  4569|  1101|REVIEW MRI       ...|
|  4569|  1101|REVIEW MRI RESULT...|
+------+------+--------------------+

I want to convert this Dataframe like this 

   id       ref          text
+--------+------+--------------------+
|  8309  |  3129|3 MO F/U HAIR LOS...|
|  8309_1|  3129|        4 MO SKIN CK|
|  8309_2|  3129|      4 MO F/U LM AG|
|  8309_3|  3129|HAIR LOSS AND SPO...|
|  8309_4|  3129|    2 MO F/U CONF KC|
|  8309_5|  3129|SSR AND DISCUSS H...|
|  4569  |  1101|F/U LM TO CONFIRM...|
|  4569_1|  1101|F/U (LF) LM TO CO...|
|  4569_2|  1101|        FU CONFIRMED|
|  4569_3|  1101|F/U MRI RESULTS  ...|
|--------|------|--------------------|

i want just bind the repeated ID with unique number. If it not incremental it will be fine.

pyspark pandas dataframe
2 Answers

Use GroupBy.cumcount for count:

df['id'] = (df['id'].astype(str).add(df.groupby('id')
                                       .cumcount()
                                       .astype(str)
                                       .radd('_')
                                       .replace('_0','')))
print (df)

         id   ref                  text
0      8309  3129  3 MO F/U HAIR LOS...
1    8309_1  3129          4 MO SKIN CK
2    8309_2  3129        4 MO F/U LM AG
3    8309_3  3129  HAIR LOSS AND SPO...
4    8309_4  3129      2 MO F/U CONF KC
5    8309_5  3129  SSR AND DISCUSS H...
6      4569  1101  F/U LM TO CONFIRM...
7    4569_1  1101  F/U (LF) LM TO CO...
8    4569_2  1101          FU CONFIRMED
9    4569_3  1101  F/U MRI RESULTS  ...
10   4569_4  1101  F/U AFTER MRI JC ...
11   4569_5  1101                    FU
12   4569_6  1101  F/U AND NEW PROBL...
13   4569_7  1101                   F/U
14   4569_8  1101          FU CONFIRMED
15   4569_9  1101         REVIEW MRI...
16  4569_10  1101  REVIEW MRI RESULT...
user_29010020

You can use combination of row_number(), lag(), when and window functions to get your desired result as 

import org.apache.spark.sql.expressions._
import org.apache.spark.sql.functions._
def windowSpec = Window.partitionBy("id").orderBy("ref")
df.withColumn("rank", lag(row_number().over(windowSpec), 1).over(windowSpec))
    .withColumn("id", when($"rank".isNotNull, concat_ws("_", $"id", $"rank")).otherwise($"id"))
    .drop("rank")
    .show(false)

You should be getting final dataframe as 

+-------+----+--------------------+
|id     |ref |text                |
+-------+----+--------------------+
|4569   |1101|F/U LM TO CONFIRM...|
|4569_1 |1101|F/U (LF) LM TO CO...|
|4569_2 |1101|        FU CONFIRMED|
|4569_3 |1101|F/U MRI RESULTS  ...|
|4569_4 |1101|F/U AFTER MRI JC ...|
|4569_5 |1101|                  FU|
|4569_6 |1101|F/U AND NEW PROBL...|
|4569_7 |1101|                 F/U|
|4569_8 |1101|        FU CONFIRMED|
|4569_9 |1101|REVIEW MRI       ...|
|4569_10|1101|REVIEW MRI RESULT...|
|8309   |3129|3 MO F/U HAIR LOS...|
|8309_1 |3129|        4 MO SKIN CK|
|8309_2 |3129|      4 MO F/U LM AG|
|8309_3 |3129|HAIR LOSS AND SPO...|
|8309_4 |3129|    2 MO F/U CONF KC|
|8309_5 |3129|SSR AND DISCUSS H...|
+-------+----+--------------------+
user_58807060
Related