Replace a null value with a string value

Question

I try tro convert null values into a string variable as x. The reason is that this data frame should be imported to power Bi to make visualisations. We aim to calculate a box plot and my idea is that if there is a x, then these calues will no be included in the calculation. In this way, we can avoid overestimation in the calculation. Is it a good idea to replace null values with x? Or is there a better approach?

data = [["1", "Amit", "DU", "I", "8", "6"],
        ["2", "Mohit", "DU", "I", "4", "2"],
        ["3", "rohith", "BHU", "I", "5", "3"],
        ["4", "sridevi", "LPU", "I", "1", "6"],
        ["1", "sravan", "KLMP", "M", "2", "4"],
        ["5", "gnanesh", "IIT", "M", "null", "8"],
       ["6", "gnadesh", "KLM", "c", "10", "null"]]

columns = ['ID', 'NAME', 'college', 'metric', 'x', 'y']


dataframe = spark.createDataFrame(data, columns)

Actual output

+---+-------+-------+------+----+-----+
| ID|   NAME|college|metric|  x |  y  |
+---+-------+-------+------+----+----+
|  1|   Amit|     DU|     I|  8 |  6 |
|  2|  Mohit|     DU|     I|  4 |  2 |
|  3| rohith|    BHU|     I|  5 |  3 |
|  4|sridevi|    LPU|     I|  1 |  6 |
|  1| sravan|   KLMP|     M|  2 |  4 |
|  5|gnanesh|    IIT|     M|null|  8 |
|  6|gnadesh|    KLM|     c| 10 |null|
+---+-------+-------+------+----+----+

Desired output

+---+-------+-------+------+----+-----+
| ID|   NAME|college|metric|  x |  y  |
+---+-------+-------+------+----+----+
|  1|   Amit|     DU|     I|  8 |  6 |
|  2|  Mohit|     DU|     I|  4 |  2 |
|  3| rohith|    BHU|     I|  5 |  3 |
|  4|sridevi|    LPU|     I|  1 |  6 |
|  1| sravan|   KLMP|     M|  2 |  4 |
|  5|gnanesh|    IIT|     M|  x |  8 |
|  6|gnadesh|    KLM|     c| 10 |  x |
+---+-------+-------+------+----+----+

I tried this code, but i does not work with string, but only with numbers


data = data.fillna({'y':'x'})

check if the null is written as a string? if yes, then use when().otherwise() — samkart
– samkart, Commented Dec 5, 2022 at 9:23

Azhar Khan · Accepted Answer · 2022-12-09 05:05:21Z

1

You can use the following:

data = data.fillna('x')

Please ensure that both your columns x and y are of Stringtype():

data = data.withColumns('x',col(('x').cast('string'))

edited Dec 9, 2022 at 5:05

Azhar Khan

4,15811 gold badges31 silver badges34 bronze badges

answered Dec 5, 2022 at 10:47

Aman Kumar Gupta

512 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace a null value with a string value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related