Group data based on column label in pandas dataframe
dataframe, indexing, pandas, pandas-groupby, python
Solution
You can use column-wise (`axis=1`) groupby and take the `mean`:
In [11]: df = pd.DataFrame(np.random.randn(4, 3), columns=[[1, 2, 3], ['d', 's', 'd']])
In [12]: df.columns.names = ['PLOT', 'DEPTH']
In [13]: df
Out[13]:
PLOT 1 2 3
DEPTH d s d
0 -0.557490 -1.231495 -0.333703
1 0.513394 1.046577 0.596306
2 -0.404606 -1.615080 -0.694562
3 -0.078497 -0.683405 0.056857
In [14]: df.groupby(level='DEPTH', axis=1).mean()
Out[14]:
DEPTH d s
0 -0.445596 -1.231495
1 0.554850 1.046577
2 -0.549584 -1.615080
3 -0.010820 -0.683405
Problem
I've been reading about hierarchical index and multiindex in a pandas dataframe but it seems these are all for ordered labels. For example, my data looks like this: And I want to be able to group the data together based on the column label ie. aggregate all columns with 'd' in row 3 together by averaging. What is the best way to get this excel data (or csv if absolutely needed) into a dataframe so that I can do these operations and how would I go about doing them? Any advice or references would be appreciated EDIT I tried loading the data from a csv using the following command: ``` data = pd.read_csv('Dataset.csv', index_col=0, header=[0,1,2,3], parse_dates=True) ``` which gives me this when loaded: ``` <class 'pandas.core.frame.DataFrame'> DatetimeIndex: 18 entries, 2013-05-27 10:31:00 to 2013-07-24 11:31:00 Data columns (total 40 columns): (1, mix, d, n) 18 non-null values (2, aq, s, n) 18 non-null values (3, gr, s, n) 18 non-null values (4, mix, d, n) 18 non-null values (5, aq, d, n) 17 non-null values ``` I'm just not really sure where to go from there.