How to Pivot pandas for removing of some headers and renaming of some indexes

0 votes

I have the following pandas dataframe:

    count  event      date
0    1544  'strike'   2016-11-01
1     226  'defense'  2016-11-01
2    1524  'strike'   2016-12-01
3     246  'defense'  2016-12-01
4    1592  'strike'   2017-01-01
5     245  'defense'  2017-01-01

I want to pivot/transform it in such a way the final output looks like this:

event     2016-11-01 2016-12-01 2017-01-01 2017-02-01 2017-03-01                                                                     
'strike'        1544       1524       1592       1608       1654   
'defense'        226        246        245        210        254

but what i'm getting now upon pivoting is this:

                        count          count            count           count             count\
date          2016-11-01 2016-12-01 2017-01-01 2017-02-01 2017-03-01   
event                                                                  
'strike'            1544       1524       1592       1608       1654   
'defense'            226        246        245        210        254   

is there any way i could remove the entire empty row ahead of the event index-name and rename the date index-name with event as its index-name and also remove the unwanted countappearing in the first row of the data frame? The data seems to be transforming correctly i just want to get rid of these headers and indexes and have the renamed and removed properly. I also don't want the row labels in the desired output.

This is what i've been trying till now:

output = df.pivot(index='event', columns='date')
print(output)

Sep 27, 2018 in Python by bug_seeker
• 15,520 points
20,560 views

1 answer to this question.

+1 vote

Solution is add parameter values to pivot, then add reset_index for column from index and rename_axis fro remove column name:

output=df.pivot(index='event',columns='date',values='count').reset_index().rename_axis(None,1)
print(output)
       event  2016-11-01  2016-12-01  2017-01-01
0  'defense'         226         246         245
1   'strike'        1544        1524        1592

What happens if omit it?

print (df)
   count      event        date  count1
0   1544   'strike'  2016-11-01       1
1    226  'defense'  2016-11-01       7
2   1524   'strike'  2016-12-01       8
3    246  'defense'  2016-12-01       3
4   1592   'strike'  2017-01-01       0
5    245  'defense'  2017-01-01       1

pivot use each not used column and create MultiIndex for distinguish original columns:

output = df.pivot(index='event', columns='date')
print(output)
               count                           count1                      
date      2016-11-01 2016-12-01 2017-01-01 2016-11-01 2016-12-01 2017-01-01
event                                                                      
'defense'        226        246        245          7          3          1
'strike'        1544       1524       1592          1          8          0
answered Sep 27, 2018 by Priyaj
• 58,090 points

I tried same but I am getting below error,

rename_axis() takes from 1 to 2 positional arguments but 3 were given
Can you please help me.

Hi, @saroj,

Set the name of the axis for the index or columns.

rename_axis(None,axis=1) instead of rename_axis(None,1)

Related Questions In Python

0 votes
1 answer

How can I reformat value_counts() analysis in Pandas for large number of columns?

If I were you, I'd do it ...READ MORE

answered Apr 17, 2018 in Python by anonymous
6,436 views
0 votes
1 answer

How to print all the index of Pandas Dataframe?

Try this: print(df.index.values) READ MORE

answered Apr 8, 2019 in Python by Yogi
4,666 views
0 votes
1 answer

How to find the sum of rows in Pandas dataframe?

You can use a combination groupby function with the sum() method. ...READ MORE

answered May 9, 2019 in Python by Jisha
4,251 views
+1 vote
2 answers

How to display column names of Pandas Dataframe?

print(list(your_df)) READ MORE

answered Jan 18, 2020 in Python by anonymous
21,826 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,023 views
0 votes
1 answer
+1 vote
1 answer

How to replace id with attribute corresponding to id of another table?

Use the following query statement and let ...READ MORE

answered Aug 8, 2018 in Python by Priyaj
• 58,090 points
2,016 views
0 votes
1 answer

How to sort a list of strings?

Try  items = ["live", "like", "code", "cool", "bug"] ...READ MORE

answered Jul 27, 2018 in Python by Priyaj
• 58,090 points
557 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP