CODEWITHSUNDEEP
pythonpandas
zacha2
zacha2

Reputation: 223

pandas stacking a dataframe

I have a dataframe, that Looks like:

sensorId                   1      2      3   

9b:f3:55:19:00:4b:12:00    1      7      8    
bf:f3:55:19:00:4b:12:00    6      5      9   
da:f3:55:19:00:4b:12:00    1      1      2

and I want to restructure into a dataframe with the following struture:

 sensorId                 y
 9b:f3:55:19:00:4b:12:00  1
 9b:f3:55:19:00:4b:12:00  7
 9b:f3:55:19:00:4b:12:00  8
 bf:f3:55:19:00:4b:12:00  6
 bf:f3:55:19:00:4b:12:00  5
 bf:f3:55:19:00:4b:12:00  9
 da:f3:55:19:00:4b:12:00  1
 da:f3:55:19:00:4b:12:00  1
 da:f3:55:19:00:4b:12:00  2

I tried using df.stack(), but the result isn't promising, because it Returns a pd.series:

9b:f3:55:19:00:4b:12:00  1      1
                         2      7
                         3      8
bf:f3:55:19:00:4b:12:00  1      6
                         2      5
                         3      9
da:f3:55:19:00:4b:12:00  1      1
                         2      1
                         3      2

Upvotes: 2

Views: 242

Answers (2)

jezrael
jezrael

Reputation: 863801

Use double Series.reset_index - first for remove second level of MultiIndex and second for convert Series to DataFrame:

df = df.stack().reset_index(level=1, drop=True).reset_index(name='y')
print (df)
                  sensorId  y
0  9b:f3:55:19:00:4b:12:00  1
1  9b:f3:55:19:00:4b:12:00  7
2  9b:f3:55:19:00:4b:12:00  8
3  bf:f3:55:19:00:4b:12:00  6
4  bf:f3:55:19:00:4b:12:00  5
5  bf:f3:55:19:00:4b:12:00  9
6  da:f3:55:19:00:4b:12:00  1
7  da:f3:55:19:00:4b:12:00  1
8  da:f3:55:19:00:4b:12:00  2

EDIT:

Because df.stack return MultiIndex, it means sensorId is not column, but index.

If sensorId is column:

df = df.set_index('sensorId').stack().reset_index(level=1, drop=True).reset_index(name='y')

Upvotes: 5

U13-Forward
U13-Forward

Reputation: 71620

Or use:

df = df.set_index('sensorId').stack().reset_index(name='y').drop('level_1',1)

And also a little correction to @jezrael's (his is nice tho):

df = df.set_index('sensorId').stack().reset_index(level=1, drop=True).reset_index(name='y')

And now:

print(df)

Is:

                  sensorId  y
0  9b:f3:55:19:00:4b:12:00  1
1  9b:f3:55:19:00:4b:12:00  7
2  9b:f3:55:19:00:4b:12:00  8
3  bf:f3:55:19:00:4b:12:00  6
4  bf:f3:55:19:00:4b:12:00  5
5  bf:f3:55:19:00:4b:12:00  9
6  da:f3:55:19:00:4b:12:00  1
7  da:f3:55:19:00:4b:12:00  1
8  da:f3:55:19:00:4b:12:00  2

Upvotes: 0

Related Questions