Заполнение нулевых значений на основе необычного повторяющегося шаблона ⇐ Python

[Anonymous]

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:

• The correct city ID will always appear in the first occurrence for that city in the 'City' column
• The correct address_type will always appear in the second occurrence for that city in the 'City' column
• Null for 'City' will always occur in the third entry for that city
• 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

Подробнее здесь: https://stackoverflow.com/questions/790 ... ng-pattern