how to set the columns in pandasHow to merge two dictionaries in a single expression?How do I check whether a...
Why did my leaking pool light trip the circuit breaker, but not the GFCI?
Cannot update a field to a Lookup, MasterDetail, or Hierarchy from something else (44:13)
What is the difference between figures illustration and images?
Are the plates of a battery really charged?
Can Monks cast spells?
Who are the police in Hong Kong?
I had an c.p.a file late returns, stating i would get money. but i.r.s. says they were filed too late
How come having a Deathly Hallow is not a big deal?
Should I cheat if the majority does it?
My players like to search everything. What do they find?
How might boat designs change in order to allow them to be pulled by dragons?
List of Implementations for common OR problems
How can I get a file's size with C++17?
In National Velvet why didn't they use a stunt double for Elizabeth Taylor?
Bypass with wrong cvv of debit card and getting OTP
My mother co-signed for my car. Can she take it away from me if I am the one making car payments?
Does a reference have a storage location?
how to set the columns in pandas
SQL Server error 242 with ANSI datetime
How did sloshing prevent the Apollo Service Module from moving safely away from the Command Module and how was this fixed?
Pandas merge and fillna
Has there ever been a cold war other than between the U.S. and the U.S.S.R.?
What could a Medieval society do with excess animal blood?
Contributing to a candidate as a Foreign National US Resident?
how to set the columns in pandas
How to merge two dictionaries in a single expression?How do I check whether a file exists without exceptions?Selecting multiple columns in a pandas dataframeRenaming columns in pandasAdding new column to existing DataFrame in Python pandasDelete column from pandas DataFrame“Large data” work flows using pandasHow to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headers
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
9
here is my dataframe -:
Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19
Saturday 2540.0 2441.0 3832.0 4093.0 1455.0 2552.0
Sunday 1313.0 1891.0 2968.0 2260.0 1454.0 1798.0
Monday 1360.0 1558.0 2967.0 2156.0 1564.0 1752.0
Tuesday 1089.0 2105.0 2476.0 1577.0 1744.0 1457.0
Wednesday 1329.0 1658.0 2073.0 2403.0 1231.0 874.0
Thursday 798.0 1195.0 2183.0 1287.0 1460.0 1269.0
i have tried some pandas ops but i am not able to do that
this is what i want to do
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
Sunday 1891.0
Monday 1558.0
Tuesday 2105.0
Wednesday 1658.0
Thursday 1195.0 ............ and so on
i want to set those rows into rows in downside
how to do that?
thanks in advance
python pandas dataframe
asked 10 hours ago
jonyjony
555 bronze badges
add a comment |
9
here is my dataframe -:
Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19
Saturday 2540.0 2441.0 3832.0 4093.0 1455.0 2552.0
Sunday 1313.0 1891.0 2968.0 2260.0 1454.0 1798.0
Monday 1360.0 1558.0 2967.0 2156.0 1564.0 1752.0
Tuesday 1089.0 2105.0 2476.0 1577.0 1744.0 1457.0
Wednesday 1329.0 1658.0 2073.0 2403.0 1231.0 874.0
Thursday 798.0 1195.0 2183.0 1287.0 1460.0 1269.0
i have tried some pandas ops but i am not able to do that
this is what i want to do
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
Sunday 1891.0
Monday 1558.0
Tuesday 2105.0
Wednesday 1658.0
Thursday 1195.0 ............ and so on
i want to set those rows into rows in downside
how to do that?
thanks in advance
python pandas dataframe
asked 10 hours ago
jonyjony
555 bronze badges
2
Try: df.reset_index().melt() by index
– political scientist
10 hours ago
add a comment |
9
9
9
2
here is my dataframe -:
Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19
Saturday 2540.0 2441.0 3832.0 4093.0 1455.0 2552.0
Sunday 1313.0 1891.0 2968.0 2260.0 1454.0 1798.0
Monday 1360.0 1558.0 2967.0 2156.0 1564.0 1752.0
Tuesday 1089.0 2105.0 2476.0 1577.0 1744.0 1457.0
Wednesday 1329.0 1658.0 2073.0 2403.0 1231.0 874.0
Thursday 798.0 1195.0 2183.0 1287.0 1460.0 1269.0
i have tried some pandas ops but i am not able to do that
this is what i want to do
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
Sunday 1891.0
Monday 1558.0
Tuesday 2105.0
Wednesday 1658.0
Thursday 1195.0 ............ and so on
i want to set those rows into rows in downside
how to do that?
thanks in advance
python pandas dataframe
asked 10 hours ago
jonyjony
555 bronze badges
here is my dataframe -:
Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19
Saturday 2540.0 2441.0 3832.0 4093.0 1455.0 2552.0
Sunday 1313.0 1891.0 2968.0 2260.0 1454.0 1798.0
Monday 1360.0 1558.0 2967.0 2156.0 1564.0 1752.0
Tuesday 1089.0 2105.0 2476.0 1577.0 1744.0 1457.0
Wednesday 1329.0 1658.0 2073.0 2403.0 1231.0 874.0
Thursday 798.0 1195.0 2183.0 1287.0 1460.0 1269.0
i have tried some pandas ops but i am not able to do that
this is what i want to do
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
Sunday 1891.0
Monday 1558.0
Tuesday 2105.0
Wednesday 1658.0
Thursday 1195.0 ............ and so on
i want to set those rows into rows in downside
how to do that?
thanks in advance
python pandas dataframe
python pandas dataframe
asked 10 hours ago
jonyjony
555 bronze badges
asked 10 hours ago
jonyjony
555 bronze badges
asked 10 hours ago
jonyjony
555 bronze badges
asked 10 hours ago
jonyjony
555 bronze badges
asked 10 hours ago
jonyjony
555 bronze badges
555 bronze badges
2
Try: df.reset_index().melt() by index
– political scientist
10 hours ago
add a comment |
2
Try: df.reset_index().melt() by index
– political scientist
10 hours ago
2
2
Try: df.reset_index().melt() by index
– political scientist
10 hours ago
Try: df.reset_index().melt() by index
– political scientist
10 hours ago
add a comment |
3 Answers
3
active
oldest
votes
6
df.reset_index().melt(id_vars='index').drop('variable',1)
Output:
index value
0 Saturday 2540.0
1 Sunday 1313.0
2 Monday 1360.0
3 Tuesday 1089.0
4 Wednesday 1329.0
5 Thursday 798.0
6 Saturday 2441.0
7 Sunday 1891.0
8 Monday 1558.0
9 Tuesday 2105.0
10 Wednesday 1658.0
11 Thursday 1195.0
12 Saturday 3832.0
13 Sunday 2968.0
14 Monday 2967.0
15 Tuesday 2476.0
16 Wednesday 2073.0
17 Thursday 2183.0
18 Saturday 4093.0
19 Sunday 2260.0
20 Monday 2156.0
21 Tuesday 1577.0
22 Wednesday 2403.0
23 Thursday 1287.0
24 Saturday 1455.0
25 Sunday 1454.0
26 Monday 1564.0
27 Tuesday 1744.0
28 Wednesday 1231.0
29 Thursday 1460.0
30 Saturday 2552.0
31 Sunday 1798.0
32 Monday 1752.0
33 Tuesday 1457.0
34 Wednesday 874.0
35 Thursday 1269.0
Note: just noted a commented suggesting to do the same thing, I will delete my post if requested :)
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
add a comment |
7
Create it with numpy by reshaping the data.
import pandas as pd
import numpy as np
pd.DataFrame(df.to_numpy().flatten('F'),
index=np.tile(df.index, df.shape[1]),
columns=['items'])
Output:
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
...
Sunday 1798.0
Monday 1752.0
Tuesday 1457.0
Wednesday 874.0
Thursday 1269.0
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
1
My answer was virtually identical to this.a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])
– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
Minor fix: the argument tonp.tileshould bedf.shape[1]instead ofdf.shape[0], which only happens to work on this example data because it is square!
– Peter Leimbigler
4 hours ago
add a comment |
3
You can do:
df = df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
It is interesting that this method got overlooked even though it is the fastest:
import time
start = time.time()
df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.006181955337524414
while this:
start = time.time()
df.reset_index().melt(id_vars='days').drop('variable',1)
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.010072708129882812
Any my output format matches OP's requested exactly.
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
Interesting: why does this work? I would expectdf.stack().sort_index(level=1)to lexicographically sort the stringsDec-18,Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects.df.stack().index.get_level_values(1).sort_values()lexsorts.
– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]})then:df = df.set_index('days')then:df.stack().sort_index(level=1)Then go back and change the order of the columns and see what appears first.
– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the.stack()returns a DataFrame (or if you convert to DataFrame using.stack().to_frame()), the index no longer remembers the order of the original columns.
– Peter Leimbigler
9 hours ago
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, usedf.unstack().reset_index(0, drop=True).to_frame('items'). Byunstack-ing rather thanstack-ing, you save yourself from the sorting shenanigans.
– piRSquared
9 hours ago
|
show 2 more comments
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
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%2f56841907%2fhow-to-set-the-columns-in-pandas%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
6
df.reset_index().melt(id_vars='index').drop('variable',1)
Output:
index value
0 Saturday 2540.0
1 Sunday 1313.0
2 Monday 1360.0
3 Tuesday 1089.0
4 Wednesday 1329.0
5 Thursday 798.0
6 Saturday 2441.0
7 Sunday 1891.0
8 Monday 1558.0
9 Tuesday 2105.0
10 Wednesday 1658.0
11 Thursday 1195.0
12 Saturday 3832.0
13 Sunday 2968.0
14 Monday 2967.0
15 Tuesday 2476.0
16 Wednesday 2073.0
17 Thursday 2183.0
18 Saturday 4093.0
19 Sunday 2260.0
20 Monday 2156.0
21 Tuesday 1577.0
22 Wednesday 2403.0
23 Thursday 1287.0
24 Saturday 1455.0
25 Sunday 1454.0
26 Monday 1564.0
27 Tuesday 1744.0
28 Wednesday 1231.0
29 Thursday 1460.0
30 Saturday 2552.0
31 Sunday 1798.0
32 Monday 1752.0
33 Tuesday 1457.0
34 Wednesday 874.0
35 Thursday 1269.0
Note: just noted a commented suggesting to do the same thing, I will delete my post if requested :)
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
add a comment |
6
df.reset_index().melt(id_vars='index').drop('variable',1)
Output:
index value
0 Saturday 2540.0
1 Sunday 1313.0
2 Monday 1360.0
3 Tuesday 1089.0
4 Wednesday 1329.0
5 Thursday 798.0
6 Saturday 2441.0
7 Sunday 1891.0
8 Monday 1558.0
9 Tuesday 2105.0
10 Wednesday 1658.0
11 Thursday 1195.0
12 Saturday 3832.0
13 Sunday 2968.0
14 Monday 2967.0
15 Tuesday 2476.0
16 Wednesday 2073.0
17 Thursday 2183.0
18 Saturday 4093.0
19 Sunday 2260.0
20 Monday 2156.0
21 Tuesday 1577.0
22 Wednesday 2403.0
23 Thursday 1287.0
24 Saturday 1455.0
25 Sunday 1454.0
26 Monday 1564.0
27 Tuesday 1744.0
28 Wednesday 1231.0
29 Thursday 1460.0
30 Saturday 2552.0
31 Sunday 1798.0
32 Monday 1752.0
33 Tuesday 1457.0
34 Wednesday 874.0
35 Thursday 1269.0
Note: just noted a commented suggesting to do the same thing, I will delete my post if requested :)
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
add a comment |
6
6
6
df.reset_index().melt(id_vars='index').drop('variable',1)
Output:
index value
0 Saturday 2540.0
1 Sunday 1313.0
2 Monday 1360.0
3 Tuesday 1089.0
4 Wednesday 1329.0
5 Thursday 798.0
6 Saturday 2441.0
7 Sunday 1891.0
8 Monday 1558.0
9 Tuesday 2105.0
10 Wednesday 1658.0
11 Thursday 1195.0
12 Saturday 3832.0
13 Sunday 2968.0
14 Monday 2967.0
15 Tuesday 2476.0
16 Wednesday 2073.0
17 Thursday 2183.0
18 Saturday 4093.0
19 Sunday 2260.0
20 Monday 2156.0
21 Tuesday 1577.0
22 Wednesday 2403.0
23 Thursday 1287.0
24 Saturday 1455.0
25 Sunday 1454.0
26 Monday 1564.0
27 Tuesday 1744.0
28 Wednesday 1231.0
29 Thursday 1460.0
30 Saturday 2552.0
31 Sunday 1798.0
32 Monday 1752.0
33 Tuesday 1457.0
34 Wednesday 874.0
35 Thursday 1269.0
Note: just noted a commented suggesting to do the same thing, I will delete my post if requested :)
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
df.reset_index().melt(id_vars='index').drop('variable',1)
Output:
index value
0 Saturday 2540.0
1 Sunday 1313.0
2 Monday 1360.0
3 Tuesday 1089.0
4 Wednesday 1329.0
5 Thursday 798.0
6 Saturday 2441.0
7 Sunday 1891.0
8 Monday 1558.0
9 Tuesday 2105.0
10 Wednesday 1658.0
11 Thursday 1195.0
12 Saturday 3832.0
13 Sunday 2968.0
14 Monday 2967.0
15 Tuesday 2476.0
16 Wednesday 2073.0
17 Thursday 2183.0
18 Saturday 4093.0
19 Sunday 2260.0
20 Monday 2156.0
21 Tuesday 1577.0
22 Wednesday 2403.0
23 Thursday 1287.0
24 Saturday 1455.0
25 Sunday 1454.0
26 Monday 1564.0
27 Tuesday 1744.0
28 Wednesday 1231.0
29 Thursday 1460.0
30 Saturday 2552.0
31 Sunday 1798.0
32 Monday 1752.0
33 Tuesday 1457.0
34 Wednesday 874.0
35 Thursday 1269.0
Note: just noted a commented suggesting to do the same thing, I will delete my post if requested :)
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
answered 10 hours ago
YucaYuca
3,8032 gold badges10 silver badges27 bronze badges
3,8032 gold badges10 silver badges27 bronze badges
add a comment |
add a comment |
7
Create it with numpy by reshaping the data.
import pandas as pd
import numpy as np
pd.DataFrame(df.to_numpy().flatten('F'),
index=np.tile(df.index, df.shape[1]),
columns=['items'])
Output:
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
...
Sunday 1798.0
Monday 1752.0
Tuesday 1457.0
Wednesday 874.0
Thursday 1269.0
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
1
My answer was virtually identical to this.a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])
– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
Minor fix: the argument tonp.tileshould bedf.shape[1]instead ofdf.shape[0], which only happens to work on this example data because it is square!
– Peter Leimbigler
4 hours ago
add a comment |
7
Create it with numpy by reshaping the data.
import pandas as pd
import numpy as np
pd.DataFrame(df.to_numpy().flatten('F'),
index=np.tile(df.index, df.shape[1]),
columns=['items'])
Output:
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
...
Sunday 1798.0
Monday 1752.0
Tuesday 1457.0
Wednesday 874.0
Thursday 1269.0
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
1
My answer was virtually identical to this.a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])
– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
Minor fix: the argument tonp.tileshould bedf.shape[1]instead ofdf.shape[0], which only happens to work on this example data because it is square!
– Peter Leimbigler
4 hours ago
add a comment |
7
7
7
Create it with numpy by reshaping the data.
import pandas as pd
import numpy as np
pd.DataFrame(df.to_numpy().flatten('F'),
index=np.tile(df.index, df.shape[1]),
columns=['items'])
Output:
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
...
Sunday 1798.0
Monday 1752.0
Tuesday 1457.0
Wednesday 874.0
Thursday 1269.0
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
Create it with numpy by reshaping the data.
import pandas as pd
import numpy as np
pd.DataFrame(df.to_numpy().flatten('F'),
index=np.tile(df.index, df.shape[1]),
columns=['items'])
Output:
items
Saturday 2540.0
Sunday 1313.0
Monday 1360.0
Tuesday 1089.0
Wednesday 1329.0
Thursday 798.0
Saturday 2441.0
...
Sunday 1798.0
Monday 1752.0
Tuesday 1457.0
Wednesday 874.0
Thursday 1269.0
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
edited 4 hours ago
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
answered 10 hours ago
ALollzALollz
19.8k5 gold badges18 silver badges40 bronze badges
19.8k5 gold badges18 silver badges40 bronze badges
1
My answer was virtually identical to this.a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])
– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
Minor fix: the argument tonp.tileshould bedf.shape[1]instead ofdf.shape[0], which only happens to work on this example data because it is square!
– Peter Leimbigler
4 hours ago
add a comment |
1
My answer was virtually identical to this.a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])
– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
Minor fix: the argument tonp.tileshould bedf.shape[1]instead ofdf.shape[0], which only happens to work on this example data because it is square!
– Peter Leimbigler
4 hours ago
1
1
My answer was virtually identical to this.
a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])– piRSquared
10 hours ago
My answer was virtually identical to this.
a = df.to_numpy(); pd.DataFrame(np.reshape(a, (-1, 1), 'F'), np.resize(df.index, a.size), ['items'])– piRSquared
10 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared my answer was faster than the accepted answer and matches the output requested exactly, while the accepted answer does not. Mine was also first posted. Sometimes things just don't make sense do they :P.
– d_kennetz
9 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
@piRSquared want me to add yours to this solution? (or feel free to edit yourself :D)
– ALollz
7 hours ago
1
1
Minor fix: the argument to
np.tile should be df.shape[1] instead of df.shape[0], which only happens to work on this example data because it is square!– Peter Leimbigler
4 hours ago
Minor fix: the argument to
np.tile should be df.shape[1] instead of df.shape[0], which only happens to work on this example data because it is square!– Peter Leimbigler
4 hours ago
add a comment |
3
You can do:
df = df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
It is interesting that this method got overlooked even though it is the fastest:
import time
start = time.time()
df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.006181955337524414
while this:
start = time.time()
df.reset_index().melt(id_vars='days').drop('variable',1)
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.010072708129882812
Any my output format matches OP's requested exactly.
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
Interesting: why does this work? I would expectdf.stack().sort_index(level=1)to lexicographically sort the stringsDec-18,Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects.df.stack().index.get_level_values(1).sort_values()lexsorts.
– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]})then:df = df.set_index('days')then:df.stack().sort_index(level=1)Then go back and change the order of the columns and see what appears first.
– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the.stack()returns a DataFrame (or if you convert to DataFrame using.stack().to_frame()), the index no longer remembers the order of the original columns.
– Peter Leimbigler
9 hours ago
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, usedf.unstack().reset_index(0, drop=True).to_frame('items'). Byunstack-ing rather thanstack-ing, you save yourself from the sorting shenanigans.
– piRSquared
9 hours ago
|
show 2 more comments
3
You can do:
df = df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
It is interesting that this method got overlooked even though it is the fastest:
import time
start = time.time()
df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.006181955337524414
while this:
start = time.time()
df.reset_index().melt(id_vars='days').drop('variable',1)
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.010072708129882812
Any my output format matches OP's requested exactly.
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
Interesting: why does this work? I would expectdf.stack().sort_index(level=1)to lexicographically sort the stringsDec-18,Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects.df.stack().index.get_level_values(1).sort_values()lexsorts.
– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]})then:df = df.set_index('days')then:df.stack().sort_index(level=1)Then go back and change the order of the columns and see what appears first.
– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the.stack()returns a DataFrame (or if you convert to DataFrame using.stack().to_frame()), the index no longer remembers the order of the original columns.
– Peter Leimbigler
9 hours ago
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, usedf.unstack().reset_index(0, drop=True).to_frame('items'). Byunstack-ing rather thanstack-ing, you save yourself from the sorting shenanigans.
– piRSquared
9 hours ago
|
show 2 more comments
3
3
3
You can do:
df = df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
It is interesting that this method got overlooked even though it is the fastest:
import time
start = time.time()
df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.006181955337524414
while this:
start = time.time()
df.reset_index().melt(id_vars='days').drop('variable',1)
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.010072708129882812
Any my output format matches OP's requested exactly.
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
You can do:
df = df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
It is interesting that this method got overlooked even though it is the fastest:
import time
start = time.time()
df.stack().sort_index(level=1).reset_index(level = 1, drop=True).to_frame('items')
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.006181955337524414
while this:
start = time.time()
df.reset_index().melt(id_vars='days').drop('variable',1)
end = time.time()
print("time taken {}".format(end-start))
yields: time taken 0.010072708129882812
Any my output format matches OP's requested exactly.
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
edited 9 hours ago
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
answered 10 hours ago
d_kennetzd_kennetz
2,8014 gold badges9 silver badges28 bronze badges
2,8014 gold badges9 silver badges28 bronze badges
Interesting: why does this work? I would expectdf.stack().sort_index(level=1)to lexicographically sort the stringsDec-18,Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects.df.stack().index.get_level_values(1).sort_values()lexsorts.
– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]})then:df = df.set_index('days')then:df.stack().sort_index(level=1)Then go back and change the order of the columns and see what appears first.
– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the.stack()returns a DataFrame (or if you convert to DataFrame using.stack().to_frame()), the index no longer remembers the order of the original columns.
– Peter Leimbigler
9 hours ago
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, usedf.unstack().reset_index(0, drop=True).to_frame('items'). Byunstack-ing rather thanstack-ing, you save yourself from the sorting shenanigans.
– piRSquared
9 hours ago
|
show 2 more comments
Interesting: why does this work? I would expectdf.stack().sort_index(level=1)to lexicographically sort the stringsDec-18,Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects.df.stack().index.get_level_values(1).sort_values()lexsorts.
– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]})then:df = df.set_index('days')then:df.stack().sort_index(level=1)Then go back and change the order of the columns and see what appears first.
– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the.stack()returns a DataFrame (or if you convert to DataFrame using.stack().to_frame()), the index no longer remembers the order of the original columns.
– Peter Leimbigler
9 hours ago
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, usedf.unstack().reset_index(0, drop=True).to_frame('items'). Byunstack-ing rather thanstack-ing, you save yourself from the sorting shenanigans.
– piRSquared
9 hours ago
Interesting: why does this work? I would expect
df.stack().sort_index(level=1) to lexicographically sort the strings Dec-18, Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects. df.stack().index.get_level_values(1).sort_values() lexsorts.– Peter Leimbigler
10 hours ago
Interesting: why does this work? I would expect
df.stack().sort_index(level=1) to lexicographically sort the strings Dec-18, Jan-19, etc., but in fact they get sorted in date order, even if they're strings and not datetime objects. df.stack().index.get_level_values(1).sort_values() lexsorts.– Peter Leimbigler
10 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:
df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]}) then: df = df.set_index('days') then: df.stack().sort_index(level=1) Then go back and change the order of the columns and see what appears first.– d_kennetz
9 hours ago
@PeterLeimbigler it is sorting based on the order of the columns, not datetime or string. If jan-19 was the first column that would've been sorted first. try it using this setup:
df = pd.DataFrame({'days': ['Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday'], 'Dec-18': [400,300,200,100,1000,1200], 'Jan-19': [500, 300, 200, 800, 900, 1000]}) then: df = df.set_index('days') then: df.stack().sort_index(level=1) Then go back and change the order of the columns and see what appears first.– d_kennetz
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the
.stack() returns a DataFrame (or if you convert to DataFrame using .stack().to_frame()), the index no longer remembers the order of the original columns.– Peter Leimbigler
9 hours ago
Thanks for the explanation! This is unexpected behaviour to me. From my testing, it appears that if you stack a DataFrame's columns into a MultiIndex and the result is a Series, then the index remembers the order of the columns, and sorts according to that order. But if the
.stack() returns a DataFrame (or if you convert to DataFrame using .stack().to_frame()), the index no longer remembers the order of the original columns.– Peter Leimbigler
9 hours ago
2
2
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
@d_kennetz sometimes they do not. I usually think of answers as general ideas. I judge them accordingly. I give credit for ingenuity and presentation/explanation. I like to see the output from proposed solutions because all to often answers provide a solution that doesn't produce correct output. This doesn't show the results. Also, most of the time, DataFrames aren't big enough for performance to matter. OP goes with what is most understandable to them. Keep up the good fight and answer questions that are beneficial long term. (-:
– piRSquared
9 hours ago
Also, use
df.unstack().reset_index(0, drop=True).to_frame('items'). By unstack-ing rather than stack-ing, you save yourself from the sorting shenanigans.– piRSquared
9 hours ago
Also, use
df.unstack().reset_index(0, drop=True).to_frame('items'). By unstack-ing rather than stack-ing, you save yourself from the sorting shenanigans.– piRSquared
9 hours ago
|
show 2 more comments
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
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%2f56841907%2fhow-to-set-the-columns-in-pandas%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
2
Try: df.reset_index().melt() by index
– political scientist
10 hours ago