<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Pandas Cookbook
Step1:  Idioms
If-then-else
Override calculations and reassign variables
Step2:  Execute an if-then statement on one column
Step3:  If-then with assignment to 2 columns
Step4:  Now you can perform another operation on the first row
Step5:  You can also use pandas after setting up a mask
Step6:  Use numpy's where() to perform an if-then-else operation.
In other words, the conditional creation of a DataFrame column
Step7:  Splitting
Split a frame based on a boolean value
Step8:  Building Criteria
Select with multi-column criteria
Step9:  Select the rows with data that's closest to a target value
Step10:  Dynamically reduce a list of criteria using binary operators
Step11:  If you want to hard code a solution
Step12:  You may want to work with a list of dynamically built criteria
Step13:  Selection
DataFrames
Ladies and gentleman, The Indexing Documentation
Pandas using row labels in boolean indexing
Step14:  Use loc for label-oriented slicing and iloc for positional slicing
Step15:  There are two explicit slicing methods and an available third option
Step16:  Ambiguity arises when an index consists of integers with a non-zero start or non-unit increment.
Step17:  Using the inverse operator ~ to take the complement of a mask
Step18:  Panels
Extend a panel frame by transposing, adding a new dimension, and transposing back to the original dimensions
Step19:  New Columns
Create new columns using applymap()
Step20:  Keep other columns when using min() with groupby
Step21:  MultiIndexing
Ladies and Gentleman, The MultiIndexing Documentation.
Creating a multi-index from a labeled frame
Step22:  Arithmetic
Performing arithmetic with a multi-index that needs broadcasting
Step23:  Slicing
Slicing a multi-index with xs
Step24:  Navigating the indices in a MultiIndex
Another way to slice a multi-index using the xs() method.
Step25:  Having fun yet?
Check out this Stack Overflow post on how to tinker with the numbers on different levels of a MultiIndex.
Sorting
Using a MultiIndex, sort by a specific column or an ordered list of columns
Step26:  Levels
Prepending a level to a multiindex
Flatten hiererchical columns
Missing Data
Ladies and Gentleman, The Missing Data Documentation.
Fill-forward a reversed timeseries
Step27:  cumsum reset at NaN values
Replace
Using replace with backrefs
Grouping
Gentleman and Ladies, The Group By Docs.
Basic grouping using apply
Unlike agg(), apply's callable is passed a sub-DataFrame which gives you access to all of the columns.
Step28:  Now list the size of the animals with the highest weight.
Step29:  Grouping using get_group()
Step30:  Apply to different items in a group
Step31:  Expanding Apply
Step32:  Replacing some values with the mean of the rest of a group
Step33:  Sort groups by their aggregated data
Step34:  Create multiple aggregated columns
Step35:  Create a value counts column and reassign back to the DataFrame
Step36:  Shift groups of the values in a column based on the index
Step37:  Select row with maximum value from each group
Step38:  Grouping like Python's itertools.groupby method
Step39:  Expanding Data
Alignment and to-date
Splitting
Splitting a frame
Create a list of datafrmaes, split using delineation based on logic included in rows.
Step40:  Pivot
Here are your friendly neighborhood Pivot docs
Partial sums and subtotals
Step41:  Frequency tables like plyr in R
The plyr homepage.
plyr is a set of tools for a common set of problems
Step42:  Plot pandas DataFrame with year over year data
Step43:  Apply
Rolling Apply to Organize - Turning embedded lists into a multi-index frame
Step44:  Rolling Apply with a DataFrame returning a Series
Rolling Apply to multiple columns where the function calculates a Series before a Scalar from the series is returned.
Step45:  Rolling Apply with a DataFrame returning a Scalar
Rolling Apply to multiple columns where the function returns a scalar, the Volume Weighted Average Price
Step46:  Timeseries
But first, a quick demonstration of how to create example (mock) data.
To create a dataframe from every combination of some given values, like R's expand.grid() function, we can create a dict where the keys are the column names and the values are lists of the data values.
Step47:  Now, back to the Timeseries stuff.
Here is the pandas Time Series / Date functionality documentation.
Let's start by dropping the rows that aren't inside the time range that we want.
Between times
Step48:  Using indexer between time
Create a mask to only consider the rows where the index falls within a certain range.
Step49:  One trick to remember is that by setting start_time to be later than end_time, you can get the times that are not between the two times.
This also works for pandas.DataFrame.between_time().
Constructing a datetime range that excludes weekends and includes only certain times
Generate sequential timestamp with jump.
In other words, construct a range of desired datetimes and reindex to it
Step50:  Vectorized Lookup
How to rearrange a Python pandas DataFrame
Dealing with duplicates when reindexing a timeseries to a specified frequency
Calculate the first day of the month for each entry in a DatetimeIndex
Step51:  Resampling
The Resample Documentation
Step52:  Using Grouper instead of TimeGrouper for time grouping of values
Time grouping with some missing values
Step53:  Grouping using a MultiIndex
Say you have a MultiIndex pandas DataFrame, where the first level index is a group and the second level index is time.
Within each group, resample the daily frequency, taking the average of intraday observations.
Step54:  Using TimeGrouper and another grouping to create subgroups, then apply a custom function
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
Explanation: Pandas Cookbook
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7],
                  'BBB' : [10,20,30,40],
                  'CCC' : [100,50,-30,-50]})
