problem with pd.wide_to_long specifications

I have a dataframe that looks like the following:

id	xx_04-Feb-94	yyy_04-Feb-94	z_04-Feb-94	xx_22-Mar-94	yyy_22-Mar-94	z_22-Mar-94
123
456
789

with values inside the table filled out. I would like to pivot the data from wide to long. the desired output looks as follows:

id	date	xx	yyy	z
123	04-Feb-94
123	22-Mar-94
123	16-Sep-94
456	04-Feb-94
456	22-Mar-94
456	16-Sep-94

I have more than 100 dates which I have stored in a dataframe or a list called mydates I have used the following code:

df_long= pd.wide_to_long(df_wide, stubnames = ['xx','yyy','z'], i='id', j = mydates, sep='_', suffix=r'w+')

but it gives me errors: “too many levels: Index has only 1 level, not 2” or “Must pass DataFrame or 2-d ndarray with boolean values only”

what am I doing wrong?

Answer

Try this:

pd.wide_to_long(
    df, stubnames=["xx", "yyy", "z"], i="id", j="date", sep="_", suffix=r".*"
)