goofd goofd - 2 months ago 7
Python Question

Pandas DataFrame: transforming frame using unique values of a column

I have a pandas dataframe/csv of the form

date Country Type Val
2013-01-01 USA x 23
2013-01-01 USA y 13
2013-01-01 MX x 11
2013-01-01 MX y 14
2013-01-02 USA x 20
2013-01-02 USA y 19
2013-01-02 MX x 14
2013-01-02 MX y 16


I want to convert this to a form

date Country x y
2013-01-01 USA 23 13
2013-01-01 MX 11 14
2013-01-02 USA 20 19
2013-01-02 MX 14 16


In general I am looking for a way to transform a table using unique values of a single column.

I have looked at
pivot
and
groupby
but didn't get the exact form.

HINT: possibly this is solvable by
pivot
but I haven't been able to get the form

Answer

Probably not the most elegant way possible, but using unstack:

>>> df
         date Country Type  Val
0  2013-01-01     USA    x   23
1  2013-01-01     USA    y   13
2  2013-01-01      MX    x   11
3  2013-01-01      MX    y   14
4  2013-01-02     USA    x   20
5  2013-01-02     USA    y   19
6  2013-01-02      MX    x   14
7  2013-01-02      MX    y   16

>>> df.set_index(['date', 'Country', 'Type']).unstack('Type').reset_index()
            date Country  Val
Type                        x   y
0     2013-01-01      MX   11  14
1     2013-01-01     USA   23  13
2     2013-01-02      MX   14  16
3     2013-01-02     USA   20  19

A little more generally, and removing the strange hierarchical columns in the result:

>>> cols = [c for c in df.columns if c not in {'Type', 'Val'}]
>>> df2 = df.set_index(cols + ['Type']).unstack('Type')
>>> df2
                    Val
Type                  x   y
date       Country
2013-01-01 MX        11  14
           USA       23  13
2013-01-02 MX        14  16
           USA       20  19
>>> df2.columns = df2.columns.levels[1]
>>> df2.columns.name = None
>>> df2
                     x   y
date       Country
2013-01-01 MX       11  14
           USA      23  13
2013-01-02 MX       14  16
           USA      20  19
>>> df2.reset_index()
         date Country   x   y
0  2013-01-01      MX  11  14
1  2013-01-01     USA  23  13
2  2013-01-02      MX  14  16
3  2013-01-02     USA  20  19
Comments