Rearranging Pandas dataframe, pivoting, multindex?

I have this table that I want to modify such that the state column becomes header, and there should be multi header such that for each state there are columns positive and totaltested. Can anyone tell how to do it?

image

Use groupby functionality to have the multilevel index for the state. Below is an example (considering data frame in variable df:

df.groupby('state').count()

I don’t want to group by. I just want to bring the state column into header. Can we have multi header just like multi index?

df.pivot(index=‘updatedon’, columns=‘state’)

I think above will have actual data and columns you need
Thought the header and subheader will be swaped ( I couldn’t figure out how to adjust that)


This might work as well.

df1 = df.pivot(index=‘updatedon’, columns=‘state’)
df2 = df1.swaplevel(i=1, j=0, axis=1)
df2 = df2.sort_index(axis=1)

please try and share your results/observation if possible.

It throws this error-

Can you share a public link to your notebook?

What is the index column? Does it have duplicate entries?
If sharing notebook not possible, you can check this thread:

https://drive.google.com/file/d/1VjhKmn-HFPN6H6lnUgfBtDc1IsS65EIR/view?usp=sharing

This is the file.

Screenshot 2020-10-03 at 12.51.24 PM

You will have to make the access public

please try now

https://drive.google.com/file/d/1VjhKmn-HFPN6H6lnUgfBtDc1IsS65EIR/view?usp=sharing

Tried this:

df = pd.read_excel('covid.xlsx', parse_dates=True)
df1 = df.pivot(columns='state')
df2 = df1.swaplevel(i=1, j=0, axis=1)
df2 = df2.sort_index(axis=1)
df2.head()

But you want to keep 'updatedon' as index columns. Right?

Updated
‘updatedon’ column has duplicate entries (for e.g. 17/04/2020), so you cannot use it as index while pivoting. (There might be some trick/workaround, but you will have to google)

ok, thank you! @sanjayk

actually since updatedon has duplicate entries thats why I wanted to pivot.