How to transform the following dataset for time series analysis?-CodePudding

This is the dataset, I want to transform for time series forecasting. Here, the column names contains the store number.

df=

| Date | store_1 |store_2 |store_3
|:---- |:------:| -----:|-----:|
| 1-1-21 | 0.5    | 0.2  | 0.3 |
| 1-2-21 | 0.3    | 0.7  | 0.1 |
| 1-3-21 | 0.6    | 0.9  | 0.4 |

I want to convert df to df1:

Date    store number value

1-1-21   1            0.5     
1-2-21   1            0.3     
1-3-21   1            0.6     
1-1-21   2            0.2     
1-2-21   2            0.7     
1-3-21   2            0.9     
1-1-21   3            0.3     
1-2-21   3            0.1     
1-3-21   3            0.4

CodePudding user response：

Use melt:

out = df.melt(id_vars=['Date'], var_name='Store_number', value_name='Value')
out['Store_number'] = out['Store_number'].str.extract(r'store_(\d )')
print(out)

# Output:
     Date Store_number  Value
0  1-1-21            1    0.5
1  1-2-21            1    0.3
2  1-3-21            1    0.6
3  1-1-21            2    0.2
4  1-2-21            2    0.7
5  1-3-21            2    0.9
6  1-1-21            3    0.3
7  1-2-21            3    0.1
8  1-3-21            3    0.4

Update:

Can you suggest me a way to get back to the orginal form? After forecasting the prediction, I need to make the dataframe as the orginal one.

out = out.pivot(index='Date', columns='Store_number', values='Value') \
         .add_prefix('store_').rename_axis(columns=None).reset_index()
print(out)

     Date  store_1  store_2  store_3
0  1-1-21      0.5      0.2      0.3
1  1-2-21      0.3      0.7      0.1
2  1-3-21      0.6      0.9      0.4