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?
Advertisement
Answer
Try this:
pd.wide_to_long( df, stubnames=["xx", "yyy", "z"], i="id", j="date", sep="_", suffix=r".*" )