[Python] Built-in Pandas function for cell-level apply with index/column awareness?

I am cleaning historical data for forecasting for exponential smoothing. I have data at the US county level (i.e., second-level administrative division), but there are a lot of zero values (due to low volume) that lead to issues with the forecasting model.

Since the data is highly seasonal, what I am doing is checking each county's data for each year. If a particular year's data contains zeroes, I am replacing the county's data for that year with an adjusted dataset that applies the state-level seasonality to the county-level annual volume.

After much trial and error to avoid iteration that led me down paths of nested apply functions with reset indices (e.g., df.apply(lambda x: x.reset_index().apply(lambda y: [calculation])), I eventually wrote the data cleanup using iteration that calculates the seasonality and then multiplies the seasonal data by a dataframe that has the annual volume stored in monthly columns:

# Initialize empty seasonality df with the index and column values from the source data
cty_season = pd.DataFrame(index=cty_data.index, columns=cty_data.columns)

# Iterate through the index and columns to populate each value
for idx in cty_season.index:
for col in cty_season.columns:
cty_season.loc[idx,col] = [calculation referring to helper dfs with identical indices and columns]

# Combine seasonality data with sales totals to get revised dataset
cty_adj = cty_season * cty_annual


Is there a way to do this more efficiently or in a way that is more "pandic" (or whatever the Pandas equivalent to pythonic is)? The only thing that comes to mind is breaking apart the columns so that each year is a separate row, which might allow a more straightforward apply statement since the replacement is being done year by year.

Continue reading...