I have a problem in selecting what columns to be inserted in Pandas.DataFrame.Groupby.agg.
Here’s the code to get and prepare the data.
# Data Collecting and library import from pandas_datareader import data import pandas as pd symbol = 'AAPL' source = 'yahoo' start_date = '2018-01-01' end_date = '2019-04-24' stock = data.DataReader(symbol, source, start_date, end_date) new_range = pd.date_range(start="2018-1-1", end="2019-12-30") stock = stock.reindex(new_range).fillna(method='ffill').fillna(method='bfill') stock['Day'] = stock.index.weekday_name stock['Month'] = stock.index.month_name() stock['Size'] = stock['High'].apply(lambda x: 'Big' if x>175 else 'Small') stock['Other Size'] = stock['Low'].apply(lambda x: 'Big' if x>175 else 'Small') stock.round(2) stock.head(10)
Which results in
What I’ve done so far is
stock.groupby(['Day', 'Month']).agg( { 'High' : [min, 'mean', max], 'Low' : [min, 'mean', max], 'Open' : 'mean', 'Size' : lambda x: x.value_counts().index[0], # Other_non_numeric : lambda x: x.value_counts().index[1], # Other_columns : 'mean' } ).round(2)
that results in:
The question is:
- How do I include other non numeric columns?
- How do I include other undetermined columns in the dictionary and set the method as ‘mean’?
Advertisement
Answer
1) To determine if a column is numeric, you can use pandas.api.types.is_numeric_dtype
2) To find the remaining columns, you can use set(df.columns)
minus the columns you used in groupby
and those with specific agg functions, for example
from pandas.api.types import is_numeric_dtype fields_groupby = ['Day', 'Month'] fields_specific = { 'High': [min, 'mean', max], 'Low': [min, 'mean', max], 'Open': 'mean', 'Size': lambda x: x.value_counts().index[0], } fields_other = set(set(stock.columns) - set(fields_groupby) - set(fields_specific)) fields_agg_remaining = {col: 'mean' if is_numeric_dtype(stock[col]) else lambda x: x.value_counts().index[1] for col in fields_other}
after that, combine the set of fields_specific
and fields_agg_remaining
to be the agg fields list
agg_fields = fields_agg_remaining agg_fields.update(fields_specific) stock.groupby(['Day', 'Month']).agg(agg_fields).round(2)
EDIT: You can combine everything to put them inside the dictionary argument, for example:
stock.groupby(['Day', 'Month']).agg( {col: [min, 'mean', max] if col in ['High', 'Low'] else 'mean' if col in ['Open'] else lambda x: x.value_counts().index[0] if col in ['Size'] else 'mean' if is_numeric_dtype(stock[col]) else lambda x: x.value_counts().index[1] for col in set(set(stock.columns) - {'Day', 'Month'})} ).round(2)