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

user_7983149 0
Viewed 696

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?

pandas ipython python
3 Answers

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
user_29010020

replace + stack 

df.set_index('A').replace({0:np.nan}).stack().reset_index(name='Value').drop('level_1',1)
Out[22]: 
                        A  Value
0  2017-11-1710:23:28.691  788.0
1  2017-11-1710:23:28.691  756.0
2  2017-11-1710:23:28.691  789.0
3  2017-11-1710:23:28.691  780.0
4  2017-11-1710:23:29.731  788.0
5  2017-11-1710:23:29.731  783.0
6  2017-11-1710:23:30.655  747.0
7  2017-11-1710:23:31.627  766.0
8  2017-11-1710:23:32.606  807.0
user_79645270

You can use melt

df.replace({0: np.nan}).melt(id_vars='datetime', value_vars=['val1', 'val2', 'val3', 'val4']).dropna().drop('variable', axis = 1)

    datetime                    value
0   0 2017-11-17 10:23:28.691   788.0
1   1 2017-11-17 10:23:29.731   788.0
2   2 2017-11-17 10:23:30.655   747.0
3   3 2017-11-17 10:23:31.627   766.0
4   4 2017-11-17 10:23:32.606   807.0
5   0 2017-11-17 10:23:28.691   756.0
6   1 2017-11-17 10:23:29.731   783.0
10  0 2017-11-17 10:23:28.691   789.0
15  0 2017-11-17 10:23:28.691   780.0
user_62873080
Related