CODEWITHSUNDEEP
pythonpandaspivotpivot-tableaggregate
baxx
baxx

Reputation: 4725

Pandas pivot table without multi-index return

I have the following data: 

s = '{"j":{"0":"{}","1":"{}","2":"{}","3":"{}","4":"{}"},"l":{"0":"some","1":"some","2":"some","3":"some","4":"some"},"l_t":{"0":"thing","1":"thing","2":"thing","3":"thing","4":"thing"},"o_l":{"0":"one","1":"one","2":"two","3":"one","4":"one"},"s":{"0":"y","1":"y","2":"y","3":"y","4":"y"},"val":{"0":4,"1":4,"2":3,"3":4,"4":4},"v_text":{"0":"L","1":"L","2":"NLH","3":"L","4":"L"},"v_text_2":{"0":"light","1":"light","2":"neither heavy or light","3":"light","4":"light"},"v":{"0":"x","1":"x","2":"x","3":"x","4":"x"},"year":{"0":2020,"1":2020,"2":2020,"3":2020,"4":2020}}'
dt_test = pd.read_json(s)

which looks as:

    j     l    l_t  o_l  s  val v_text                v_text_2  v  year
0  {}  some  thing  one  y    4      L                   light  x  2020
1  {}  some  thing  one  y    4      L                   light  x  2020
2  {}  some  thing  two  y    3    NLH  neither heavy or light  x  2020
3  {}  some  thing  one  y    4      L                   light  x  2020
4  {}  some  thing  one  y    4      L                   light  x  2020

and would like to create a pivot table, what I don't understand is why the pivot table that I create has a multiindex as the column.

Here's what I've tried:

dt_test.pivot_table(index="v_text_2", columns="l_t", aggfunc="count")

which looks as: 

                           j     l   o_l     s     v v_text   val  year
l_t                    thing thing thing thing thing  thing thing thing
v_text_2                                                               
light                      4     4     4     4     4      4     4     4
neither heavy or light     1     1     1     1     1      1     1     1

I am expecting it to look as: 

l_t                    thing
v_text_2                    
light                      4
neither heavy or light     1

Ultimately I want to aggregate this data so that I can then plot it.

Upvotes: 0

Views: 1081

Answers (2)

Georgina Skibinski
Georgina Skibinski

Reputation: 13397

Actually it's a pretty odd behaviour - for pivot_table except agg function you want to use, you also should mention column you want to apply it to:

For example:

dt_test.pivot_table(index="v_text_2", aggfunc="count", columns="l_t", values="year")

Outputs:

l_t                     thing
v_text_2
light                       4
neither heavy or light      1

Upvotes: 1

ManojK
ManojK

Reputation: 1640

Alternatively, you can use pandas.crosstab:

pd.crosstab(df['v_text_2'],df['l_t'])

l_t                     thing
v_text_2                     
light                       4
neither heavy or light      1

This will yield same output as expected.

Upvotes: 2

Related Questions