Help! Filling NaN with a certain values based on conditions from other column?

Hi all

I am complete novice in coding 6 weeks in… I am working on large data set 18 columns with 1,282,354 entries for my assignment and I’m stuck on this issue:

The dataset has two location columns “State” and “ZIP Code” see below sample.

  State ZIPCode
0    CA    4568
1   NaN     NaN
2    FL    4546XX
3    TX     323
4    NY   45578
5   NaN    7857x
6    GA    7888AC
7    AA     NaN

I have some missing values and wanted to replace them based on the value of one of the columns for example:

  1. If State is not NaN but ZIP Code is NaN, then fill ZIP Code NaN with “N/A”

and

  1. If State is NaN and ZIP Code is NaN, then fill both State and ZIP Code with “Unknown”

and also

  1. If State is NaN but ZIP Code is not NaN, then fill State with “No Data” (I don’t think I have any gaps here but can’t be sure)

I tried If else but can’t figure it out.

Any ideas how the code can look like?

Thanks!

Determine whether each row has NaN in the ‘State’ column:

>>> table['State'].isna()
0    False
1     True
2    False
3    False
4    False
5     True
6    False
7    False
Name: State, dtype: bool

Invert that result:

>>> ~table['State'].isna()
0     True
1    False
2     True
3     True
4     True
5    False
6     True
7     True
Name: State, dtype: bool

For the ‘ZIPCode’ column:

>>> table['ZIPCode'].isna()
0    False
1     True
2    False
3    False
4    False
5    False
6    False
7     True
Name: ZIPCode, dtype: bool

Combine them:

>>> ~table['State'].isna() & table['ZIPCode'].isna()
0    False
1    False
2    False
3    False
4    False
5    False
6    False
7     True
dtype: bool

You can display the rows:

>>> table[~table['State'].isna() & table['ZIPCode'].isna()]
  State ZIPCode
7    AA     NaN

Fill the ‘ZIPCode’ column with ‘N/A’:

>>> table.loc[~table['State'].isna() & table['ZIPCode'].isna(), 'ZIPCode'] = 'N/A'

Note the use of .loc when assigning.

Display the resulting table:

>>> table
  State ZIPCode
0    CA    4568
1   NaN     NaN
2    FL  4546XX
3    TX     323
4    NY   45578
5   NaN   7857x
6    GA  7888AC
7    AA     N/A

Looks correct.

Brilliant!
It worked

Thank you,