2

Suppose if I have a data like the following,with Name, class and Date

Name    class   Date
A   7th grade   1/1/2016
A   7th grade   1/2/2016
A   7th grade   1/3/2016
A   7th grade   1/4/2016
A   7th grade   1/5/2016
A   7th grade   1/6/2016
A   7th grade   1/7/2016
B   8th grade   1/8/2016
B   8th grade   1/9/2016
B   8th grade   1/10/2016
C   9th grade   1/11/2016
C   9th grade   1/12/2016
C   9th grade   1/13/2016
C   9th grade   1/14/2016
C   9th grade   1/15/2016
C   9th grade   1/16/2016
C   9th grade   1/17/2016
C   9th grade   1/18/2016
C   9th grade   1/19/2016
C   9th grade   1/20/2016
C   9th grade   1/21/2016
C   9th grade   1/22/2016

I am looking for a output which would give me the count of values for each name, their respective grade and the earliest date and the latest date. My output would be,

Name grade   count earlydate latestdate
A   7thgrade   7    1/1/2016  1/7/2016
B   8th grade  3    1/8/2016  1/10/2016
C   9th grade  12   1/11/2016 1/22/2016

I am able to find the counts of each name by,

data.groupby('name','grade').count()
or
data.groupby('name','grade').size()

But not able to find the early date and latest date inside the date column.

Can anybody help me in dng this?

haimen
  • 1,985
  • 7
  • 30
  • 53
  • What kind of database are you using ? Try using Joins, and MIN/MAX functions in sql – jood Feb 26 '16 at 00:05
  • I have this as a python dataframe . Need to do this in python – haimen Feb 26 '16 at 00:05
  • http://stackoverflow.com/questions/23178129/getting-min-and-max-dates-from-a-pandas-dataframe – jood Feb 26 '16 at 00:07
  • @clemkoa Thanks for the link. but in that link, the dates are as index and not as column and also doesn't return a result like that I want. I want a dataframe here, which would give those details as shown – haimen Feb 26 '16 at 00:12
  • 1
    ok so maybe http://stackoverflow.com/questions/25024797/max-and-min-date-in-pandas-groupby ? – jood Feb 26 '16 at 00:18
  • @clemkoa Thanks! this works – haimen Feb 26 '16 at 00:37
  • Ok I'll try to post it as an answer then, so we can close the question – jood Feb 26 '16 at 00:41

1 Answers1

3

Max and Min date in pandas groupby

Something like this maybe:

data.groupby('name','grade').agg({'date' : [np.min, np.max]}).count()
Community
  • 1
  • 1
jood
  • 2,188
  • 2
  • 21
  • 32