def standardize_data(cls, data: pd.DataFrame) -> pd.DataFrame:
data = dataset_utils.strip_whitespace(data)
data = cls.remove_duplicate_city_data(data)
# CDS state level aggregates are identifiable by not having a city or county.
only_county = data[cls.Fields.COUNTY].notnull() & data[cls.Fields.STATE].notnull()
county_hits = numpy.where(only_county, "county", None)
only_state = (
data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].notnull()
)
only_country = (
data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].isnull()
& data[cls.Fields.COUNTRY].notnull()
)
state_hits = numpy.where(only_state, "state", None)
county_hits[state_hits != None] = state_hits[state_hits != None]
county_hits[only_country] = "country"
data[cls.Fields.AGGREGATE_LEVEL] = county_hits
# Backfilling FIPS data based on county names.
# The following abbrev mapping only makes sense for the US
# TODO: Fix all missing cases
data = data[data["country"] == "United States"]
data[CommonFields.COUNTRY] = "USA"
data[CommonFields.STATE] = data[cls.Fields.STATE].apply(
lambda x: US_STATE_ABBREV[x] if x in US_STATE_ABBREV else x
)
fips_data = dataset_utils.build_fips_data_frame()
data = dataset_utils.add_fips_using_county(data, fips_data)
no_fips = data[CommonFields.FIPS].isna()
if no_fips.sum() > 0:
logging.error(f"Removing {len(data.loc[no_fips])} rows without fips id")
# logging.error(f"Removing rows without fips id: {str(data.loc[no_fips])}")
data = data.loc[~no_fips]
data.set_index(["date", "fips"], inplace=True)
if data.index.has_duplicates:
# Use keep=False when logging so the output contains all duplicated rows, not just the
# first or last instance of each duplicate.
logging.error(f"Removing duplicates: {str(data.index.duplicated(keep=False))}")
data = data.loc[~data.index.duplicated(keep=False)]
data.reset_index(inplace=True)
# ADD Negative tests
data[cls.Fields.NEGATIVE_TESTS] = data[cls.Fields.TESTED] - data[cls.Fields.CASES]
return data
def standardize_data(cls, data: pd.DataFrame) -> pd.DataFrame:
data = dataset_utils.strip_whitespace(data)
# Don't want to return city data because it's duplicated in county
# City data before 3-23 was not duplicated.
# data = data[data[cls.Fields.CITY].isnull()]
pre_march_23 = data[data.date < "2020-03-23"]
pre_march_23.county = pre_march_23.apply(fill_missing_county_with_city,
axis=1)
split_data = [
pre_march_23,
data[(data.date >= "2020-03-23") & data[cls.Fields.CITY].isnull()],
]
data = pd.concat(split_data)
# CDS state level aggregates are identifiable by not having a city or county.
only_county = (data[cls.Fields.COUNTY].notnull()
& data[cls.Fields.STATE].notnull())
county_hits = numpy.where(only_county, "county", None)
only_state = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].notnull())
only_country = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].isnull()
& data[cls.Fields.COUNTRY].notnull())
state_hits = numpy.where(only_state, "state", None)
county_hits[state_hits != None] = state_hits[state_hits != None]
county_hits[only_country] = "country"
data[cls.Fields.AGGREGATE_LEVEL] = county_hits
# Backfilling FIPS data based on county names.
# The following abbrev mapping only makes sense for the US
# TODO: Fix all missing cases
data = data[data["country"] == "United States"]
data["state_abbr"] = data[cls.Fields.STATE].apply(
lambda x: US_STATE_ABBREV[x] if x in US_STATE_ABBREV else x)
data["state_tmp"] = data["state"]
data["state"] = data["state_abbr"]
fips_data = dataset_utils.build_fips_data_frame()
data = dataset_utils.add_fips_using_county(data, fips_data)
# ADD Negative tests
data[cls.Fields.NEGATIVE_TESTS] = (data[cls.Fields.TESTED] -
data[cls.Fields.CASES])
# put the state column back
data["state"] = data["state_tmp"]
return data
def standardize_data(cls, data: pd.DataFrame) -> pd.DataFrame:
data = dataset_utils.strip_whitespace(data)
data = cls.remove_duplicate_city_data(data)
# CDS state level aggregates are identifiable by not having a city or county.
only_county = data[cls.Fields.COUNTY].notnull() & data[
cls.Fields.STATE].notnull()
county_hits = numpy.where(only_county, "county", None)
only_state = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].notnull())
only_country = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].isnull()
& data[cls.Fields.COUNTRY].notnull())
state_hits = numpy.where(only_state, "state", None)
county_hits[state_hits != None] = state_hits[state_hits != None]
county_hits[only_country] = "country"
data[cls.Fields.AGGREGATE_LEVEL] = county_hits
# Backfilling FIPS data based on county names.
# The following abbrev mapping only makes sense for the US
# TODO: Fix all missing cases
data = data[data["country"] == "United States"]
data["state_abbr"] = data[cls.Fields.STATE].apply(
lambda x: US_STATE_ABBREV[x] if x in US_STATE_ABBREV else x)
data["state_tmp"] = data["state"]
data["state"] = data["state_abbr"]
fips_data = dataset_utils.build_fips_data_frame()
data = dataset_utils.add_fips_using_county(data, fips_data)
# ADD Negative tests
data[cls.Fields.
NEGATIVE_TESTS] = data[cls.Fields.TESTED] - data[cls.Fields.CASES]
# put the state column back
data["state"] = data["state_tmp"]
return data
def standardize_data(cls, data: pd.DataFrame) -> pd.DataFrame:
data = dataset_utils.strip_whitespace(data)
# Don't want to return city data because it's duplicated in county
# City data before 3-23 was not duplicated.
# data = data[data[cls.Fields.CITY].isnull()]
pre_march_23 = data[data.date < "2020-03-23"]
pre_march_23.county = pre_march_23.apply(fill_missing_county_with_city,
axis=1)
split_data = [
pre_march_23,
data[(data.date >= "2020-03-23") & data[cls.Fields.CITY].isnull()],
]
data = pd.concat(split_data)
# CDS state level aggregates are identifiable by not having a city or county.
only_county = (data[cls.Fields.COUNTY].notnull()
& data[cls.Fields.STATE].notnull())
county_hits = numpy.where(only_county, "county", None)
only_state = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].notnull())
only_country = (data[cls.Fields.COUNTY].isnull()
& data[cls.Fields.CITY].isnull()
& data[cls.Fields.STATE].isnull()
& data[cls.Fields.COUNTRY].notnull())
state_hits = numpy.where(only_state, "state", None)
county_hits[state_hits != None] = state_hits[state_hits != None]
county_hits[only_country] = "country"
data[cls.Fields.AGGREGATE_LEVEL] = county_hits
# Backfilling FIPS data based on county names.
# TODO: Fix all missing cases
fips_data = dataset_utils.build_fips_data_frame()
data = dataset_utils.add_fips_using_county(data, fips_data)
return data