[FIXED] How to transpose rows into single column based on time-stamp index in python?

Issue

Sample input dataset is:

0 2017-11-17 10:23:28.691 788 756 789 780

1 2017-11-17 10:23:29.731 788 783 0 0

2 2017-11-17 10:23:30.655 747 0 0 0

3 2017-11-17 10:23:31.627 766 0 0 0

4 2017-11-17 10:23:32.606 807 0 0 0

Sample output dataset what I want:

0 2017-11-17 10:23:28.691 788

0 2017-11-17 10:23:28.691 756

0 2017-11-17 10:23:28.691 789

0 2017-11-17 10:23:28.691 780

1 2017-11-17 10:23:29.731 788

1 2017-11-17 10:23:29.731 783

2 2017-11-17 10:23:30.655 747

3 2017-11-17 10:23:31.627 766

4 2017-11-17 10:23:32.606 807

How can I do this by python or pandas? or is there any other technique to do this?

Solution

Use set_index + stack, then filter out 0 rows and last create DataFrame from Series:

print (df)
                      date    a    b    c    d
0  2017-11-17 10:23:28.691  788  756  789  780
1  2017-11-17 10:23:29.731  788  783    0    0
2  2017-11-17 10:23:30.655  747    0    0    0
3  2017-11-17 10:23:31.627  766    0    0    0
4  2017-11-17 10:23:32.606  807    0    0    0

a = df.set_index('date').stack()
df = a[a != 0].reset_index(drop=True, level=1).reset_index(name='a')
print (df)
                      date    a
0  2017-11-17 10:23:28.691  788
1  2017-11-17 10:23:28.691  756
2  2017-11-17 10:23:28.691  789
3  2017-11-17 10:23:28.691  780
4  2017-11-17 10:23:29.731  788
5  2017-11-17 10:23:29.731  783
6  2017-11-17 10:23:30.655  747
7  2017-11-17 10:23:31.627  766
8  2017-11-17 10:23:32.606  807

Answered By - jezrael