[Python] Fill null values based on unusual repeating pattern

I have a pandas df that needs to be cleaned by filling nulls in city_id and address_type fields:

City_ID​

Date​

State​

City​

Address_type​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

Null​

10/1/24​

California​

1002​

Null​

Null​

10/1/24​

California​

House​

Null​

Null​

Null​

Null​

Null​

Null​

Null​

10/1/24​

California​

San Diego​

Null​

Null​

10/1/24​

California​

San Diego​

Null​

Null​

10/1/24​

New York​

1003​

Null​

Null​

10/1/24​

New York​

Apartment​

Null​

Null​

Null​

Null​

Null​

Null​

Null​

10/1/24​

New York​

Queens​

Null​

Null​

10/1/24​

New York​

Queens​

Null​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

This is the desired output:

City_ID​

Date​

State​

City​

Address_type​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1001​

10/1/24​

Texas​

Houston​

House​

1002​

10/1/24​

California​

San Diego​

House​

1002​

10/1/24​

California​

San Diego​

House​

1002​

10/1/24​

California​

San Diego​

House​

1002​

10/1/24​

California​

San Diego​

House​

1002​

10/1/24​

California​

San Diego​

House​

1003​

10/1/24​

New York​

Queens​

Apartment​

1003​

10/1/24​

New York​

Queens​

Apartment​

1003​

10/1/24​

New York​

Queens​

Apartment​

1003​

10/1/24​

New York​

Queens​

Apartment​

1003​

10/1/24​

New York​

Queens​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

1004​

10/1/24​

Washington DC​

Washington DC​

Apartment​

Rules: If a city has a null value in the city column, the following rules apply:

1. The correct city ID will always appear in the first occurrence for that city in the 'City' column
2. The correct address_type will always appear in the second occurrence for that city in the 'City' column
3. Null for 'City' will always occur in the third entry for that city
4. There will always be exactly 5 entries for a city with a null

If a city does not have a null value, it will not always have 5 entries

The groups of cities with nulls occur at random through the dataset, so there will need to be a rule to stop from overfilling into another group

I am not as concerned with backfilling the correct Date, State, and City fields as long as the City_ID and Address_type are populated.

Thanks

Continue reading...