Handling Missing Data with Pyspark

from pyspark.sql import SparkSession
from pyspark.sql.functions import countDistinct, avg,stddev

# Load data from a CSV
file_location = "/FileStore/tables/df_panel_fix.csv"
df = spark.read.format("CSV").option("inferSchema", True).option("header", True).load(file_location)
display(df.take(5))

_c0	province	specific	general	year	gdp	fdi	rnr	rr	i	fr	reg	it
0	Anhui	147002.0	null	1996	2093.3	50661	0.0	0.0	0.0	1128873	East China	631930
1	Anhui	151981.0	null	1997	2347.32	43443	0.0	0.0	0.0	1356287	East China	657860
2	Anhui	174930.0	null	1998	2542.96	27673	0.0	0.0	0.0	1518236	East China	889463
3	Anhui	285324.0	null	1999	2712.34	26131	null	null	null	1646891	East China	1227364
4	Anhui	195580.0	32100.0	2000	2902.09	31847	0.0	0.0	0.0	1601508	East China	1499110

df.show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| null|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| null|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| null|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| null|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| null|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0| null|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| null|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| null|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| null|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| null|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| null|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

Dropping Columns without non-null values

# Has to have at least 2 NON-null values
df.na.drop(thresh=2).show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| null|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| null|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| null|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| null|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| null|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0| null|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| null|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| null|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| null|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| null|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| null|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

Dropping any row that contains missing data

df.na.drop().show()

+---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ _c0| province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0| 1601508| East China|1499110| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0| 1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0| 1896479| East China|2815820| 10| Anhui|1457872.0| 279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324| 3434548| East China|5167300| 11| Anhui|2213991.0| 178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324| 4468640| East China|7040099| 16| Beijing| 281769.0| 188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53| 1667114| North China| 757990| 18| Beijing| 558569.0| 280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53| 2511249| North China|1078754| 19| Beijing| 642581.0| 269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0| 2823366| North China|1426600| 22| Beijing|1315102.0| 405966.0|2006|8117.78|455191| 0.0|0.794871795| 0.0| 4830392| North China|1782317| 23| Beijing| 752279.0|1023453.0|2007|9846.81|506572| 0.0|0.794871795| 0.0|14926380| North China|1962192| 34|Chongqing| 833423.0| 150000.0|2006|3907.23| 69595|0.09677419400000001| 0.0| 0.0| 1762409|Southwest China|3124234| 35|Chongqing|1791403.0| 447900.0|2007|4676.13|108534|0.09677419400000001| 0.0| 0.0| 4427000|Southwest China|3923569| 40| Fujian| 142650.0| 71807.0|2000|3764.54|343191| 0.0| 0.0| 0.0| 2110577| East China| 819028| 42| Fujian| 137190.0| 59263.0|2002|4467.55|383837| 0.22| 0.3| 0.0| 2373047| East China|1184990| 43| Fujian| 148812.0| 68142.0|2003|4983.67|259903| 0.0| 0.0| 0.3| 2648861| East China|1364980| 46| Fujian| 397517.0| 149549.0|2006|7583.85|322047| 0.4| 0.0| 0.0| 4830320| East China|2135224| 47| Fujian| 753552.0| 317700.0|2007|9248.53|406058| 0.4| 0.0| 0.0| 6994577| East China|2649011| 52| Gansu| 223984.0| 58533.0|2000|1052.88| 6235| 0.0|0.153846154| 0.0| 505196|Northwest China|1258100| 54| Gansu| 337894.0| 129791.0|2002|1232.03| 6121| 0.0| 0.13| 0.0| 597159|Northwest China|1898911| 58| Gansu| 833430.0| 516342.0|2006|2277.35| 2954| 0.0| 0.0|0.128205128| 924080|Northwest China|3847158| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ only showing top 20 rows

df.na.drop(subset=["general"]).show()

