reshape dataframe: columns to lines












0















I have a dataframe df_given [n, m] and I want to change the columns values into line values at each 3 rows.



import pandas as pd
df_test1 = pd.DataFrame({'A1': np.arange(1,4), 'B1': np.arange(10, 13), 'C1': np.arange(20, 23)})
df_test2 = pd.DataFrame({'A1': np.arange(11,14), 'B1': np.arange(100, 103), 'C1': np.arange(200, 203)})
df_given = pd.concat((df_test1, df_test2), axis =0)

df_desired = pd.DataFrame({'A': [1,10,20,11, 100, 200], 'B':[2,11, 21,12, 101, 201], 'C':[3,12,22,13, 102, 202]})
df_desired.head(10)

df_given.head(10)









share|improve this question

























  • Please paste the input and output in code format not images.

    – Mayank Porwal
    Oct 22 '18 at 14:23











  • I posted the whole code from the beginning. Should I delete the pictures?

    – Silvia
    Oct 22 '18 at 14:35











  • Code is fine, just replace your images with proper format.

    – Mayank Porwal
    Oct 22 '18 at 14:36













  • Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

    – Silvia
    Oct 22 '18 at 14:41
















0















I have a dataframe df_given [n, m] and I want to change the columns values into line values at each 3 rows.



import pandas as pd
df_test1 = pd.DataFrame({'A1': np.arange(1,4), 'B1': np.arange(10, 13), 'C1': np.arange(20, 23)})
df_test2 = pd.DataFrame({'A1': np.arange(11,14), 'B1': np.arange(100, 103), 'C1': np.arange(200, 203)})
df_given = pd.concat((df_test1, df_test2), axis =0)

df_desired = pd.DataFrame({'A': [1,10,20,11, 100, 200], 'B':[2,11, 21,12, 101, 201], 'C':[3,12,22,13, 102, 202]})
df_desired.head(10)

df_given.head(10)









share|improve this question

























  • Please paste the input and output in code format not images.

    – Mayank Porwal
    Oct 22 '18 at 14:23











  • I posted the whole code from the beginning. Should I delete the pictures?

    – Silvia
    Oct 22 '18 at 14:35











  • Code is fine, just replace your images with proper format.

    – Mayank Porwal
    Oct 22 '18 at 14:36













  • Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

    – Silvia
    Oct 22 '18 at 14:41














0












0








0








I have a dataframe df_given [n, m] and I want to change the columns values into line values at each 3 rows.



import pandas as pd
df_test1 = pd.DataFrame({'A1': np.arange(1,4), 'B1': np.arange(10, 13), 'C1': np.arange(20, 23)})
df_test2 = pd.DataFrame({'A1': np.arange(11,14), 'B1': np.arange(100, 103), 'C1': np.arange(200, 203)})
df_given = pd.concat((df_test1, df_test2), axis =0)

df_desired = pd.DataFrame({'A': [1,10,20,11, 100, 200], 'B':[2,11, 21,12, 101, 201], 'C':[3,12,22,13, 102, 202]})
df_desired.head(10)

df_given.head(10)









share|improve this question
















I have a dataframe df_given [n, m] and I want to change the columns values into line values at each 3 rows.



import pandas as pd
df_test1 = pd.DataFrame({'A1': np.arange(1,4), 'B1': np.arange(10, 13), 'C1': np.arange(20, 23)})
df_test2 = pd.DataFrame({'A1': np.arange(11,14), 'B1': np.arange(100, 103), 'C1': np.arange(200, 203)})
df_given = pd.concat((df_test1, df_test2), axis =0)

df_desired = pd.DataFrame({'A': [1,10,20,11, 100, 200], 'B':[2,11, 21,12, 101, 201], 'C':[3,12,22,13, 102, 202]})
df_desired.head(10)

df_given.head(10)






python pandas dataframe reshape






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 16 '18 at 3:23









Cœur

19k9114155




19k9114155










asked Oct 22 '18 at 14:19









SilviaSilvia

4510




4510













  • Please paste the input and output in code format not images.

    – Mayank Porwal
    Oct 22 '18 at 14:23











  • I posted the whole code from the beginning. Should I delete the pictures?

    – Silvia
    Oct 22 '18 at 14:35











  • Code is fine, just replace your images with proper format.

    – Mayank Porwal
    Oct 22 '18 at 14:36













  • Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

    – Silvia
    Oct 22 '18 at 14:41



















  • Please paste the input and output in code format not images.

    – Mayank Porwal
    Oct 22 '18 at 14:23











  • I posted the whole code from the beginning. Should I delete the pictures?

    – Silvia
    Oct 22 '18 at 14:35











  • Code is fine, just replace your images with proper format.

    – Mayank Porwal
    Oct 22 '18 at 14:36













  • Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

    – Silvia
    Oct 22 '18 at 14:41

