df
Explanation: Idioms
If-then-else
Override calculations and reassign variables:
End of explanation
# If AAA >= 5, BBB = -1
df.loc[df.AAA >= 5, 'BBB'] = -1; df
Explanation: Execute an if-then statement on one column:
End of explanation
df.loc[df.AAA >= 5, ['BBB','CCC']] = 555; df
Explanation: If-then with assignment to 2 columns:
End of explanation
df.loc[df.AAA < 5, ['BBB', 'CCC']] = 2000; df
Explanation: Now you can perform another operation on the first row:
End of explanation
df_mask = pd.DataFrame({'AAA' : [True] * 4, 'BBB' : [False] * 4, 'CCC' : [True,False] * 2})
df_mask
df.where(df_mask, -1000)
Explanation: You can also use pandas after setting up a mask:
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,-50]}); df
# New column -- if AAA > 5, then high, else low:
df['logic'] = np.where(df['AAA'] > 5,'high','low'); df
Explanation: Use numpy's where() to perform an if-then-else operation.
In other words, the conditional creation of a DataFrame column:
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,-50]}); df
df_low = df[df.AAA <= 5]; df_low
df_high = df[df.AAA > 5]; df_high
Explanation: Splitting
Split a frame based on a boolean value:
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40],'CCC' : [100,50,-30,-50]}); df
# And operation without assignment:
newseries = df.loc[(df['BBB'] < 25) & (df['CCC'] >= -40), 'AAA']; newseries
# Or operation without assignment:
newseries = df.loc[(df['BBB'] > 25) | (df['CCC'] >= -40), 'AAA']; newseries
# Or operation with assignment modifies the dataframe:
df.loc[(df['BBB'] > 25) | (df['CCC'] >= 75), 'AAA'] = 0.1; df
Explanation: Building Criteria
Select with multi-column criteria:
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40],'CCC' : [100,50,-30,-50]}); df
aValue = 43.0
df.loc[(df.CCC-aValue).abs().argsort()]
Explanation: Select the rows with data that's closest to a target value:
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,-50]}); df
Crit1 = df.AAA <= 5.5; Crit1
Crit2 = df.BBB == 10.0; Crit2
Crit3 = df.CCC > -40.0; Crit3
Explanation: Dynamically reduce a list of criteria using binary operators
End of explanation
AllCrit = Crit1 & Crit2 & Crit3; AllCrit
Explanation: If you want to hard code a solution:
End of explanation
CritList = [Crit1,Crit2,Crit3]; CritList
import functools
AllCrit = functools.reduce(lambda x,y: x & y, CritList); AllCrit
df[AllCrit]
Explanation: You may want to work with a list of dynamically built criteria:
End of explanation
# Using both row labels and value conditionals:
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,50]}); df
df[(df.AAA <= 6) & (df.index.isin([0,2,4]))]
Explanation: Selection
DataFrames
Ladies and gentleman, The Indexing Documentation
Pandas using row labels in boolean indexing
End of explanation
data = {'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,50]}
pd.DataFrame(data)
df = pd.DataFrame(data=data,index=['foo','bar','goo','car']); df
Explanation: Use loc for label-oriented slicing and iloc for positional slicing
End of explanation
# Label-oriented:
df.loc['bar' : 'car']
# Positional-oriented:
df.iloc[0:3]
Explanation: There are two explicit slicing methods and an available third option:
1. Positional-oriented ( Python slicing style : exclusive end )
2. Label-oriented ( Non-Python slicing style : inclusive end )
3. General ( Either slicing style : depends on slicing on labels or positions )
End of explanation
# Begin index at 1 instead of 0
df2 = pd.DataFrame(data=data,index=[1,2,3,4]); df2
df2.iloc[1:3]
df2.loc[1:3]
Explanation: Ambiguity arises when an index consists of integers with a non-zero start or non-unit increment.
End of explanation
df = pd.DataFrame({'AAA' : [4,5,6,7], 'BBB' : [10,20,30,40], 'CCC' : [100,50,-30,-50]}); df
df[~((df.AAA <= 6) & (df.index.isin([0,2,4])))]
Explanation: Using the inverse operator ~ to take the complement of a mask
End of explanation
rng = pd.date_range('1/1/2018',periods=100,freq='D')
data = np.random.randn(100,4)
print(rng[:5])
print()
print(data[:5])
cols = ['A','B','C','D']; cols
# Another reason why Python is so wonderful:
df1, df2, df3 = pd.DataFrame(data, rng, cols), pd.DataFrame(data, rng, cols), pd.DataFrame(data, rng, cols)
df1[:5]
pf = pd.Panel({'df1':df1,'df2':df2,'df3':df3});pf
pf.loc[:,:,'F'] = pd.DataFrame(data,rng,cols);pf
Explanation: Panels
Extend a panel frame by transposing, adding a new dimension, and transposing back to the original dimensions
End of explanation
df = pd.DataFrame({'AAA' : [1,2,1,3], 'BBB' : [1,1,2,2], 'CCC' : [2,1,3,1]}); df
source_cols = df.columns  # any subset would work
source_cols
new_cols = [str(x) + "_cat" for x in source_cols]
new_cols
categories = { 1 : 'Alpha', 2 : 'Beta', 3 : 'Charlie' }
df[new_cols] = df[source_cols].applymap(categories.get); df
Explanation: New Columns
Create new columns using applymap()
End of explanation
df = pd.DataFrame({'AAA' : [1,1,1,2,2,2,3,3], 'BBB' : [2,1,3,4,5,1,2,3]}); df
# Method 1 -- use idxmin() to get the index of the mins:
df.loc[df.groupby('AAA')['BBB'].idxmin()]
# Method 2 -- sort the entries, then take the first of each.
# Notice that the resulting dataframe is the same except for the index.
df.sort_values(by='BBB').groupby('AAA', as_index=False).first()
Explanation: Keep other columns when using min() with groupby
End of explanation
df = pd.DataFrame({'Row' : [0,1,2],
                 'one_X' : [1.1,1.1,1.1],
                 'one_Y' : [1.2,1.2,1.2],
                 'two_X' : [1.11,1.11,1.11],
                 'two_Y' : [1.22,1.22,1.22]})
df
# As a labeled index:
df = df.set_index('Row'); df
# With hierarchical columns:
df.columns = pd.MultiIndex.from_tuples([tuple(c.split('_')) for c in df.columns]); df
# Now you can stack and reset them:
df = df.stack(0).reset_index(1); df
# Notice how the label 'level_1' got added for you in the DataFrame above.
# You can fix your own labels, too:
df.columns = ['Sample','All_X', 'All_Y']; df
Explanation: MultiIndexing
Ladies and Gentleman, The MultiIndexing Documentation.
Creating a multi-index from a labeled frame
End of explanation
cols = pd.MultiIndex.from_tuples([(x,y) for x in ['A','B','C'] for y in ['O','I']]); cols
df = pd.DataFrame(cols); df
df = pd.DataFrame(np.random.randn(2,6),index=['n','m'],columns=cols); df
df = df.div(df['C'],level=1); df
df = df.div(df['B'],level=1); df
Explanation: Arithmetic
Performing arithmetic with a multi-index that needs broadcasting
End of explanation
coords = [('AA','one'),('AA','six'),('BB','one'),('BB','two'),('BB','six')]
df = pd.DataFrame(coords); df
index = pd.MultiIndex.from_tuples(coords); index
df = pd.DataFrame(index); df
df = pd.DataFrame([11,22,33,44,55],index,['MyData']); df
# Take the cross section of the 1st level and 1st axis
df.xs('BB',level=0,axis=0)
# Now take the 2nd level of the first axis
df.xs('six',level=1,axis=0)
Explanation: Slicing
Slicing a multi-index with xs
End of explanation
import itertools
index = list(itertools.product(['Ada','Quinn','Violet'],['Comp','Math','Sci'])); index
headr = list(itertools.product(['Exams','Labs'],['I','II'])); headr
indx = pd.MultiIndex.from_tuples(index,names=['Student','Course']); indx
pd.DataFrame(indx)
# Notice that the labelsin the cols multi-index are unnamed
cols = pd.MultiIndex.from_tuples(headr); cols
data = [[70+x+y+(x*y)%3 for x in range(4)] for y in range(9)]; data
df = pd.DataFrame(data,indx,cols); df
# https://stackoverflow.com/questions/38208416/colon-none-slicenone-in-numpy-array-indexers
All = slice(None); All
df.loc['Violet']
df.loc['Violet']
df.loc[(All,'Math'),All]
df.loc[(slice('Ada','Quinn'),'Math'),All]
df.loc[(All,'Math'),('Exams')]
df.loc[(All,'Math'),(All,'II')]
Explanation: Navigating the indices in a MultiIndex
Another way to slice a multi-index using the xs() method.
End of explanation
df.sort_values(by=('Labs','II'),ascending=False)
Explanation: Having fun yet?
Check out this Stack Overflow post on how to tinker with the numbers on different levels of a MultiIndex.
Sorting
Using a MultiIndex, sort by a specific column or an ordered list of columns
End of explanation
df = pd.DataFrame(np.random.randn(6,1),
                 index=pd.date_range('2018-08-01',periods=6,freq='B'),
                 columns=list('A'))
df
df.loc[df.index[3],'A'] = np.nan; df
df.reindex(df.index[::-1]).ffill()
Explanation: Levels
Prepending a level to a multiindex
Flatten hiererchical columns
Missing Data
Ladies and Gentleman, The Missing Data Documentation.
Fill-forward a reversed timeseries:
End of explanation
df = pd.DataFrame({'animal' : 'cat dog cat fish dog cat cat'.split(),
                  'size' : list('SSMMMLL'),
                  'weight' : [8,10,11,1,20,12,12],
                  'adult' : [False] * 5 + [True] * 2})
df
Explanation: cumsum reset at NaN values
Replace
Using replace with backrefs
Grouping
Gentleman and Ladies, The Group By Docs.
Basic grouping using apply
Unlike agg(), apply's callable is passed a sub-DataFrame which gives you access to all of the columns.
End of explanation
df.groupby('animal').apply(lambda subf: subf['size'][subf['weight'].idxmax()])
Explanation: Now list the size of the animals with the highest weight.
End of explanation
gb = df.groupby(['animal'])
gb.get_group('cat')
Explanation: Grouping using get_group()
End of explanation
def GrowUp(x):
    avg_weight = sum(x[x['size'] == 'S'].weight * 1.5)
    avg_weight += sum(x[x['size'] == 'M'].weight * 1.25)
    avg_weight += sum(x[x['size'] == 'L'].weight)
    avg_weight /=len(x)
    return pd.Series(['L',avg_weight,True],index=['size', 'weight', 'adult'])
expected_df = gb.apply(GrowUp); expected_df
Explanation: Apply to different items in a group
End of explanation
S = pd.Series([i / 100.0 for i in range(1,11)]); S
def CumRet(x,y):
    return x * (1 + y)
def Red(x):
    return functools.reduce(CumRet,x,1.0)
S.expanding().apply(Red)
Explanation: Expanding Apply
End of explanation
df = pd.DataFrame({'A' : [1,1,2,2], 'B' : [1,-1,1,2]})
gb = df.groupby('A'); gb
def replace(g):
    # Select all values less than zero:
    mask = g < 0
    # Replace those values with the mean of the other values that are positive:
    g.loc[mask] = g[~mask].mean()
    return g
gb.transform(replace)
Explanation: Replacing some values with the mean of the rest of a group
End of explanation
df = pd.DataFrame({'code' : ['foo','bar','baz'] * 2,
                  'data' : [0.16,-0.21,0.33,0.45,-0.59,0.62],
                  'flag' : [False,True] * 3})
df
code_groups = df.groupby('code'); code_groups
agg_n_sort_order = code_groups[['data']].transform(sum).sort_values(by='data'); agg_n_sort_order
sorted_df = df.loc[agg_n_sort_order.index]; sorted_df
Explanation: Sort groups by their aggregated data
End of explanation
rng = pd.date_range(start="2018-7-10",periods=10,freq='2min'); rng
ts = pd.Series(data=list(range(10)),index=rng); ts
def MyCust(x):
    if len(x) > 2:
        return x[1] * 1.234
    return pd.NaT
mhc = {'Mean' : np.mean, 'Max' : np.max, 'Custom' : MyCust}
print(ts.resample('5min').apply(mhc), ts)
Explanation: Create multiple aggregated columns
End of explanation
df = pd.DataFrame({'Color' : 'Red Red Red Blue'.split(),
                  'Value' : [100, 150, 50, 50]})
df
df['Counts'] = df.groupby(['Color']).transform(len); df
Explanation: Create a value counts column and reassign back to the DataFrame
End of explanation
df = pd.DataFrame({u'line_race' : [10, 10, 8, 10, 10, 8],
                  u'beyer' : [99, 102, 103, 103, 88, 100]},
                 index=[u'Last Gunfighter', u'Last Gunfighter', u'Last Gunfighter',
                       u'Paynter', u'Paynter', u'Paynter'])
df
df['beyer_shifted'] = df.groupby(level=0)['beyer'].shift(1); df
Explanation: Shift groups of the values in a column based on the index
End of explanation
df = pd.DataFrame({'host' : ['other','other','that','this','this'],
                  'service' : ['mail','web','mail','mail','web'],
                  'no' : [1,2,1,2,1]}).set_index(['host', 'service'])
df
mask = df.groupby(level=0).agg('idxmax'); mask
df_count = df.loc[mask['no']].reset_index(); df_count
Explanation: Select row with maximum value from each group
End of explanation
df = pd.DataFrame([0,1,0,1,1,1,0,1,1], columns=['A']); df
df.A.groupby((df.A != df.A.shift()).cumsum()).groups
df.A.groupby((df.A != df.A.shift()).cumsum()).cumsum()
Explanation: Grouping like Python's itertools.groupby method
End of explanation
df = pd.DataFrame(data={'Case' : ['A','A','A','B','A','A','B','A','A'],
                       'Data' : np.random.randn(9)}); df
dfs = list(zip(*df.groupby((1*(df['Case']=='B')).cumsum().rolling(window=3,min_periods=1).median())))[-1]
dfs
dfs[0]
dfs[1]
dfs[2]
Explanation: Expanding Data
Alignment and to-date
Splitting
Splitting a frame
Create a list of datafrmaes, split using delineation based on logic included in rows.
End of explanation
df = pd.DataFrame(data={'Province' : ['ON','QC','BC','AL','AL','MN','ON'],
                        'City' : ['Toronto','Montreal','Vancouver','Calgary','Edmonton','Winnipeg','Windsor'],
                        'Sales' : [13,6,16,8,4,3,1]})
df
table = pd.pivot_table(df,values=['Sales'],index=['Province'],columns=['City'],aggfunc=np.sum,margins=True); table
table.stack('City')
Explanation: Pivot
Here are your friendly neighborhood Pivot docs
Partial sums and subtotals
End of explanation
grades = [48,99,75,80,42,80,72,68,36,78]
df = pd.DataFrame( {'ID': ["x%d" % r for r in range(10)],
                    'Gender' : ['F', 'M', 'F', 'M', 'F', 'M', 'F', 'M', 'M', 'M'],
                    'ExamYear': ['2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'],
                    'Class': ['algebra', 'stats', 'bio', 'algebra', 'algebra', 'stats', 'stats', 'algebra', 'bio', 'bio'],
                    'Participated': ['yes','yes','yes','yes','no','yes','yes','yes','yes','yes'],
                    'Passed': ['yes' if x > 50 else 'no' for x in grades],
                    'Employed': [True,True,True,False,False,False,False,True,True,False],
                    'Grade': grades})
df
df.groupby('ExamYear').agg({'Participated' : lambda x: x.value_counts()['yes'],
                           'Passed' : lambda x: sum(x == 'yes'),
                           'Employed' : lambda x: sum(x),
                           'Grade' : lambda x : sum(x) / len(x)})
Explanation: Frequency tables like plyr in R
The plyr homepage.
plyr is a set of tools for a common set of problems:
You need to split up a big data structure into homogeneous pieces, apply a function to each piece and then combine all the results back together.
End of explanation
df = pd.DataFrame({'value' : np.random.randn(36)},
                 index=pd.date_range('2011-01-01',freq='M',periods=36))
df
pv = pd.pivot_table(df,index=df.index.month,
              columns=df.index.year,
              values='value',
              aggfunc='sum')
pv
from matplotlib import pyplot as plt
pv.plot()
plt.show()
Explanation: Plot pandas DataFrame with year over year data
End of explanation
df = pd.DataFrame(data={'A' : [[2,4,8,16],[100,200],[10,20,30]], 
                        'B' : [['a','b','c'],['jj','kk'],['ccc']]},
                  index=['I','II','III'])
df
def SeriesFromSubList(aList):
    return pd.Series(aList)
organized_df = pd.concat(dict([(ind,row.apply(SeriesFromSubList)) for ind,row in df.iterrows()])); organized_df
Explanation: Apply
Rolling Apply to Organize - Turning embedded lists into a multi-index frame
End of explanation
df = pd.DataFrame(data=np.random.randn(2000,2)/10000,
                 index=pd.date_range('2001-01-01',periods=2000),
                 columns=['A','B'])
df.head()
def gm(aDF,Const):
    v = ((((aDF.A+aDF.B)+1).cumprod())-1)*Const
    return (aDF.index[0],v.iloc[-1])
S = pd.Series(dict([gm(df.iloc[i:min(i+51,len(df)-1)],5) for i in range(len(df)-50)])); S
Explanation: Rolling Apply with a DataFrame returning a Series
Rolling Apply to multiple columns where the function calculates a Series before a Scalar from the series is returned.
End of explanation
rng = pd.date_range(start='2014-01-01',periods=100)
rng[:5]
df = pd.DataFrame({'Open' : np.random.randn(len(rng)),
                  'Close' : np.random.randn(len(rng)),
                  'Volume' : np.random.randint(100,2000,len(rng))},index=rng)
df.describe()
def vwap(bars) : return ((bars.Close*bars.Volume).sum()/bars.Volume.sum())
window = 5
s = pd.concat([(pd.Series(vwap(df.iloc[i:i+window]),
                          index=[df.index[i+window]]))
               for i in range(len(df)-window)])
s.round(2)
Explanation: Rolling Apply with a DataFrame returning a Scalar
Rolling Apply to multiple columns where the function returns a scalar, the Volume Weighted Average Price
End of explanation
def expand_grid(data_dict):
    rows = itertools.product(*data_dict.values())
    return pd.DataFrame.from_records(rows, columns=data_dict.keys())
df = expand_grid({'height' : [60,70],
                 'weight' : [100,140,180],
                 'sex' : ['Male','Female']})
df.describe()
df.head(10)
Explanation: Timeseries
But first, a quick demonstration of how to create example (mock) data.
To create a dataframe from every combination of some given values, like R's expand.grid() function, we can create a dict where the keys are the column names and the values are lists of the data values.
End of explanation
import datetime
ts.between_time(datetime.time(18), datetime.time(9), include_start=False, include_end=False)
rng = pd.date_range('1/1/2000', periods=24, freq='H'); rng
ts = pd.Series(pd.np.random.randn(len(rng)), index=rng); ts
# Select the rows from 10am to 2pm (inclusive:)
ts.iloc[ts.index.indexer_between_time(datetime.time(10), datetime.time(14))]
# The same syntax works for a DataFrame:
df = pd.DataFrame(ts); df
df.iloc[df.index.indexer_between_time(datetime.time(10), datetime.time(14))]
Explanation: Now, back to the Timeseries stuff.
Here is the pandas Time Series / Date functionality documentation.
Let's start by dropping the rows that aren't inside the time range that we want.
Between times
End of explanation
index = pd.date_range('2013-1-1',periods=10,freq='15Min'); index
data = pd.DataFrame(data=[1,2,3,4,5,6,7,8,9,0],columns=['value'],index=index); data
data.index.indexer_between_time(start_time='1:15',end_time='2:00')
data.iloc[data.index.indexer_between_time('1:15','2:00')]
Explanation: Using indexer between time
Create a mask to only consider the rows where the index falls within a certain range.
End of explanation
rng = pd.date_range('20130101 09:00','20130110 16:00',freq='30T'); rng
# Eliminate times that are outside of the range:
rng = rng.take(rng.indexer_between_time('09:30','16:00')); rng
# Eliminate non-weekdays:
rng = rng[rng.weekday<5]; rng
# Convert rng to a pandas Series:
rng.to_series()
Explanation: One trick to remember is that by setting start_time to be later than end_time, you can get the times that are not between the two times.
This also works for pandas.DataFrame.between_time().
Constructing a datetime range that excludes weekends and includes only certain times
Generate sequential timestamp with jump.
In other words, construct a range of desired datetimes and reindex to it:
End of explanation
dates = pd.date_range('2000-01-01',periods=5); dates
dates.to_period(freq='M').to_timestamp()
Explanation: Vectorized Lookup
How to rearrange a Python pandas DataFrame
Dealing with duplicates when reindexing a timeseries to a specified frequency
Calculate the first day of the month for each entry in a DatetimeIndex:
End of explanation
# The resample basics:
rng = pd.date_range('1/1/2012', periods=100, freq='S'); rng
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng); ts
ts.resample('5Min').sum()
ts.resample('5Min').mean()
ts.resample('5Min').ohlc()
ts.resample('5Min').max()
Explanation: Resampling
The Resample Documentation
End of explanation
df = pd.DataFrame([
  ('Monthly','2014-02-1', 529.1),
  ('Monthly','2014-03-1',  67.1),
  ('Monthly','2014-04-1', np.nan), 
  ('Monthly','2014-05-1', 146.8),
  ('Monthly','2014-06-1', 469.7),
  ('Monthly','2014-07-1',  82.9),
  ('Monthly','2014-08-1', 636.9),
  ('Monthly','2014-09-1', 520.9),
  ('Monthly','2014-10-1', 217.4),
  ('Monthly','2014-11-1', 776.6),
  ('Monthly','2014-12-1',  18.4),
  ('Monthly','2015-01-1', 376.7),
  ('Monthly','2015-02-1', 266.5),
  ('Monthly','2015-03-1', np.nan),
  ('Monthly','2015-04-1', 144.1), 
  ('Monthly','2015-05-1', 385.0),
  ('Monthly','2015-06-1', 527.1),
  ('Monthly','2015-07-1', 748.5),
  ('Monthly','2015-08-1', 518.2)],
  columns=['Frequency','Date','Value'])
