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'})
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'))