Remove extra column level from dataframe

user_6529720 0
Viewed 170

My dataframe looks like this.

enter image description here

It's the result of this code:

survival_by_position = survival_by_position.groupby("POSITION")["SURVIVED"].value_counts().rename("Count").reset_index()
survival_by_position = survival_by_position.pivot(index = "POSITION", columns = "SURVIVED", values = "Count")
survival_by_position = survival_by_position.rename(columns = {False: "DEAD", True: "SURVIVORS"})

I want it to look like this.

enter image description here

That is, POSITION should remain the index, but the columns DEAD and SURVIVORS should "come down", and SURVIVED should disappear. I've tried to (re)set the index, create a new dataframe and assign it the right index and columns, nothing worked. I either get all NaNs, or the default integer index, or some error. I know this has got to be some multi-index trickery, but I am not familiar with that so I don't know how to fix it.

multi-index pandas dataframe python
1 Answers

You just have to rename the column axis:

survival_by_position = survival_by_position.rename_axis(columns=None)

Full code:

survival_by_position = (
    survival_by_position.value_counts(['POSITION', 'SURVIVED'])
                        .unstack('SURVIVED').fillna(0).astype(int)
                        .rename(columns={True: 'DEAD', False: 'SURVIVORS'})
                        .rename_axis(columns=None)
)

Some modifications:

  1. replace groupby / value_counts by value_counts

  2. replace pivot by unstack

  3. remove rename('Count')

  4. remove reset_index()

user_152399510
Related