2

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.

Nicola
  • 379
  • 3
  • 14

1 Answers1

1

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()

Corralien
  • 109,409
  • 8
  • 28
  • 52
  • Renaming the column axis doesn't "move" down the two column names. I know the result is still functionally identical to a normal dataframe (or so it seems to me), but I wonder why there doesn't seem to be a way to make it look like one. I didn't know about the `unstack` trick, very cool way to avoid `groupby`. – Nicola Mar 10 '22 at 11:04
  • If you want your expected output, you have to append `.reset_index()` (modification 4) – Corralien Mar 10 '22 at 11:07
  • That works, but then "POSITION" is no longer the index. Is there way to make it such, at that point? – Nicola Mar 10 '22 at 11:08
  • No this is the default representation of a dataframe in jupyter notebook. The index is one row below. You can't change this. – Corralien Mar 10 '22 at 11:10
  • That's right, I remember now. – Nicola Mar 10 '22 at 11:14