Please paste the input and output in code format not images.

– Mayank Porwal
Oct 22 '18 at 14:23





Please paste the input and output in code format not images.

– Mayank Porwal
Oct 22 '18 at 14:23













I posted the whole code from the beginning. Should I delete the pictures?

– Silvia
Oct 22 '18 at 14:35





I posted the whole code from the beginning. Should I delete the pictures?

– Silvia
Oct 22 '18 at 14:35













Code is fine, just replace your images with proper format.

– Mayank Porwal
Oct 22 '18 at 14:36







Code is fine, just replace your images with proper format.

– Mayank Porwal
Oct 22 '18 at 14:36















Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

– Silvia
Oct 22 '18 at 14:41





Ok, I don't understand what exactly do you mean. I will have to check the FAQ questions as I don't know what a proper format for images is.

– Silvia
Oct 22 '18 at 14:41












3 Answers
3






active

oldest

votes


















2














Using pd.concat with GroupBy:



df = df.reset_index(drop=True)

res = pd.concat((pd.DataFrame(df_slice.T.values, columns=df.columns.str[0])
for _, df_slice in df.groupby(df.index // 3)), ignore_index=True)

print(res)

A B C
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202


Explanation





  • df.reset_index(drop=True) resets the dataframe index to the regular pd.RangeIndex, i.e. 0, 1, 2, ..., n.


  • df.groupby(df.index // 3) groups the dataframe in chunks of 3 rows. We iterate over this GroupBy object in a generator comprehension.


  • df_slice.T.values transposes the 3 rows which constitute a slice and converts to a NumPy array. We feed this to pd.DataFrame to construct a dataframe from a NumPy array.


  • pd.concat takes an iterable of dataframes and combines them to construct a single dataframe.






share|improve this answer





















  • 1





    Seems like there is no better way :-(

    – Wen-Ben
    Oct 22 '18 at 14:42











  • Can you elaborate a bit on what the code is doing?

    – Silvia
    Oct 22 '18 at 14:53











  • @Silvia, Sure, added an explanation.

    – jpp
    Oct 22 '18 at 15:25



















2














IIUC



pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T
Out[997]:
0 1 2
A1 1 2 3
B1 10 11 12
C1 20 21 22
A1 11 12 13
B1 100 101 102
C1 200 201 202





share|improve this answer
























  • I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

    – Silvia
    Oct 22 '18 at 14:51






  • 1





    @Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

    – Wen-Ben
    Oct 22 '18 at 14:54











  • You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

    – Silvia
    Oct 22 '18 at 15:02



















1














One alternative using groupby, stack & unstack



df1 = df_given.assign(g=df_given.groupby(level=0).cumcount()).set_index('g',append=True)

df1 = df1.stack().unstack(0).reset_index(drop=True)

df1.columns = df_given.columns

print (df1)


A1 B1 C1
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202





share|improve this answer



















  • 1





    This version also works.

    – Silvia
    Oct 22 '18 at 15:03











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/3.0/"u003ecc by-sa 3.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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52931586%2freshape-dataframe-columns-to-lines%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














Using pd.concat with GroupBy:



df = df.reset_index(drop=True)

res = pd.concat((pd.DataFrame(df_slice.T.values, columns=df.columns.str[0])
for _, df_slice in df.groupby(df.index // 3)), ignore_index=True)

print(res)

A B C
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202


Explanation





  • df.reset_index(drop=True) resets the dataframe index to the regular pd.RangeIndex, i.e. 0, 1, 2, ..., n.


  • df.groupby(df.index // 3) groups the dataframe in chunks of 3 rows. We iterate over this GroupBy object in a generator comprehension.


  • df_slice.T.values transposes the 3 rows which constitute a slice and converts to a NumPy array. We feed this to pd.DataFrame to construct a dataframe from a NumPy array.


  • pd.concat takes an iterable of dataframes and combines them to construct a single dataframe.






share|improve this answer





















  • 1





    Seems like there is no better way :-(

    – Wen-Ben
    Oct 22 '18 at 14:42











  • Can you elaborate a bit on what the code is doing?

    – Silvia
    Oct 22 '18 at 14:53











  • @Silvia, Sure, added an explanation.

    – jpp
    Oct 22 '18 at 15:25
















2














Using pd.concat with GroupBy:



df = df.reset_index(drop=True)

res = pd.concat((pd.DataFrame(df_slice.T.values, columns=df.columns.str[0])
for _, df_slice in df.groupby(df.index // 3)), ignore_index=True)

print(res)

A B C
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202


Explanation





  • df.reset_index(drop=True) resets the dataframe index to the regular pd.RangeIndex, i.e. 0, 1, 2, ..., n.


  • df.groupby(df.index // 3) groups the dataframe in chunks of 3 rows. We iterate over this GroupBy object in a generator comprehension.


  • df_slice.T.values transposes the 3 rows which constitute a slice and converts to a NumPy array. We feed this to pd.DataFrame to construct a dataframe from a NumPy array.


  • pd.concat takes an iterable of dataframes and combines them to construct a single dataframe.






share|improve this answer





















  • 1





    Seems like there is no better way :-(

    – Wen-Ben
    Oct 22 '18 at 14:42











  • Can you elaborate a bit on what the code is doing?

    – Silvia
    Oct 22 '18 at 14:53











  • @Silvia, Sure, added an explanation.

    – jpp
    Oct 22 '18 at 15:25














2












2








2







Using pd.concat with GroupBy:



df = df.reset_index(drop=True)

res = pd.concat((pd.DataFrame(df_slice.T.values, columns=df.columns.str[0])
for _, df_slice in df.groupby(df.index // 3)), ignore_index=True)

print(res)

A B C
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202


Explanation





  • df.reset_index(drop=True) resets the dataframe index to the regular pd.RangeIndex, i.e. 0, 1, 2, ..., n.


  • df.groupby(df.index // 3) groups the dataframe in chunks of 3 rows. We iterate over this GroupBy object in a generator comprehension.


  • df_slice.T.values transposes the 3 rows which constitute a slice and converts to a NumPy array. We feed this to pd.DataFrame to construct a dataframe from a NumPy array.


  • pd.concat takes an iterable of dataframes and combines them to construct a single dataframe.






share|improve this answer















Using pd.concat with GroupBy:



df = df.reset_index(drop=True)

res = pd.concat((pd.DataFrame(df_slice.T.values, columns=df.columns.str[0])
for _, df_slice in df.groupby(df.index // 3)), ignore_index=True)

print(res)

A B C
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202


Explanation





  • df.reset_index(drop=True) resets the dataframe index to the regular pd.RangeIndex, i.e. 0, 1, 2, ..., n.


  • df.groupby(df.index // 3) groups the dataframe in chunks of 3 rows. We iterate over this GroupBy object in a generator comprehension.


  • df_slice.T.values transposes the 3 rows which constitute a slice and converts to a NumPy array. We feed this to pd.DataFrame to construct a dataframe from a NumPy array.


  • pd.concat takes an iterable of dataframes and combines them to construct a single dataframe.







share|improve this answer














share|improve this answer



share|improve this answer








edited Oct 22 '18 at 15:25

























answered Oct 22 '18 at 14:30









jppjpp

102k2165115




102k2165115








  • 1





    Seems like there is no better way :-(

    – Wen-Ben
    Oct 22 '18 at 14:42











  • Can you elaborate a bit on what the code is doing?

    – Silvia
    Oct 22 '18 at 14:53











  • @Silvia, Sure, added an explanation.

    – jpp
    Oct 22 '18 at 15:25














  • 1





    Seems like there is no better way :-(

    – Wen-Ben
    Oct 22 '18 at 14:42











  • Can you elaborate a bit on what the code is doing?

    – Silvia
    Oct 22 '18 at 14:53











  • @Silvia, Sure, added an explanation.

    – jpp
    Oct 22 '18 at 15:25








1




1





Seems like there is no better way :-(

– Wen-Ben
Oct 22 '18 at 14:42





Seems like there is no better way :-(

– Wen-Ben
Oct 22 '18 at 14:42













Can you elaborate a bit on what the code is doing?

– Silvia
Oct 22 '18 at 14:53





Can you elaborate a bit on what the code is doing?

– Silvia
Oct 22 '18 at 14:53













@Silvia, Sure, added an explanation.

– jpp
Oct 22 '18 at 15:25





@Silvia, Sure, added an explanation.

– jpp
Oct 22 '18 at 15:25













2














IIUC



pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T
Out[997]:
0 1 2
A1 1 2 3
B1 10 11 12
C1 20 21 22
A1 11 12 13
B1 100 101 102
C1 200 201 202





share|improve this answer
























  • I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

    – Silvia
    Oct 22 '18 at 14:51






  • 1





    @Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

    – Wen-Ben
    Oct 22 '18 at 14:54











  • You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

    – Silvia
    Oct 22 '18 at 15:02
















2














IIUC



pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T
Out[997]:
0 1 2
A1 1 2 3
B1 10 11 12
C1 20 21 22
A1 11 12 13
B1 100 101 102
C1 200 201 202





share|improve this answer
























  • I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

    – Silvia
    Oct 22 '18 at 14:51






  • 1





    @Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

    – Wen-Ben
    Oct 22 '18 at 14:54











  • You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

    – Silvia
    Oct 22 '18 at 15:02














2












2








2







IIUC



pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T
Out[997]:
0 1 2
A1 1 2 3
B1 10 11 12
C1 20 21 22
A1 11 12 13
B1 100 101 102
C1 200 201 202





share|improve this answer













IIUC



pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T
Out[997]:
0 1 2
A1 1 2 3
B1 10 11 12
C1 20 21 22
A1 11 12 13
B1 100 101 102
C1 200 201 202






share|improve this answer












share|improve this answer



share|improve this answer










answered Oct 22 '18 at 14:30









Wen-BenWen-Ben

119k83569




119k83569













  • I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

    – Silvia
    Oct 22 '18 at 14:51






  • 1





    @Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

    – Wen-Ben
    Oct 22 '18 at 14:54











  • You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

    – Silvia
    Oct 22 '18 at 15:02



















  • I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

    – Silvia
    Oct 22 '18 at 14:51






  • 1





    @Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

    – Wen-Ben
    Oct 22 '18 at 14:54











  • You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

    – Silvia
    Oct 22 '18 at 15:02

















I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

– Silvia
Oct 22 '18 at 14:51





I am pandas 0.23.0, and unfortunatelly this didn't work for me. The df is transposed, but I am getting still a (3,5) sdf shape

– Silvia
Oct 22 '18 at 14:51




1




1





@Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

– Wen-Ben
Oct 22 '18 at 14:54





@Silvia it is work , you just forget assign it back df=pd.concat([x for _,x in df_given.groupby((df_given.index==0).cumsum())],axis=1).T

– Wen-Ben
Oct 22 '18 at 14:54













You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

– Silvia
Oct 22 '18 at 15:02





You are very right. My mistake. I am sorry, I have accepted the other answer already, I just could gave you a +.

– Silvia
Oct 22 '18 at 15:02











1














One alternative using groupby, stack & unstack



df1 = df_given.assign(g=df_given.groupby(level=0).cumcount()).set_index('g',append=True)

df1 = df1.stack().unstack(0).reset_index(drop=True)

df1.columns = df_given.columns

print (df1)


A1 B1 C1
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202





share|improve this answer



















  • 1





    This version also works.

    – Silvia
    Oct 22 '18 at 15:03
















1














One alternative using groupby, stack & unstack



df1 = df_given.assign(g=df_given.groupby(level=0).cumcount()).set_index('g',append=True)

df1 = df1.stack().unstack(0).reset_index(drop=True)

df1.columns = df_given.columns

print (df1)


A1 B1 C1
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202





share|improve this answer



















  • 1





    This version also works.

    – Silvia
    Oct 22 '18 at 15:03














1












1








1







One alternative using groupby, stack & unstack



df1 = df_given.assign(g=df_given.groupby(level=0).cumcount()).set_index('g',append=True)

df1 = df1.stack().unstack(0).reset_index(drop=True)

df1.columns = df_given.columns

print (df1)


A1 B1 C1
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202





share|improve this answer













One alternative using groupby, stack & unstack



df1 = df_given.assign(g=df_given.groupby(level=0).cumcount()).set_index('g',append=True)

df1 = df1.stack().unstack(0).reset_index(drop=True)

df1.columns = df_given.columns

print (df1)


A1 B1 C1
0 1 2 3
1 10 11 12
2 20 21 22
3 11 12 13
4 100 101 102
5 200 201 202






share|improve this answer












share|improve this answer



share|improve this answer










answered Oct 22 '18 at 14:54









AbhiAbhi

2,540421




2,540421








  • 1





    This version also works.

    – Silvia
    Oct 22 '18 at 15:03














  • 1





    This version also works.

    – Silvia
    Oct 22 '18 at 15:03








1




1





This version also works.

– Silvia
Oct 22 '18 at 15:03





This version also works.

– Silvia
Oct 22 '18 at 15:03


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52931586%2freshape-dataframe-columns-to-lines%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Xamarin.iOS Cant Deploy on Iphone

Glorious Revolution

Dulmage-Mendelsohn matrix decomposition in Python