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