pandas merge columns and adding the original column-CodePudding

I have a Dataframe with value predictions.

The first column is for the exact value and each new column is a week in the future. For each week a new row is added. As a result, I have the following example table:

Index W1  W2  W3  W4
1.     5   7   4   9
2.     8   7  10  11

and so on.

I want the values to all be in a single row and I want the other rows to be the number of weeks ahead (in my case exactly the column index of the value).

My table should look like this:

Index Value Week
1.        5    1
2.        7    2
3.        4    3 
4.        9    4
5.        8    1
6.        7    2 
7.       10    3
8.       11    4

I am working with python and have just declared the dataframe. How can this be done?

CodePudding user response：

You could use stack:

df.columns = [c.strip('W') for c in df.columns]
res = (df.set_index('Index').stack().droplevel(level=0).reset_index()).rename(columns = {0:'Value'})

prints:

  index  Value
0     1      5
1     2      7
2     3      4
3     4      9
4     1      8
5     2      7
6     3     10
7     4     11

CodePudding user response：

This is a variation on melt:

(df.melt(id_vars='Index', value_name='Value')
   .assign(Week=lambda d: d['variable'].str[1:].astype(int))
   .drop(columns='variable')
   .sort_values('Index')
)

output:

   Index  Value  Week
0    1.0      5     1
2    1.0      7     2
4    1.0      4     3
6    1.0      9     4
1    2.0      8     1
3    2.0      7     2
5    2.0     10     3
7    2.0     11     4

CodePudding user response：

First rename columns names with cast to integers and remove W and then reshape by DataFrame.stack with some data cleaning by Series.droplevel, Series.rename_axis and Series.reset_index:

df = (df.rename(columns= lambda x: int(x.replace('W','')))
        .stack()
        .droplevel(0)
        .rename_axis('Week')
        .reset_index(name='Value'))
print (df)
   Week  Value
0     1      5
1     2      7
2     3      4
3     4      9
4     1      8
5     2      7
6     3     10
7     4     11