reshape dataframe: columns to lines
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
add a comment |
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
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
add a comment |
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
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
python pandas dataframe reshape
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
add a comment |
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
add a comment |
3 Answers
3
active
oldest
votes
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 regularpd.RangeIndex
, i.e.0, 1, 2, ..., n
.
df.groupby(df.index // 3)
groups the dataframe in chunks of 3 rows. We iterate over thisGroupBy
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 topd.DataFrame
to construct a dataframe from a NumPy array.
pd.concat
takes an iterable of dataframes and combines them to construct a single dataframe.
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
add a comment |
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
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
add a comment |
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
1
This version also works.
– Silvia
Oct 22 '18 at 15:03
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/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
});
}
});
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
Required, but never shown
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
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 regularpd.RangeIndex
, i.e.0, 1, 2, ..., n
.
df.groupby(df.index // 3)
groups the dataframe in chunks of 3 rows. We iterate over thisGroupBy
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 topd.DataFrame
to construct a dataframe from a NumPy array.
pd.concat
takes an iterable of dataframes and combines them to construct a single dataframe.
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
add a comment |
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 regularpd.RangeIndex
, i.e.0, 1, 2, ..., n
.
df.groupby(df.index // 3)
groups the dataframe in chunks of 3 rows. We iterate over thisGroupBy
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 topd.DataFrame
to construct a dataframe from a NumPy array.
pd.concat
takes an iterable of dataframes and combines them to construct a single dataframe.
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
add a comment |
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 regularpd.RangeIndex
, i.e.0, 1, 2, ..., n
.
df.groupby(df.index // 3)
groups the dataframe in chunks of 3 rows. We iterate over thisGroupBy
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 topd.DataFrame
to construct a dataframe from a NumPy array.
pd.concat
takes an iterable of dataframes and combines them to construct a single dataframe.
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 regularpd.RangeIndex
, i.e.0, 1, 2, ..., n
.
df.groupby(df.index // 3)
groups the dataframe in chunks of 3 rows. We iterate over thisGroupBy
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 topd.DataFrame
to construct a dataframe from a NumPy array.
pd.concat
takes an iterable of dataframes and combines them to construct a single dataframe.
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
add a comment |
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
add a comment |
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
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
add a comment |
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
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
add a comment |
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
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
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
add a comment |
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
add a comment |
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
1
This version also works.
– Silvia
Oct 22 '18 at 15:03
add a comment |
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
1
This version also works.
– Silvia
Oct 22 '18 at 15:03
add a comment |
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
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
answered Oct 22 '18 at 14:54
AbhiAbhi
2,540421
2,540421
1
This version also works.
– Silvia
Oct 22 '18 at 15:03
add a comment |
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
add a comment |
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.
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
Required, but never shown
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
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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