CODEWITHSUNDEEP
pandaspandas-groupby
Moh
Moh

Reputation: 61

Reshaping column values into rows with Identifier column at the end

I have measurements for Power related to different sensors i.e A1_Pin, A2_Pin and so on. These measurements are recorded in file as columns. The data is uniquely recorded with timestamps. 

df1 = pd.DataFrame({'DateTime': ['12/12/2019', '12/13/2019', '12/14/2019', 
'12/15/2019', '12/16/2019'],
'A1_Pin': [2, 8, 8, 3, 9],
'A2_Pin': [1, 2, 3, 4, 5],
'A3_Pin': [85, 36, 78, 32, 75]})

I want to reform the table so that each row corresponds to one sensor. The last column indicates the sensor ID to which the row data belongs to.

The final table should look like: 

df2 = pd.DataFrame({'DateTime': ['12/12/2019', '12/12/2019', '12/12/2019', 
'12/13/2019', '12/13/2019','12/13/2019', '12/14/2019', '12/14/2019', 
'12/14/2019', '12/15/2019','12/15/2019', '12/15/2019', '12/16/2019', 
'12/16/2019', '12/16/2019'],
'Power': [2, 1, 85,8, 2, 36, 8,3,78, 3, 4, 32, 9, 5, 75],
'ModID': ['A1_PiN','A2_PiN','A3_PiN','A1_PiN','A2_PiN','A3_PiN',                               
'A1_PiN','A2_PiN','A3_PiN','A1_PiN','A2_PiN','A3_PiN',
'A1_PiN','A2_PiN','A3_PiN']})

I have tried Groupby, Melt, Reshape, Stack and loops but could not do that. If anyone could help? Thanks

Upvotes: 1

Views: 25

Answers (2)

Ben.T
Ben.T

Reputation: 29635

When you tried stack, you were on one good track. you need to set_index first and reset_index after such as:

df2 = df1.set_index('DateTime').stack().reset_index(name='Power')\
         .rename(columns={'level_1':'ModID'}) #to fit the names your expected output

And you get:

print (df2)
      DateTime   ModID  Power
0   12/12/2019  A1_Pin      2
1   12/12/2019  A2_Pin      1
2   12/12/2019  A3_Pin     85
3   12/13/2019  A1_Pin      8
4   12/13/2019  A2_Pin      2
5   12/13/2019  A3_Pin     36
6   12/14/2019  A1_Pin      8
7   12/14/2019  A2_Pin      3
8   12/14/2019  A3_Pin     78
9   12/15/2019  A1_Pin      3
10  12/15/2019  A2_Pin      4
11  12/15/2019  A3_Pin     32
12  12/16/2019  A1_Pin      9
13  12/16/2019  A2_Pin      5
14  12/16/2019  A3_Pin     75

Upvotes: 1

ecortazar
ecortazar

Reputation: 1422

I'd try something like this:

df1.set_index('DateTime').unstack().reset_index()

Upvotes: 1

Related Questions