-2
$\begingroup$

I have csv file which have DateTime and application OS name. DateTime have for 1 month ,per hour basis. The csv looks as follows,

DateTime         Apps
1/31/2019 23:54 AppOS
1/31/2019 23:51 AppOS
1/31/2019 23:48 Linux
1/31/2019 22:19 AppOS
1/31/2019 22:15 AppOS
1/31/2019 22:07 Windows
1/31/2019 22:06 AppOS
1/31/2019 22:05 Windows
1/31/2019 22:04 Windows
1/31/2019 22:03 AppOS
1/4/2019 16:54  AppOS
1/4/2019 16:54  Windows
1/4/2019 16:54  AppOS
1/4/2019 16:53  Linux
1/4/2019 16:53  AppOS
1/4/2019 16:52  AppOS
1/4/2019 16:51  Windows
1/4/2019 16:50  AppOS
1/4/2019 16:49  Windows
1/4/2019 16:48  Windows
1/4/2019 16:46  AppOS
1/4/2019 16:46  AppOS
1/4/2019 16:45  Windows

My problem is how to get the number of occurances of 'Apps' per hour.

I require the result as follows,

DateTime              AppOS  Linux  Windows
2019-1-31 23:00:00      2      1       0
          23:59:00

2019-01031 22:00:00
           22:59:00    2        3       1

So, I need for every hour, what is the occurrence of the OS. I tried as follows,

df1_1day = df1[(df1['DateTime'] > '2019-01-01 23:00:00') & (df1['DateTime'] <= '2019-01-01 23:59:00')]
#print (df1[(df1['DateTime'] > '2019-01-01 00:00:00') & (df1['DateTime'] <= '2019-01-01 23:59:00')])
print (df1_1day)
print ("*********** CHECK 2 **************")
#dg = df1.groupby(pd.Grouper(key='DateTime', freq='1H'))['Apps'].count()  # groupby each 1 month
#df1_perhr1 = df1_1day.between_time('23:00:00', '23:59:00')
#print (df1_perhr1)
#print (dg)
print (df1_1day.groupby('Apps').count())

Thought to try with for loop for whole month, but feel it may not work. So, please help on this to get the required values. Thanks.

$\endgroup$
0
$\begingroup$

You could do the following :

  1. Given that you have the dataframe and a date object present in one of the columns, you could generate the following columns : 'day' , 'month' , 'year', 'hour' . I would suggest using delorean to parse the dates and get the values.
  2. Given these columns, you could do simply groupby 'hour' or other fields and get the counts you are looking for.
$\endgroup$
1
  • $\begingroup$ Yes..THe problem is, I need for each day with hour basis such as 00:00:00 to 23:59:00 hrs..so for each day , I need to collect the apps count for 24 hrs..Finally, each hour of 24 hr occurance needs to be capture. $\endgroup$
    – sundarr
    May 7 '19 at 13:34
-1
$\begingroup$

The solution is as follows.

dataframe_new = df1.groupby([pd.Grouper(key='DateTime',freq='H'),df.Apps]).size().reset_index(name='Occurances')
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.