df
df['Date'] = pd.to_datetime(df['Date'])
df.set_index(['Frequency','Date'],inplace=True); df
# Write a function that will return the sum unless an entry is NaN.
# The function will also return NaN if a month is missing.
gpy = df.groupby(pd.Grouper(level='Date',freq='Q')); gpy
gpy.agg(lambda x: np.nan if (np.isnan(x).any() or len(x)<3) else x.sum())
Explanation: Using Grouper instead of TimeGrouper for time grouping of values
Time grouping with some missing values
End of explanation
data = pd.concat([pd.DataFrame([['A']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')),
                                list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T,
                  pd.DataFrame([['B']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')),
                                list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T,
                  pd.DataFrame([['C']*72, list(pd.date_range('1/1/2011', periods=72, freq='H')),
                                list(np.random.rand(72))], index = ['Group', 'Time', 'Value']).T],
                  axis = 0).set_index(['Group', 'Time'])
data.describe()
data.head()
# Change 'Value' column to type float and then use Grouper:
data['Value'] = data['Value'].astype(float)
daily_counts = data.groupby([pd.TimeGrouper('D', level='Time'),
                            pd.Grouper(level='Group')])['Value'].mean()
daily_counts
# Another solution:
data = data.reset_index(level='Group')
data.groupby('Group').resample('D')['Value'].mean()
Explanation: Grouping using a MultiIndex
Say you have a MultiIndex pandas DataFrame, where the first level index is a group and the second level index is time.
Within each group, resample the daily frequency, taking the average of intraday observations.
End of explanation
df = pd.DataFrame({
'Branch' : 'A A A A A B'.split(),
'Buyer': 'Carl Mark Carl Joe Joe Carl'.split(),
'Quantity': [1,3,5,8,9,3],
'Date' : [
pd.datetime(2013,1,1,13,0),
pd.datetime(2013,1,1,13,5),
pd.datetime(2013,10,1,20,0),
pd.datetime(2013,10,3,10,0),
pd.datetime(2013,12,2,12,0),
pd.datetime(2013,12,2,14,0),
]}); df
df.set_index('Quantity').groupby(pd.Grouper(key='Date', freq='6M')).sum()
# http://pandas.pydata.org/pandas-docs/stable/timeseries.html#offset-aliases
# Use Grouper with another critera, in this case an anonymous function.
df.set_index('Date').groupby(pd.Grouper(freq='6M'))['Quantity'].apply(lambda x: x.count())
# Now use groupby with Grouper and 'Branch':
newdf = df.set_index('Date').groupby(pd.Grouper(freq='6M')).apply(lambda x: x.groupby('Branch')); newdf
dir(newdf)
newdf.__add__
Explanation: Using TimeGrouper and another grouping to create subgroups, then apply a custom function
End of explanation
<END_TASK>