Using groupby as_index=False, count, to_frame gives 'Dataframe' object has no attribute to_frame

I am trying to create a new dataframe from an existing dataframe with a groupby(), a count(), and a to_frame(). I am getting AttributeError: 'DataFrame' object has no attribute 'to_frame' after adding 'as_index=False' to the groupby.

This is the code:


    newdat = indat.query('-1017 <= WDIR16 <= -1000')
    newdat.reset_index(drop=True, inplace=True)
    newdat.sort_values(by=['YEAR', 'MO', 'GP', 'HR'], inplace=True)

    # Find Count
    w1 = newdat.groupby(['YEAR','MO', 'GP','HR'], as_index=False)["WDIR16"].count().to_frame(name='wndclimodirectionobsqty').reset_index()

    # Find Means
    w1['wndclimomeanspeedrate'] = newdat.groupby(['YEAR','MO', 'GP', 'HR'], as_index=False).aggregate({'WSPD':'mean'}, as_index=False).values

The error occurs on the 'to_frame' line. The reason I am using 'as_index=False' in the groupby is because sometimes the existing dataframe can be empty with columns. Reference: Keep columns after a groupby in an empty dataframe If I leave out the 'as_index=False' the line with the 'to_frame' works. BUT, if the dataframe is empty on a groupby, the empty columns do not move over to the new dataframe. Any ideas?

Here is a few rows of the newdat dataframe:

NETWORKTYPE,PLATFORMID,REPORTTYPECODE,OBSERVATIONTIME,YEAR,MO,DAY,HR,MINUTEDV,PLATFORMHEIGHT,TEMPC,DEWPC,WDIR,WSPD,GUST,SLP,STNPRES,ALSTG,CIG,SKY,CAVOK,VSBY,PRCP1,PRCPTIM1,PRCP2,PRCPTIM2,PRCP3,PRCPTIM3,PRCP4,PRCPTIM4,HUMREL,VAPOR,ABSHUM,SPHUM,TVIRTK,DENSITY,DENALT,PRSALT,SKY100,TEMP_GE32,TEMP_LE0,TEMP_LEM17,TSTM,FOG,FOG3MILE,BLOWSNOW,BLOWSAND,FREZRAIN,HAIL,SNOW,FROZPRCP,SNOWICE,RAIN,ALLPRECP,SMOKHAZE,SANDSNOW,OBSTVISN,U,V,WDIRCOS,WDIRSIN,WDIR16,CALM,LIGHT,WSPDGT12_8,WSPDGT12_3,WSPDGT9_7,WSPDGT17_5,WSPDGT25_2,VSBY_800,VSBY_1600,VSBY_3200,VSBY_4800,GP
ICAO ,KOFF,SAO  ,1948-01-12 06:00:00,1948,1,12,6,0,320.0,2.4,0.2,290.0,4.6,,,,,22000.0,8.0,N,11200,,,,,,,,,,6.196962,4.87,,,,,0,100.0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4.32259,-1.57329,0.3420201029058752,-0.9396926354975091,-1014,0,0,0,0,0,0,0,1,1,1,1,3
ICAO ,KOFF,SAO  ,1948-01-12 07:00:00,1948,1,12,6,0,320.0,2.4,-2.6,290.0,5.1,,,,,22000.0,7.0,N,8000,,,,,,,,,,5.045877,3.97,,,,,0,87.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,4.79243,-1.7443,0.3420201029058752,-0.9396926354975091,-1014,0,0,0,0,0,0,0,1,1,1,1,3
ICAO ,KOFF,SAO  ,1948-01-12 08:00:00,1948,1,12,9,0,320.0,0.8,-1.5,290.0,4.6,,,,,22000.0,7.0,N,11200,,,,,,,,,,5.473223,4.33,,,,,0,87.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4.32259,-1.57329,0.3420201029058752,-0.9396926354975091,-1014,0,0,0,0,0,0,0,1,1,1,1,3

Solution

It doesn't make sense to use as_index=False and to_frame. Use one or the other.

Either remove as_index=False:

(newdat.groupby(['YEAR','MO', 'GP','HR'])['WDIR16'].count()
       .to_frame(name='wndclimodirectionobsqty').reset_index()
)

Or rename after groupby:

(newdat.groupby(['YEAR','MO', 'GP','HR'], as_index=False)['WDIR16'].count()
       .rename(columns={'WDIR16': 'wndclimodirectionobsqty'})
)

Output:

   YEAR  MO  GP  HR  wndclimodirectionobsqty
0  1948   1   3   6                        2
1  1948   1   3   9                        1

Now, if you want to perform several computations and assign custom names in a single shot, consider using groupby.agg with named aggregation:

(newdat.groupby(['YEAR','MO', 'GP','HR'], as_index=False)
       .agg(**{'wndclimodirectionobsqty': ('WDIR16', 'count'),
               'wndclimomeanspeedrate' : ('WSPD', 'mean')
              })
)

Output:

   YEAR  MO  GP  HR  wndclimodirectionobsqty  wndclimomeanspeedrate
0  1948   1   3   6                        2                   4.85
1  1948   1   3   9                        1                   4.60