I have a data frame something like:
id Col Col2 Date
1 A a 2022-10-03
2 A a 2022-10-03
3 A b 2022-10-03
4 A a 2022-10-04
5 A a 2022-10-04
6 B c 2022-10-03
7 B d 2022-10-03
8 B c 2022-10-03
9 B d 2022-10-04
10 B c 2022-10-04
and i want the count of every unique element in Col2 by date so basically my data frame should be looking like:
id Col Date count
1 A 2022-10-03 2
2 A 2022-10-04 1
3 B 2022-10-03 2
4 B 2022-10-04 2
Explaining it a little better: for Column Col2 for date 2022-10-03 we have 2 unique elements namely a and b. and for date 2022-10-04 we have the just one unique element which is a.
I am not able to think of any approach as such..I tried grouping by but i was not very successful. If anybody can help it will be really appreciated. Thank you.