Sitz Blogz Sitz Blogz - 4 months ago 21
Python Question

Multiple Count and Median Values from a Dataframe

I am trying to perform several operations in one program at same time.
I have a data-frame that has

Dates
of which I have no clue of start and end and I want to find:


  1. Total number of days the data-set has

  2. Total number of hours

  3. Median of the Count

  4. Write a separate output for median per day/date.

  5. If possible Median-of-Median in most possible simple way.



Input: Few rows from the a large file of GB size

2004-01-05,16:00:00,17:00:00,Mon,10766,656
2004-01-05,17:00:00,18:00:00,Mon,12223,670
2004-01-05,18:00:00,19:00:00,Mon,12646,710
2004-01-05,19:00:00,20:00:00,Mon,19269,778
2004-01-05,20:00:00,21:00:00,Mon,20504,792
2004-01-05,21:00:00,22:00:00,Mon,16553,783
2004-01-05,22:00:00,23:00:00,Mon,18944,790
2004-01-05,23:00:00,00:00:00,Mon,17534,750
2004-01-06,00:00:00,01:00:00,Tue,17262,747
2004-01-06,01:00:00,02:00:00,Tue,19072,777
2004-01-06,02:00:00,03:00:00,Tue,18275,785
2004-01-06,03:00:00,04:00:00,Tue,13589,757
2004-01-06,04:00:00,05:00:00,Tue,16053,735


The start and end date are NOT known.

Edit:
Expected Output:1 will have only one row of results

days,hours,median,median-of-median
2,17262,13,17398


Median-of-Median is the median value of
median
column from output 2

Expected Output:2, will have medians of every date which are to used to find median-of-median

date,median
2004-01-05,17534
2004-01-06,17262


Code:

import pandas as pd
from datetime import datetime

df = pd.read_csv('one_hour.csv')
df.columns = ['date', 'startTime', 'endTime', 'day', 'count', 'unique']

date_count = df.count(['date'])
all_median = df.median(['count'])
all_hours = df.count(['startTime'])
med_med = df.groupby(['date','count']).median()

print date_count
print all_median
print all_hours

stats = ['date_count', 'all_median', 'all_hours', 'median-of-median']
stats.to_csv('stats_all.csv', index=False)

med_med.to_csv('med_day.csv', index=False, header=False)


Obviously the code does not give the result as it is supposed to.

The error is shown below.

Error:

Traceback (most recent call last):
File "day_median.py", line 8, in <module>
all_median = df.median(['count'])
File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 5310, in stat_func
numeric_only=numeric_only)
File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 4760, in _reduce
axis = self._get_axis_number(axis)
File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 308, in _get_axis_number
axis = self._AXIS_ALIASES.get(axis, axis)
TypeError: unhashable type: 'list'

Answer

IIUC maybe help change:

date_count = df.count(['date'])
all_median = df.median(['count'])
all_hours = df.count(['startTime'])

to:

date_count = df['date'].count()
all_median = df['count'].median()
all_hours = df['startTime'].count()

print (date_count)
print (all_median)
print (all_hours)
13
17262.0
13

if need count statistics from columns date, count and startTime.

EDIT by comment:

If need count unique values of column use nunique:

date_count = df['date'].nunique()
print (date_count)
2

DataFrame stats:

cols = ['date_count', 'all_median', 'all_hours']
stats = pd.DataFrame([[date_count, all_median, all_hours]], columns = cols)
print (stats)
   date_count  all_median  all_hours
0           2     17262.0         13
Comments