How Can I Ignore Empty Series When Using Value_counts On A Pandas Groupby?

I've got a DataFrame with the metadata for a newspaper article in each row. I'd like to group these into monthly chunks, then count the values of one column (called type): monthly_

Solution 1:

You'd better give us data sample. Otherwise, it is a little hard to point out the problem. From your code snippet, it seems that the type data for some months is null. You can use apply function on grouped objects and then call unstack function. Here is the sample code that works for me, and the data is randomly generated

s = pd.Series(['positive', 'negtive', 'neutral'], index=[0, 1, 2])
atype = s.loc[np.random.randint(3, size=(150,))]

df = pd.DataFrame(dict(atype=atype.values), index=pd.date_range('2017-01-01',  periods=150))

gp = df.groupby(pd.Grouper(freq='M'))
dfx = gp.apply(lambda g: g['atype'].value_counts()).unstack()

In [75]: dfx
Out[75]: 
            negtive  neutral  positive
2017-01-3113992017-02-28111162017-03-31126132017-04-30812102017-05-3191011

In case there are null values:

In [76]: df.loc['2017-02-01':'2017-04-01', 'atype'] = np.nan
    ...: gp = df.groupby(pd.Grouper(freq='M'))
    ...: dfx = gp.apply(lambda g: g['atype'].value_counts()).unstack()
    ...: 

In [77]: dfx
Out[77]: 
            negtive  neutral  positive
2017-01-3113992017-04-3081292017-05-3191011

Thanks.