Bottom Page

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,
Quote
#2
What have you tried?
Quote
#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.
Quote
#4
It gives some error: I use python3.6:
TypeError: '>' not supported between instances of 'str' and 'int'
Quote
#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.
Quote
#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()
Quote
#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.
Quote
#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
Quote
#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'])
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  pandas dataframe iloc mystery edvvardbrian 0 46 Oct-20-2019, 03:17 PM
Last Post: edvvardbrian
  How to add a few empty rows into a pandas dataframe python_newbie09 2 399 Sep-20-2019, 08:52 AM
Last Post: python_newbie09
  How modify the DataFrame columns SriRajesh 2 140 Sep-12-2019, 03:14 PM
Last Post: SriRajesh
  Double 'for' loop and writing in a new columns dataframe marco_ita 0 150 Sep-07-2019, 12:44 PM
Last Post: marco_ita
  Dropping a column from pandas dataframe marco_ita 6 531 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  created a pandas series instead of pandas DataFrame ibaad1406 6 517 Sep-06-2019, 06:23 AM
Last Post: ibaad1406
  Applying operation to a pandas multi index dataframe subgroup Nuovoq 1 298 Sep-04-2019, 10:04 PM
Last Post: Nuovoq
  Substr on Pandas Dataframe Scott 1 337 Sep-02-2019, 02:49 AM
Last Post: scidam
  Pandas Dataframe to Google Big Query Ecniv 1 401 Aug-21-2019, 04:56 PM
Last Post: ThomasL
  how to apply user defined function to Pandas DataFrame evelynow 3 584 Aug-20-2019, 11:35 PM
Last Post: scidam

Forum Jump:


Users browsing this thread: 1 Guest(s)