I have a pandas dataframe df_all with columns 'STATE', 'AGENCY', 'YEAR_MONTH' and 'MONTHLY_COUNT'. Column 'MONTHLY_COUNT' is of datatype float64. 'YEAR_MONTH' values of are of type oject, along with the remaining columns. Additionally, 'YEAR_MONTH' values are in the form of '1999-01' for example, so in the form YYYY-MM. Now, all I want to do is a simple aggregation task, but for some reason I am getting surprising results. I want to, for each value of 'STATE' which are values of AL, CA, GA, etc (state abbreviations) take the monthly count of total incidents. In other words, I want to aggregate 'MONTHLY_COUNT' values for each 'AGENCY' within the state. Unless I am missing something, this is a fairly straightforward aggregation task in pandas. The code I have tried is df_all['YEAR_MONTH'] = pd.to_datetime(df_all['YEAR_MONTH']) df_monthly_counts_state = df_all.groupby( ['NUMERIC_STATE_CODE', 'YEAR_MONTH'] )['MONTHLY_COUNT'].sum().reset_index() df_monthly_counts_state.columns = ['NUMERIC_STATE_CODE', 'YEAR_MONTH', 'TOTAL_MONTHLY_COUNT'] Now, when I run this code, I get about 70% of my rows with 'TOTAL_MONTHLY_COUNT' values of NaN. Importantly, there are no NaN values in 'MONTHLY_COUNT' in df_all. Every row is a float value above zero. I've also checked for incosistent data. There are no inconsistensies in the 'MONTHLY_COUNT' or 'STATE' columns. Is there a different approach I can take? Or is there something I am doing wrong? Any help would be appreciated. Continue reading...
