Python Forum
[pandas] How to re-arrange DataFrame columns
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[pandas] How to re-arrange DataFrame columns
#1
Hi,
I have below pandas dataframe:

         Trial1       Trial1      Trial1        Trial2       Trial2  
Name     Sub_item1    Sub_item2  Sub_item3      Sub_item4    Sub_item5
          2019-06-01  2016-06-01  2019-06-01    2019-06-01   2019-06-01
 VBA        1               0        0            1             1
 VLK        0               0        1            1             1
 VBN        1               1        1            1             1
Now I want to arrange as below(desired output):


Name    Date Trail  Sub_item   value 
 VBA     2019-06-01  Sub_item1  1
 VBA     2019-06-01  Sub_item2  0
 VBA     2019-06-01  Sub_item3  0
 VBA     2019-06-01  Sub_item4  1
 VBA     2019-06-01  Sub_item5  1
 VLK     2019-06-01  Sub_item1  0
 VLK     2019-06-01  Sub_item2  0
 VLK     2019-06-01  Sub_item3  1
 VLK     2019-06-01  Sub_item4  1
 VLK     2019-06-01  Sub_item5  1
 VBN     2019-06-01  Sub_item1  1
 VBN     2019-06-01  Sub_item2  1
 VBN     2019-06-01  Sub_item3  1
 VBN     2019-06-01  Sub_item4  1
 VBN     2019-06-01  Sub_item5  1

I am very new to python, can anybody kindly help me how to do this,
Reply
#2
What have you tried?
Reply
#3
I hope the following example helps you:

import pandas as pd
micolumns = pd.MultiIndex.from_tuples([('X', 'foo', '10'), ('X', 'bar', '10'),
                                       ('Y', 'foo', '10'), ('Y', 'bar', '10')],
                                      names=['l0', 'l1', 'l2'])
arr = pd.DataFrame(pd.np.arange(12).reshape(3,4), columns=micolumns)

arr.T.reset_index()   # this almost what you want.
Reply
#4
It gives some error: I use python3.6:
TypeError: '>' not supported between instances of 'str' and 'int'
Reply
#5
At least, you've used symbol > in your code. Probably, you need to convert column dtype first
to be able to use comparison operators. Show your code, please.
Reply
#6
I test the below code which you provide,

import pandas as pd
micolumns = pd.MultiIndex.from_tuples([('X', 'foo', '10'), ('X', 'bar', '10'),
                                       ('Y', 'foo', '10'), ('Y', 'bar', '10')],
                                      names=['l0', 'l1', 'l2'])
arr = pd.DataFrame(pd.np.arange(12).reshape(3,4), columns=micolumns)
 
arr.T.reset_index()
Reply
#7
The code above runs without any errors on my computer (pandas version: 0.23.4). reset_index method turns pandas multi-index to columns, that is almost what you want.
Reply
#8
Sorry I am new, but as a question/suggestion, can you use group by ?
something like:
df.groupby(by=['Name','Trial1Sub_item1','Trial1Sub_item2','Trial1Sub_item3','Trial2Sub_item4','Trial2Sub_item5'])
** No idea if this would work and the other suggestions are probably better
Other idea (again probably wont work) is .transpose
Reply
#9
Once the data frame is processed by reset_index method (as shown above), you
get a column of Sub-items and column of Trial-values. Let
these columns are named subs and trials respectively. In this case you can use groupby method, as follows:

# df is original dataframe (multi-indexed)
new_df = df.T.reset_index() # we suppose subs and trials are columns of new_df; may be you will need to rename column names manually
new_df.groupby(['subs', 'trials'])
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to add columns to polars dataframe sayyedkamran 1 1,689 Nov-03-2023, 03:01 PM
Last Post: gulshan212
  concat 3 columns of dataframe to one column flash77 2 778 Oct-03-2023, 09:29 PM
Last Post: flash77
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  Use pandas to obtain cartesian product between a dataframe of int and equations? haihal 0 1,091 Jan-06-2023, 10:53 PM
Last Post: haihal
  Pandas Dataframe Filtering based on rows mvdlm 0 1,397 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Pandas dataframe: calculate metrics by year mcva 1 2,269 Mar-02-2022, 08:22 AM
Last Post: mcva
  Pandas dataframe comparing anto5 0 1,243 Jan-30-2022, 10:21 AM
Last Post: anto5
  PANDAS: DataFrame | Replace and others questions moduki1 2 1,759 Jan-10-2022, 07:19 PM
Last Post: moduki1
  PANDAS: DataFrame | Saving the wrong value moduki1 0 1,527 Jan-10-2022, 04:42 PM
Last Post: moduki1
  update values in one dataframe based on another dataframe - Pandas iliasb 2 9,105 Aug-14-2021, 12:38 PM
Last Post: jefsummers

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020