+---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ _c0| province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0| 1601508| East China|1499110| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0| 1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0| 1896479| East China|2815820| 8| Anhui| 898441.0| 349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 10| Anhui|1457872.0| 279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324| 3434548| East China|5167300| 11| Anhui|2213991.0| 178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324| 4468640| East China|7040099| 16| Beijing| 281769.0| 188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53| 1667114| North China| 757990| 18| Beijing| 558569.0| 280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53| 2511249| North China|1078754| 19| Beijing| 642581.0| 269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0| 2823366| North China|1426600| 20| Beijing|1009936.0| 309025.0|2004|6033.21|308354| 0.0|0.794871795| 0.0| null| North China|1644601| 22| Beijing|1315102.0| 405966.0|2006|8117.78|455191| 0.0|0.794871795| 0.0| 4830392| North China|1782317| 23| Beijing| 752279.0|1023453.0|2007|9846.81|506572| 0.0|0.794871795| 0.0|14926380| North China|1962192| 30|Chongqing| 311770.0| 41907.0|2002|2232.86| 19576| null| null| null| 762806|Southwest China|1906968| 31|Chongqing| 335715.0| 18700.0|2003|2555.72| 26083| null| null| null| 929935|Southwest China|1778125| 32|Chongqing| 568835.0| 97500.0|2004|3034.58| 40508| null| null| null| null|Southwest China|2197948| 34|Chongqing| 833423.0| 150000.0|2006|3907.23| 69595|0.09677419400000001| 0.0| 0.0| 1762409|Southwest China|3124234| 35|Chongqing|1791403.0| 447900.0|2007|4676.13|108534|0.09677419400000001| 0.0| 0.0| 4427000|Southwest China|3923569| 40| Fujian| 142650.0| 71807.0|2000|3764.54|343191| 0.0| 0.0| 0.0| 2110577| East China| 819028| 42| Fujian| 137190.0| 59263.0|2002|4467.55|383837| 0.22| 0.3| 0.0| 2373047| East China|1184990| 43| Fujian| 148812.0| 68142.0|2003|4983.67|259903| 0.0| 0.0| 0.3| 2648861| East China|1364980| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ only showing top 20 rows

df.na.drop(how='any').show()

+---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ _c0| province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0| 1601508| East China|1499110| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0| 1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0| 1896479| East China|2815820| 10| Anhui|1457872.0| 279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324| 3434548| East China|5167300| 11| Anhui|2213991.0| 178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324| 4468640| East China|7040099| 16| Beijing| 281769.0| 188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53| 1667114| North China| 757990| 18| Beijing| 558569.0| 280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53| 2511249| North China|1078754| 19| Beijing| 642581.0| 269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0| 2823366| North China|1426600| 22| Beijing|1315102.0| 405966.0|2006|8117.78|455191| 0.0|0.794871795| 0.0| 4830392| North China|1782317| 23| Beijing| 752279.0|1023453.0|2007|9846.81|506572| 0.0|0.794871795| 0.0|14926380| North China|1962192| 34|Chongqing| 833423.0| 150000.0|2006|3907.23| 69595|0.09677419400000001| 0.0| 0.0| 1762409|Southwest China|3124234| 35|Chongqing|1791403.0| 447900.0|2007|4676.13|108534|0.09677419400000001| 0.0| 0.0| 4427000|Southwest China|3923569| 40| Fujian| 142650.0| 71807.0|2000|3764.54|343191| 0.0| 0.0| 0.0| 2110577| East China| 819028| 42| Fujian| 137190.0| 59263.0|2002|4467.55|383837| 0.22| 0.3| 0.0| 2373047| East China|1184990| 43| Fujian| 148812.0| 68142.0|2003|4983.67|259903| 0.0| 0.0| 0.3| 2648861| East China|1364980| 46| Fujian| 397517.0| 149549.0|2006|7583.85|322047| 0.4| 0.0| 0.0| 4830320| East China|2135224| 47| Fujian| 753552.0| 317700.0|2007|9248.53|406058| 0.4| 0.0| 0.0| 6994577| East China|2649011| 52| Gansu| 223984.0| 58533.0|2000|1052.88| 6235| 0.0|0.153846154| 0.0| 505196|Northwest China|1258100| 54| Gansu| 337894.0| 129791.0|2002|1232.03| 6121| 0.0| 0.13| 0.0| 597159|Northwest China|1898911| 58| Gansu| 833430.0| 516342.0|2006|2277.35| 2954| 0.0| 0.0|0.128205128| 924080|Northwest China|3847158| +---+---------+---------+---------+----+-------+------+-------------------+-----------+-----------+--------+---------------+-------+ only showing top 20 rows

df.na.drop(how='all').show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| null|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| null|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| null|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| null|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| null|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0| null|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| null|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| null|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| null|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| null|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| null|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

Imputation of Null Values

df.na.fill('example').show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| null|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| null|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| null|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| null|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| null|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0|example| East China|3422176| 9| Anhui| 898441.0| null|2005|5350.17| 69000| 0.0| 0.0|0.324324324|example| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| null|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| null|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| null|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| null|1999|2678.82|197525| 0.0| 0.0| 0.53|example|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| null|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

Imputation of 0

df.na.fill(0).show()

+---+--------+---------+--------+----+-------+------+---+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi|rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+---+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| 0.0|1996| 2093.3| 50661|0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| 0.0|1997|2347.32| 43443|0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| 0.0|1998|2542.96| 27673|0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| 0.0|1999|2712.34| 26131|0.0| 0.0| 0.0|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847|0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| 0.0|2001|3246.71| 33672|0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375|0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720|0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669|0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0| 0.0|2005|5350.17| 69000|0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354|0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892|0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| 0.0|1996| 1789.2|155290|0.0| 0.0| 0.0| 634562|North China| 508135| 13| Beijing| 165957.0| 0.0|1997|2077.09|159286|0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| 0.0|1998|2377.18|216800|0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| 0.0|1999|2678.82|197525|0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368|0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| 0.0|2001|3707.96|176818|0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464|0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126|0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+---+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

