Pandas: Pivot column to headers

Question

I'm trying to pivot the values in a column to column headers, but maintain the rest of the data. Here's my full code, along with the closest I can get to what I'm looking for. The only problem with this is that I can't figure out how to keep the end column:

import pandas as pd

starts = pd.date_range(start = '1/1/2017', freq = '31d', periods = 4).tolist()
ends = pd.date_range(start = '1/31/2017', freq = '31d', periods = 4).tolist()

df = pd.DataFrame({ 'id':['XXX','XXX','XXX','XXX','YYY','YYY','YYY','YYY'], 
                    'start': starts + starts,
                    'end': ends + ends,
                    'type':['car','car','car','car','truck','truck','truck','truck']
                    }, columns = ['id','start','end','type'])

Original Dataframe:

    id      start        end   type
0  XXX 2017-01-01 2017-01-31    car
1  XXX 2017-02-01 2017-03-03    car
2  XXX 2017-03-04 2017-04-03    car
3  XXX 2017-04-04 2017-05-04    car
4  YYY 2017-01-01 2017-01-31  truck
5  YYY 2017-02-01 2017-03-03  truck
6  YYY 2017-03-04 2017-04-03  truck
7  YYY 2017-04-04 2017-05-04  truck

My closest current pivot attempt:

print df.pivot(index = 'start', columns = 'id', values = 'type').reset_index()

Current output:

id      start  XXX    YYY
0  2017-01-01  car  truck
1  2017-02-01  car  truck
2  2017-03-04  car  truck
3  2017-04-04  car  truck

Desired output:

        start         end  XXX    YYY
0  2017-01-01  2017-01-31  car  truck
1  2017-02-01  2017-03-03  car  truck
2  2017-03-04  2017-04-03  car  truck
3  2017-04-04  2017-05-04  car  truck

I've tried both this and this, with no success.

Any help would be appreciated.

score 6 · Answer 1 · answered Nov 08 '17 at 17:39

6

pd.pivot_table(df,index=['start','end'],columns='id',values='type',aggfunc='sum').reset_index()
Out[1587]: 
id       start         end  XXX    YYY
0   2017-01-01  2017-01-31  car  truck
1   2017-02-01  2017-03-03  car  truck
2   2017-03-04  2017-04-03  car  truck
3   2017-04-04  2017-05-04  car  truck

answered Nov 08 '17 at 17:39

BENY

317,841
20
164
234

Nice, I guess pivot_table should be preferred – Vaishali Nov 08 '17 at 17:42
@Vaishali - why do you say `pivot_table` would be preferred? – elPastor Nov 08 '17 at 17:45
Because you have a function that does the same thing so may be more pythonic – Vaishali Nov 08 '17 at 17:48
@pshep123 it have the `aggfunc` in the end , You can also agg the value by groupby when you are using unstack, but that is one more step .. and groupby is time costing function . – BENY Nov 08 '17 at 17:52

score 5 · Accepted Answer · answered Nov 08 '17 at 17:33

5

Using set_index and unstack,

df.set_index(['start', 'end', 'id']).type.unstack().reset_index()



id  start       end         XXX YYY
0   2017-01-01  2017-01-31  car truck
1   2017-02-01  2017-03-03  car truck
2   2017-03-04  2017-04-03  car truck
3   2017-04-04  2017-05-04  car truck

answered Nov 08 '17 at 17:33

Vaishali

37,545
5
58
86

I very slightly updated the question to remove the "id" column name as the index. Solution was to add `.rename_axis(None, axis = 1)` to the end. – elPastor Nov 08 '17 at 18:02

Pandas: Pivot column to headers

2 Answers2