3

I see this question asked multiple times but solutions from other questions did not worked!

I have data frame like

df = pd.DataFrame({
"date": ["20180920"] * 3 + ["20180921"] * 3,
"id": ["A12","A123","A1234","A12345","A123456","A0"],
"mean": [1,2,3,4,5,6],
"std" :[7,8,9,10,11,12],
"test": ["a", "b", "c", "d", "e", "f"],
"result": [70, 90, 110, "(-)", "(+)", 0.3],})

df

using pivot_table

df_sum_table = (pd.pivot_table(df,index=['id'], columns = ['date'], values = ['mean','std']))

I got

enter image description here

df_sum_table.columns

MultiIndex([('mean', '20180920'),
        ('mean', '20180921'),
        ( 'std', '20180920'),
        ( 'std', '20180921')],
       names=[None, 'date'])

So I wanted to shift date column one row below and remove id row. but keep id name there. by following these past solutions

ValueError when trying to have multi-index in DataFrame.pivot

Removing index name from df created with pivot_table()

Resetting index to flat after pivot_table in pandas

pandas pivot_table keep index

df_sum_table = (pd.pivot_table(df,index=['id'], columns = ['date'], values = ['mean','std'])).reset_index().rename_axis(None, axis=1)

but getting error

TypeError: Must pass list-like as names.

How can I remove date but keep the id in the first column ?

The desired output

enter image description here

@jezrael

Corralien
  • 109,409
  • 8
  • 28
  • 52
Alexander
  • 4,527
  • 5
  • 51
  • 98

1 Answers1

1

Try with rename_axis:

df = df.pivot_table(index=['id'], columns = ['date'], values = ['mean', 'std']).rename_axis(columns={'date': None}).fillna('').reset_index().T.reset_index(level=1).T.reset_index(drop=True).reset_index(drop=True)
df.index = df.pop('id').replace('', 'id').tolist()
print(df)

Output:

             mean      mean       std       std
id       20180920  20180921  20180920  20180921
A0                        6                  12
A12             1                   7          
A123            2                   8          
A1234           3                   9          
A12345                    4                  10
A123456                   5                  11

You could use rename_axis and rename the specific column axis name with dictionary mapping. I specify the columns argument for column axis name mapping.

U13-Forward
  • 69,221
  • 14
  • 89
  • 114