Pandas reformatting

Question

I want to change the format of Pandas data to have a new multi-index based on a combination of some source columns, and at the same time defer some of the rows. But I don’t know how even after reading the styling and turning tutorial .

Basically, I have:

# fruit      year, variable, value
fruits = \ 
 [('apples' , 2014, 'weight', 1.4),
 ('apples' , 2015, 'weight', 1.5),
 ('bananas', 2014, 'yield', 0.5),
 ('bananas', 2015, 'yield', 0.6),
 ('bananas', 2014, 'weight', 1.4)]
df = DataFrame(fruits)

The result should be:

 multi-index
/----------\
fruit   year   weight yield
apples  2014   1.4    NaN
        2015   1.5    NaN
bananas 2014   1.4    0.5
        2015   NaN    0.6

Any suggestions? Thank.

Answer 1

The source data frame has a column with weightor values yield. We want them to be column names (aka "column level values").

set_index . unstack .

, :

fruits = \ 
 [('apples' , 2014, 'weight', 1.4),
 ('apples' , 2015, 'weight', 1.5),
 ('bananas', 2014, 'yield', 0.5),
 ('bananas', 2015, 'yield', 0.6),
 ('bananas', 2014, 'weight', 1.4)]
df = pd.DataFrame(fruits, columns='fruit year col val'.split())
df = df.set_index(['fruit', 'year', 'col'])
df = df.unstack(level='col')
df.columns = df.columns.droplevel(0)

col           weight  yield
fruit   year               
apples  2014     1.4    NaN
        2015     1.5    NaN
bananas 2014     1.4    0.5
        2015     NaN    0.6

---

- pivot_table:

df = df.pivot_table(index=['fruit', 'year'], columns='col')
df.columns = df.columns.droplevel(0)

Answer 2

DataFrame fruits :

>>> df = pd.DataFrame(fruits, columns=['fruit', 'year', 'var', 'val'])
>>> df
     fruit  year     var  val
0   apples  2014  weight  1.4
1   apples  2015  weight  1.5
2  bananas  2014   yield  0.5
3  bananas  2015   yield  0.6
4  bananas  2014  weight  1.4

pivot_table (nb. index ):

>>> df1 = pd.pivot_table(df, values='val', index=['fruit', 'year'], columns='var')
var           weight  yield
fruit   year               
apples  2014     1.4    NaN
        2015     1.5    NaN
bananas 2014     1.4    0.5
        2015     NaN    0.6

'var', df1.columns=['weight', 'yield'] :

>>> df1
              weight  yield
fruit   year               
apples  2014     1.4    NaN
        2015     1.5    NaN
bananas 2014     1.4    0.5
        2015     NaN    0.6