Convert column based time series data to regular DataFrame

Question

I have the following data:

df = pd.DataFrame({'id' : [1,2,3,4,5,6], 'category' : [1,3,1,4,3,2], 'day1' : [10,20,30,40,50,60], 'day2' : [1,2,3,4,5,7], 'day3' : [0,1,2,3,7,9] })
df

id  category    day1    day2    day3
0   1   1   10  1   0
1   2   3   20  2   1
2   3   1   30  3   2
3   4   4   40  4   3
4   5   3   50  5   7
5   6   2   60  7   9

It is time series data and I need to prepare the new DataFrame as records of ('id', 'category', 'day'):

df = pd.DataFrame({'id' : [1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6], 'category' : [1,1,1,3,3,3,1,1,1,4,4,4,3,3,3,2,2,2], 'day' : [10,1,0,20,2,1,30,3,2,40,4,3,50,5,7,60,7,9]})
df

    id  category    day
0   1   1   10
1   1   1   1
2   1   1   0
3   2   3   20
4   2   3   2
5   2   3   1
6   3   1   30
7   3   1   3
8   3   1   2
9   4   4   40
10  4   4   4
11  4   4   3
12  5   3   50
13  5   3   5
14  5   3   7
15  6   2   60
16  6   2   7
17  6   2   9

But I don't know how to do it without looping by every DataFrame cell

score 1 · Answer 1 · answered Nov 06 '22 at 18:09

A possible solution:

df.set_index(['id', 'category']).stack().rename(
    'day').reset_index().drop('level_2', axis=1)

Output:

    id  category  day
0    1         1   10
1    1         1    1
2    1         1    0
3    2         3   20
4    2         3    2
5    2         3    1
6    3         1   30
7    3         1    3
8    3         1    2
9    4         4   40
10   4         4    4
11   4         4    3
12   5         3   50
13   5         3    5
14   5         3    7
15   6         2   60
16   6         2    7
17   6         2    9

I'mahdi · Answer 2 · 2022-11-06T18:17:46.353

You can use pandas.melt :

df_new = df.melt(id_vars=['id', 'category'], value_name='day'
                ).sort_values(['id', 'variable']
                             ).drop('variable', axis=1
                                   ).reset_index(drop=True)
print(df_new)

Output:

    id  category  day
0    1         1   10
1    1         1    1
2    1         1    0
3    2         3   20
4    2         3    2
5    2         3    1
6    3         1   30
7    3         1    3
8    3         1    2
9    4         4   40
10   4         4    4
11   4         4    3
12   5         3   50
13   5         3    5
14   5         3    7
15   6         2   60
16   6         2    7
17   6         2    9

Convert column based time series data to regular DataFrame

2 Answers2