Styjun

What does “We have long ago paid the goblins of Moria,” from The Hobbit mean?

'Overwrote' files, space still occupied, are they lost?

Is it better to use mosfet with gate driver IC or mosfet with lower VGs on

What did the first ever Hunger Games look like?

In Bb5 systems against the Sicilian, why does White exchange their b5 bishop without playing a6?

Why cannot a convert make certain statements? I feel they are being pushed away at the same time respect is being given to them

Why 1.5fill is 0pt

What is this WWII four-engine plane on skis?

How often is duct tape used during crewed space missions?

How clean are pets?

How do we know that black holes are spinning?

What does this Blight Tower UI mean?

How To Make Earth's Oceans as Brackish as Lyr's

How would you translate Evangelii Nuntiandi?

Persuading players to be less attached to a pre-session 0 character concept

Transit visa to Hong Kong

Beauville-Laszlo for schemes

How to draw a Venn diagram for X - (Y intersect Z)?

What is the origin of the "being immortal sucks" trope?

Is there a theorem in Real analysis similar to Cauchy's theorem in Complex analysis?

What are the typical trumpet parts in classical music?

Why is the car dealer insisting on a loan instead of cash?

Why is belonging not transitive?

What is the word for a person who destroys monuments?

Reshaping column values into rows with Identifier column at the end

Renaming columns in pandasDelete column from pandas DataFrameHow to drop rows of Pandas DataFrame whose value in a certain column is NaNHow to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasIn Python Pandas, how to join on a datetime column another dataframe that is indexed by a periodindex?Reshape pandas dataframe from rows to columnsMap pandas dataframe on multiple keys as columns or multiIndexExtract rows based on the condition and put in separate dataframe

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

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

pandas pandas-groupby

share|improve this question

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

add a comment
 | 

1

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

pandas pandas-groupby

share|improve this question

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

add a comment
 | 

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

pandas pandas-groupby

share|improve this question

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

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])

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'])

share|improve this question

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

share|improve this question

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

asked Mar 28 at 13:01

MohMoh

2944 bronze badges

2 Answers
2

active

oldest

votes

1

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

share|improve this answer

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks a lot :)
  
  – Moh
  Mar 28 at 13:24
  

  
  
  
  

add a comment
 | 

1

I'd try something like this:

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

share|improve this answer

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55398334%2freshaping-column-values-into-rows-with-identifier-column-at-the-end%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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

share|improve this answer

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks a lot :)
  
  – Moh
  Mar 28 at 13:24
  

  
  
  
  

add a comment
 | 

1

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

share|improve this answer

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Thanks a lot :)
  
  – Moh
  Mar 28 at 13:24
  

  
  
  
  

add a comment
 | 

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

share|improve this answer

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

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

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

share|improve this answer

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

answered Mar 28 at 13:10

Ben.TBen.T

8,04033 gold badges1111 silver badges3131 bronze badges

1

I'd try something like this:

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

share|improve this answer

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

add a comment
 | 

1

I'd try something like this:

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

share|improve this answer

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

add a comment
 | 

I'd try something like this:

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

share|improve this answer

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

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

share|improve this answer

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges

answered Mar 28 at 13:10

ecortazarecortazar

1,07311 gold badge11 silver badge99 bronze badges