setting columns in multiindex pandas

Question

I have this pandas df which i imported from a csv:

df
0  0 apple  banana  orange                        dates apple  banana  orange
1  1      1       1      1     Friday, January 01, 2021      1       1      1
2  2      1       1      1   Saturday, January 02, 2021      2       2      2
3  3      1       1      1     Sunday, January 03, 2021      3       3      3
4  4      1       1      1     Monday, January 04, 2021      4       4      4
5  5      1       1      1    Tuesday, January 05, 2021      5       5      5
6  6      1       1      1  Wednesday, January 06, 2021      6       6      6
7  7      1       1      1   Thursday, January 07, 2021      7       7      7
8  8      1       4      1     Friday, January 08, 2021      8       8      8
9  9      1       1      1   Saturday, January 09, 2021      9       9      9

Is it possible for everything on the left to be grouped under fresh and everything on the right of the dates to be under column spoil in multiindex format. Such as, there is one column which contains [apple, banana, orange]. I want to do this because later when i set the date as index there would be no confusion as both sides of the column have the same names.

score 1 · Answer 1 · answered Aug 05 '21 at 18:11

1

This may help df.columns.values[1] = "apple1" df.columns.values[2] = "banana1"

answered Aug 05 '21 at 18:11

Faika Majid

77
4

https://stackoverflow.com/a/39986959/14310038 refer to this answer – Faika Majid Aug 05 '21 at 18:26

score 1 · Accepted Answer · answered Aug 05 '21 at 18:24

df.columns = pd.MultiIndex.from_arrays([['', '', 'fresh', 'fresh', 'fresh', '', 'spoil', 'spoil', 'spoil'],
                                        df.columns])

output:

         fresh                                              spoil                  
   0   0 apple banana orange                        dates   apple   banana   orange
0  1   1     1      1      1     Friday, January 01, 2021       1        1        1
1  2   2     1      1      1   Saturday, January 02, 2021       2        2        2
2  3   3     1      1      1     Sunday, January 03, 2021       3        3        3
3  4   4     1      1      1     Monday, January 04, 2021       4        4        4
4  5   5     1      1      1    Tuesday, January 05, 2021       5        5        5
5  6   6     1      1      1  Wednesday, January 06, 2021       6        6        6
6  7   7     1      1      1   Thursday, January 07, 2021       7        7        7
7  8   8     1      4      1     Friday, January 08, 2021       8        8        8
8  9   9     1      1      1   Saturday, January 09, 2021       9        9        9

NB. if you want to set_index('dates') do it before this operation, this will be easier

Thanks for your help. When i try to run i get this error: `ValueError: all arrays must be same length` — Slartibartfast, Aug 05 '21 at 18:29
Check the number of columns in your real dataframe. Might be different? — mozway, Aug 05 '21 at 18:30
Could you please also advise how i can get rid of the first 3 '0' columns ? Thanks — Slartibartfast, Aug 05 '21 at 21:09
The first one is the index, it will go away if you `reset_index('dates')`. The other two are indeed columns you can remove them with `df.iloc[:, 2:]` or `df.drop('0', axis=1)` (if the zero is a string). — mozway, Aug 06 '21 at 06:48

score 1 · Answer 3 · answered Aug 05 '21 at 18:42

You can try:

# Get the column number of column `dates`
dates_loc = df.columns.get_loc('dates')

arrays = [['fresh'] * dates_loc + [''] + ['spoil'] * (len(df.columns) - dates_loc -1), df.columns.tolist()]

df.columns = pd.MultiIndex.from_arrays(arrays)



  fresh                                                        spoil                  
      0   0 apple banana orange                        dates   apple   banana  orange
0     1   1     1      1      1     Friday, January 01, 2021       1        1        1
1     2   2     1      1      1   Saturday, January 02, 2021       2        2        2
2     3   3     1      1      1     Sunday, January 03, 2021       3        3        3
3     4   4     1      1      1     Monday, January 04, 2021       4        4        4
4     5   5     1      1      1    Tuesday, January 05, 2021       5        5        5
5     6   6     1      1      1  Wednesday, January 06, 2021       6        6        6
6     7   7     1      1      1   Thursday, January 07, 2021       7        7        7
7     8   8     1      4      1     Friday, January 08, 2021       8        8        8
8     9   9     1      1      1   Saturday, January 09, 2021       9        9        9

setting columns in multiindex pandas

3 Answers3