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
  error bars with dataframe and pandas Hucky 4 281 Apr-27-2020, 02:02 AM
Last Post: Hucky
  Python Pandas DataFrame Help AmericanEagle1989 1 182 Apr-12-2020, 12:37 PM
Last Post: AmericanEagle1989
  How does pyplot know what was plotted by the output of pandas.DataFrame(...).cumprod( codeowl 2 194 Mar-28-2020, 08:27 AM
Last Post: j.crater
  DataFrame: To print a column value which is not null out of 5 columns mani 2 215 Mar-18-2020, 06:07 AM
Last Post: mani
  Ordering of pandas DataFrame new_to_python 5 271 Mar-15-2020, 06:08 PM
Last Post: new_to_python
Question Dividing a single column of dataframe into multiple columns based on char length darpInd 2 240 Mar-14-2020, 09:19 AM
Last Post: scidam
  Pandas dataframe merge snmmat 1 257 Mar-09-2020, 06:56 PM
Last Post: jefsummers
  Interate for loop over certain columns in dataframe Finpyth 2 227 Mar-06-2020, 08:34 AM
Last Post: Finpyth
  Transform Facebook Graph API insights JSON to pandas dataframe usman 0 275 Mar-03-2020, 05:14 AM
Last Post: usman
  How to highlight dataframe columns SriRajesh 1 228 Feb-28-2020, 11:02 PM
Last Post: Marbelous

Forum Jump:


Users browsing this thread: 1 Guest(s)