df.na.fill('example',subset=['fr']).show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| null|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| null|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| null|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| null|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| null|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0|example| East China|3422176| 9| Anhui| 898441.0| null|2005|5350.17| 69000| 0.0| 0.0|0.324324324|example| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| null|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| null|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| null|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| null|1999|2678.82|197525| 0.0| 0.0| 0.53|example|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| null|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

df.na.fill(0,subset=['general']).show()

+---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0| 0.0|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0| 0.0|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0| 0.0|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0| 0.0|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0| 0.0|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0|349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0| 0.0|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0|279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0|178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0| 0.0|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0| 0.0|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0| 0.0|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0| 0.0|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0|188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0| 0.0|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0|280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0|269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+--------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

Imputation of the Mean

from pyspark.sql.functions import mean
mean_val = df.select(mean(df['general'])).collect()

mean_val[0][0]

Out[19]: 309127.53846153844

mean_gen = mean_val[0][0]

df.na.fill(mean_gen,["general"]).show()

+---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0|309127.53846153844|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0|309127.53846153844|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0|309127.53846153844|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0|309127.53846153844|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0|309127.53846153844|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0| 349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0|309127.53846153844|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0| 279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0| 178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0|309127.53846153844|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0|309127.53846153844|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0|309127.53846153844|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0|309127.53846153844|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0| 188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0|309127.53846153844|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0| 280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0| 269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

df.na.fill(df.select(mean(df['general'])).collect()[0][0],['general']).show()

+---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ _c0|province| specific| general|year| gdp| fdi| rnr| rr| i| fr| reg| it| +---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ 0| Anhui| 147002.0|309127.53846153844|1996| 2093.3| 50661| 0.0| 0.0| 0.0|1128873| East China| 631930| 1| Anhui| 151981.0|309127.53846153844|1997|2347.32| 43443| 0.0| 0.0| 0.0|1356287| East China| 657860| 2| Anhui| 174930.0|309127.53846153844|1998|2542.96| 27673| 0.0| 0.0| 0.0|1518236| East China| 889463| 3| Anhui| 285324.0|309127.53846153844|1999|2712.34| 26131|null| null| null|1646891| East China|1227364| 4| Anhui| 195580.0| 32100.0|2000|2902.09| 31847| 0.0| 0.0| 0.0|1601508| East China|1499110| 5| Anhui| 250898.0|309127.53846153844|2001|3246.71| 33672| 0.0| 0.0| 0.0|1672445| East China|2165189| 6| Anhui| 434149.0| 66529.0|2002|3519.72| 38375| 0.0| 0.0| 0.0|1677840| East China|2404936| 7| Anhui| 619201.0| 52108.0|2003|3923.11| 36720| 0.0| 0.0| 0.0|1896479| East China|2815820| 8| Anhui| 898441.0| 349699.0|2004| 4759.3| 54669| 0.0| 0.0| 0.0| null| East China|3422176| 9| Anhui| 898441.0|309127.53846153844|2005|5350.17| 69000| 0.0| 0.0|0.324324324| null| East China|3874846| 10| Anhui|1457872.0| 279052.0|2006| 6112.5|139354| 0.0| 0.0|0.324324324|3434548| East China|5167300| 11| Anhui|2213991.0| 178705.0|2007|7360.92|299892| 0.0| 0.0|0.324324324|4468640| East China|7040099| 12| Beijing| 165957.0|309127.53846153844|1996| 1789.2|155290|null| null| null| 634562|North China| 508135| 13| Beijing| 165957.0|309127.53846153844|1997|2077.09|159286| 0.0| 0.0| 0.6| 634562|North China| 569283| 14| Beijing| 245198.0|309127.53846153844|1998|2377.18|216800| 0.0| 0.0| 0.53| 938788|North China| 695528| 15| Beijing| 388083.0|309127.53846153844|1999|2678.82|197525| 0.0| 0.0| 0.53| null|North China| 944047| 16| Beijing| 281769.0| 188633.0|2000|3161.66|168368| 0.0| 0.0| 0.53|1667114|North China| 757990| 17| Beijing| 441923.0|309127.53846153844|2001|3707.96|176818| 0.0| 0.0| 0.53|2093925|North China|1194728| 18| Beijing| 558569.0| 280277.0|2002| 4315.0|172464| 0.0| 0.0| 0.53|2511249|North China|1078754| 19| Beijing| 642581.0| 269596.0|2003|5007.21|219126| 0.0|0.794871795| 0.0|2823366|North China|1426600| +---+--------+---------+------------------+----+-------+------+----+-----------+-----------+-------+-----------+-------+ only showing top 20 rows

This post includes code adapted from Spark and Python for Big Data udemy course and Spark and Python for Big Data notebooks.