I'm trying to reduce meterological data using pandas 0.13.1. I have a large dataframe of floats. Thanks to this answer I have grouped the data into half-hour intervals most efficiently. I am using groupby+apply instead of resample because of the need to examine multiple columns.
>>> winddata
sonic_Ux sonic_Uy sonic_Uz
TIMESTAMP
2014-04-30 14:13:12.300000 0.322444 2.530129 0.347921
2014-04-30 14:13:12.400000 0.357793 2.571811 0.360840
2014-04-30 14:13:12.500000 0.469529 2.400510 0.193011
2014-04-30 14:13:12.600000 0.298787 2.212599 0.404752
2014-04-30 14:13:12.700000 0.259310 2.054919 0.066324
2014-04-30 14:13:12.800000 0.342952 1.962965 0.070500
2014-04-30 14:13:12.900000 0.434589 2.210533 -0.010147
... ... ...
[4361447 rows x 3 columns]
>>> winddata.dtypes
sonic_Ux float64
sonic_Uy float64
sonic_Uz float64
dtype: object
>>> hhdata = winddata.groupby(TimeGrouper('30T')); hhdata
<pandas.core.groupby.DataFrameGroupBy object at 0xb440790c>
I want to use math.atan2 on the 'Ux/Uy' columns and am having trouble successfully applying any function. I get tracebacks about attribute ndim:
>>> hhdata.apply(lambda g: atan2(g['sonic_Ux'].mean(), g['sonic_Uy'].mean()))
Traceback (most recent call last):
<<snip>>
File "/usr/local/lib/python2.7/dist-packages/pandas-0.13.1-py2.7-linux-i686.egg/pandas/tools/merge.py", line 989, in __init__
if not 0 <= axis <= sample.ndim:
AttributeError: 'float' object has no attribute 'ndim'
>>>
>>> hhdata.apply(lambda g: 42)
Traceback (most recent call last):
<<snip>>
File "/usr/local/lib/python2.7/dist-packages/pandas-0.13.1-py2.7-linux-i686.egg/pandas/tools/merge.py", line 989, in __init__
if not 0 <= axis <= sample.ndim:
AttributeError: 'int' object has no attribute 'ndim'
I can loop through the groupby object just fine. I can also wrap the result in a Series or DataFrame but wrapping values requires adding an index which is tuple-ed with my original index. Following the advice of this answer to remove the duplicate index didn't work as expected. Since I can reproduce the problem and solution from that question, I wonder if believe it's behaving differently because I am grouping on a DateTimeIndex an index.
>>> for name, g in hhdata:
... print name, atan2(g['sonic_Ux'].mean(), g['sonic_Uy'].mean()), ' wd'
...
2014-04-30 14:00:00 0.13861912975 wd
2014-04-30 14:30:00 0.511709085506 wd
2014-04-30 15:00:00 -1.5088990774 wd
2014-04-30 15:30:00 0.13200013186 wd
<<snip>>
>>> def winddir(g):
... return pd.Series(atan2( np.mean(g['sonic_Ux']), np.mean(g['sonic_Uy']) ), name='wd')
...
>>> hhdata.apply(winddir)
2014-04-30 14:00:00 0 0.138619
2014-04-30 14:30:00 0 0.511709
2014-04-30 15:00:00 0 -1.508899
2014-04-30 15:30:00 0 0.132000
...
2014-05-05 14:00:00 0 -2.551593
2014-05-05 14:30:00 0 -2.523250
2014-05-05 15:00:00 0 -2.698828
Name: wd, Length: 243, dtype: float64
>>> hhdata.apply(winddir).index[0]
(Timestamp('2014-04-30 14:00:00', tz=None), 0)
>>> def winddir(g):
... return pd.DataFrame({'wd':atan2(g['sonic_Ux'].mean(), g['sonic_Uy'].mean())}, index=[g.name])
...
>>> hhdata.apply(winddir)
wd
2014-04-30 14:00:00 2014-04-30 14:00:00 0.138619
2014-04-30 14:30:00 2014-04-30 14:30:00 0.511709
2014-04-30 15:00:00 2014-04-30 15:00:00 -1.508899
2014-04-30 15:30:00 2014-04-30 15:30:00 0.132000
...
[243 rows x 1 columns]
>>> hhdata.apply(winddir).index[0]
(Timestamp('2014-04-30 14:00:00', tz=None), Timestamp('2014-04-30 14:00:00', tz=None))
>>>
>>> tsfast.groupby(TimeGrouper('30T')).apply(lambda g:
... Series({'wd': atan2(g.sonic_Ux.mean(), g.sonic_Uy.mean()),
... 'ws': np.sqrt(g.sonic_Ux.mean()**2 + g.sonic_Uy.mean()**2)}))
2014-04-30 14:00:00 wd 0.138619
ws 1.304311
2014-04-30 14:30:00 wd 0.511709
ws 0.143762
2014-04-30 15:00:00 wd -1.508899
ws 0.856643
...
2014-05-05 14:30:00 wd -2.523250
ws 3.317810
2014-05-05 15:00:00 wd -2.698828
ws 3.279520
Length: 486, dtype: float64
Edited: Notice the extra column when a Series or DataFrame is returned? And following the formula of the previously linked answer results in a hierarchical index?
My original question was: what kind of value should be returned from my applyed function so that a groupby-apply operation results in a 1-column DataFrame or Series with a length equal to number of groups and group names (e.g. Timestamps) used as index values?
After feedback & further investigation, what I am really asking is why does grouping on an Index behave differently than grouping on a column? Observe changing the DatetimeIndex to a column with string values to achieve equivalent grouping as with TimeGrouper('30T') results in the behavior I was expecting:
>>> winddata.index.name = 'WASINDEX'
>>> data2 = winddata.reset_index()
>>> def to_hh(x): # <-- big hammer
... ts = x.isoformat()
... return ts[:14] + ('30:00' if int(ts[14:16]) >= 30 else '00:00')
...
>>> data2['TS'] = data2['WASINDEX'].apply(lambda x: to_hh(x))
>>> wd = data2.groupby('TS').apply(lambda df: Series({'wd': np.arctan2(df.x.mean(), df.y.mean())}))
>>> type(wd)
pandas.core.frame.DataFrame
>>> wd.columns
Index([u'wd'], dtype=object)
>>> wd.index
Index([u'2014-04-30T14:00:00', u'2014-04-30T14:30:00', <<snip>> dtype=object)
