Pandas Count Unique occurrences by Month with filter

Question

I have some monthly data that I'm trying to summarize using Pandas and I need to count the number of unique entries that occur each month.

I'm new to python / pandas dev, so i think i don't have the right reflexes yet.

I started here: Pandas Count Unique occurrences by Month

My source df look like this :

df = pd.DataFrame({'A' : ['08/10', '08/10', '09/10', '09/10',
                          '09/10', '10/10', '10/10', '10/10'],
                   'Name' : ['one', 'one', 'two', 'three',
                          'two', 'two', 'one', 'three'],
                   'C' : [1, 2, 3, 4, 5, 6, 7, 8],
                   'D' : ['sip:32800', 'sip:38800', 'sip:32800', 'sip:32800',
                          'sip:32800', 'sip:32800', 'sip:32800', 'sip:38800']
                   })

Desired Output:

Name     08/10     09/10     10/10
one       2         0         1
two       0         2         1
three     0         1         1

Optionally, I would like to filter on column "D", so as to count only the items of column 'B' if D contains "sip:32" for instance.

I can get the result using loops and iterations, but it doesn't perform well. I think there is much simpler using .groupby () .value_count (), but my tests are inconclusive.

Your help would be greatly appreciated.

@jezrael Its not the exact dupe. OP also needs to find out count per month. — Mayank Porwal, Nov 18 '20 at 10:01
Dupe shows only `pivot`. OP needs first groupby and `count` then `pivot`. — Mayank Porwal, Nov 18 '20 at 10:03
@MayankPorwal you think `pivot_table` with `aggfunc='size'` or `pivot_table` with `count` ? — jezrael, Nov 18 '20 at 10:04
OP needs this: `df.groupby(['Name', 'A'], as_index=False).count().pivot('Name', 'A', 'C').fillna(0)`. — Mayank Porwal, Nov 18 '20 at 10:04
or crosstab. Btw, added another dupe, if OP need unique values count — jezrael, Nov 18 '20 at 10:06
Got it now. Can also do this: `df.pivot_table(index='Name', columns='A', values='C', aggfunc='count', fill_value=0)`. Thanks for the avoiding the confusion. — Mayank Porwal, Nov 18 '20 at 10:07
Hey, after some test, this cmd : pd.get_dummies(df['row']).T.dot(pd.get_dummies(df['col'])) give me the good result ! Thx a lot — lxl77, Nov 18 '20 at 16:25
Now i need to have a condition to select "name" data. for example i want only "name" for "D".startswith('sip:32'). Should i create a specific df like df2 = df.loc[df["D'].startswith('sip:32')] and apply my pivot on this df2 ? — lxl77, Nov 18 '20 at 16:30
Of course your proposal is good to : df.pivot_table(index='Name', columns='A', values='C', aggfunc='count', fill_value=0) — lxl77, Nov 18 '20 at 16:39

Pandas Count Unique occurrences by Month with filter

0 Answers0