pandas - how to get last n groups of a groupby object and combine them as a dataframe

up vote
7
down vote

favorite

How to get last 'n' groups after df.groupby() and combine them as a dataframe.

data = pd.read_sql_query(sql=sqlstr, con=sql_conn, index_col='SampleTime')
grouped = data.groupby(data.index.date,sort=False)

After doing grouped.ngroups i am getting total number of groups 277. I want to combine last 12 groups and generate a dataframe.

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

add a commentÂ |Â

up vote
7
down vote

favorite

How to get last 'n' groups after df.groupby() and combine them as a dataframe.

data = pd.read_sql_query(sql=sqlstr, con=sql_conn, index_col='SampleTime')
grouped = data.groupby(data.index.date,sort=False)

After doing grouped.ngroups i am getting total number of groups 277. I want to combine last 12 groups and generate a dataframe.

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

add a commentÂ |Â

up vote
7
down vote

favorite

How to get last 'n' groups after df.groupby() and combine them as a dataframe.

data = pd.read_sql_query(sql=sqlstr, con=sql_conn, index_col='SampleTime')
grouped = data.groupby(data.index.date,sort=False)

After doing grouped.ngroups i am getting total number of groups 277. I want to combine last 12 groups and generate a dataframe.

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

How to get last 'n' groups after df.groupby() and combine them as a dataframe.

data = pd.read_sql_query(sql=sqlstr, con=sql_conn, index_col='SampleTime')
grouped = data.groupby(data.index.date,sort=False)

After doing grouped.ngroups i am getting total number of groups 277. I want to combine last 12 groups and generate a dataframe.

python pandas pandas-groupby

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

edited 27 mins ago

jpp

73.3k184289

edited 27 mins ago

jpp

73.3k184289

edited 27 mins ago

jpp

73.3k184289

asked 1 hour ago

stockade

765

asked 1 hour ago

stockade

765

asked 1 hour ago

stockade

765

add a commentÂ |Â

5 Answers
5

active

oldest

votes

up vote
3
down vote

accepted

Pandas GroupBy objects are iterables. To extract the last n elements of an iterable, there's generally no need to create a list from the iterable and slice the last n elements. This will be memory-expensive.

Instead, you can use collections.deque and specify maxlen. Then use pd.concat to concatenate a sequence of dataframes.

from collections import deque
from operator import itemgetter

grouped = data.groupby(data.index.date, sort=False)
res = pd.concat(deque(map(itemgetter(1), grouped), maxlen=12))

As described in the collections docs:

Once a bounded length deque is full, when new items are added, a
corresponding number of items are discarded from the opposite end....
They are also useful for tracking transactions and other pools of data
where only the most recent activity is of interest.

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

add a commentÂ |Â

up vote
2
down vote

Assuming you know the order of grouped

grouped = zip(*df.groupby(data.index.date,sort=False))
pd.concat(list(grouped)[1][-12:])

answered 1 hour ago

RafaelC

24.3k72447

add a commentÂ |Â

up vote
0
down vote

You could pass a list comprehension to pd.concat():

import pandas as pd

df = pd.DataFrame([
['A',1,2],
['A',7,6],
['B',1,3],
['B',9,9],
['C',1,8],
['A',4,3],
['C',7,6],
['D',4,2]],
columns=['Var','Val1','Val2'])

last_n = 2
grouped = df.groupby('Var')

pd.concat([grouped.get_group(group) for i, group in enumerate(grouped.groups) if i>=len(grouped)-last_n])

Yields:

 Var Val1 Val2
4 C 1 8
6 C 7 6
7 D 4 2

answered 1 hour ago

rahlf23

3,3661427

add a commentÂ |Â

up vote
0
down vote

use pd.concat on lists comprehension and groupby.get_group

pd.concat([grouped.get_group(x) for x in list(grouped.groups.keys())[-12:]])

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

1

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

add a commentÂ |Â

up vote
0
down vote

You can use ngroup to subset the original DataFrame to find the last 12 groups

import numpy as np

dates = df.index.date
df[df.groupby(dates, sort=False).ngroup() >= len(np.unique(dates)) - 12]

Sample Data

import pandas as pd
df = pd.DataFrame('dates': pd.date_range('2013-01-01', '2014-01-01', freq ='12H'),
 'vals': np.random.randint(1,12,731)
 )
df = df.set_index('dates')

Output:

 vals
dates 
2013-12-21 00:00:00 5
2013-12-21 12:00:00 8
2013-12-22 00:00:00 3
2013-12-22 12:00:00 8
2013-12-23 00:00:00 2
...
2013-12-31 12:00:00 2
2014-01-01 00:00:00 5

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

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',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
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%2f52895305%2fpandas-how-to-get-last-n-groups-of-a-groupby-object-and-combine-them-as-a-data%23new-answer', 'question_page');

);

Post as a guest

Name

5 Answers
5

active

oldest

votes

5 Answers
5

active

oldest

votes

up vote
3
down vote

accepted

Instead, you can use collections.deque and specify maxlen. Then use pd.concat to concatenate a sequence of dataframes.

from collections import deque
from operator import itemgetter

grouped = data.groupby(data.index.date, sort=False)
res = pd.concat(deque(map(itemgetter(1), grouped), maxlen=12))

As described in the collections docs:

Once a bounded length deque is full, when new items are added, a
corresponding number of items are discarded from the opposite end....
They are also useful for tracking transactions and other pools of data
where only the most recent activity is of interest.

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

add a commentÂ |Â

up vote
3
down vote

accepted

Instead, you can use collections.deque and specify maxlen. Then use pd.concat to concatenate a sequence of dataframes.

from collections import deque
from operator import itemgetter

grouped = data.groupby(data.index.date, sort=False)
res = pd.concat(deque(map(itemgetter(1), grouped), maxlen=12))

As described in the collections docs:

Once a bounded length deque is full, when new items are added, a
corresponding number of items are discarded from the opposite end....
They are also useful for tracking transactions and other pools of data
where only the most recent activity is of interest.

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

add a commentÂ |Â

up vote
3
down vote

accepted

Instead, you can use collections.deque and specify maxlen. Then use pd.concat to concatenate a sequence of dataframes.

from collections import deque
from operator import itemgetter

grouped = data.groupby(data.index.date, sort=False)
res = pd.concat(deque(map(itemgetter(1), grouped), maxlen=12))

As described in the collections docs:

Once a bounded length deque is full, when new items are added, a
corresponding number of items are discarded from the opposite end....
They are also useful for tracking transactions and other pools of data
where only the most recent activity is of interest.

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

Instead, you can use collections.deque and specify maxlen. Then use pd.concat to concatenate a sequence of dataframes.

from collections import deque
from operator import itemgetter

grouped = data.groupby(data.index.date, sort=False)
res = pd.concat(deque(map(itemgetter(1), grouped), maxlen=12))

As described in the collections docs:

Once a bounded length deque is full, when new items are added, a
corresponding number of items are discarded from the opposite end....
They are also useful for tracking transactions and other pools of data
where only the most recent activity is of interest.

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

edited 30 mins ago

answered 39 mins ago

jpp

73.3k184289

answered 39 mins ago

jpp

73.3k184289

answered 39 mins ago

jpp

73.3k184289

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

add a commentÂ |Â

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

in your example where can it be seen that you recover the last 5?
â€“Â Yuca
37 mins ago

Great catch using deques, but you are still iterating over all groups. So the advantage is to save memory in this case, am I right? Good catch anyway
â€“Â RafaelC
33 mins ago

@RafaelC, Yes, the advantage is principally lower memory usage. You can't avoid iterating over all groups.
â€“Â jpp
32 mins ago

@Yuca, maxlen=12 here.
â€“Â jpp
29 mins ago

add a commentÂ |Â

up vote
2
down vote

Assuming you know the order of grouped

grouped = zip(*df.groupby(data.index.date,sort=False))
pd.concat(list(grouped)[1][-12:])

answered 1 hour ago

RafaelC

24.3k72447

add a commentÂ |Â

up vote
2
down vote

Assuming you know the order of grouped

grouped = zip(*df.groupby(data.index.date,sort=False))
pd.concat(list(grouped)[1][-12:])

answered 1 hour ago

RafaelC

24.3k72447

add a commentÂ |Â

up vote
2
down vote

Assuming you know the order of grouped

grouped = zip(*df.groupby(data.index.date,sort=False))
pd.concat(list(grouped)[1][-12:])

answered 1 hour ago

RafaelC

24.3k72447

Assuming you know the order of grouped

grouped = zip(*df.groupby(data.index.date,sort=False))
pd.concat(list(grouped)[1][-12:])

answered 1 hour ago

RafaelC

24.3k72447

answered 1 hour ago

RafaelC

24.3k72447

answered 1 hour ago

RafaelC

24.3k72447

answered 1 hour ago

RafaelC

24.3k72447

add a commentÂ |Â

up vote
0
down vote

You could pass a list comprehension to pd.concat():

import pandas as pd

df = pd.DataFrame([
['A',1,2],
['A',7,6],
['B',1,3],
['B',9,9],
['C',1,8],
['A',4,3],
['C',7,6],
['D',4,2]],
columns=['Var','Val1','Val2'])

last_n = 2
grouped = df.groupby('Var')

pd.concat([grouped.get_group(group) for i, group in enumerate(grouped.groups) if i>=len(grouped)-last_n])

Yields:

 Var Val1 Val2
4 C 1 8
6 C 7 6
7 D 4 2

answered 1 hour ago

rahlf23

3,3661427

add a commentÂ |Â

up vote
0
down vote

You could pass a list comprehension to pd.concat():

import pandas as pd

df = pd.DataFrame([
['A',1,2],
['A',7,6],
['B',1,3],
['B',9,9],
['C',1,8],
['A',4,3],
['C',7,6],
['D',4,2]],
columns=['Var','Val1','Val2'])

last_n = 2
grouped = df.groupby('Var')

pd.concat([grouped.get_group(group) for i, group in enumerate(grouped.groups) if i>=len(grouped)-last_n])

Yields:

 Var Val1 Val2
4 C 1 8
6 C 7 6
7 D 4 2

answered 1 hour ago

rahlf23

3,3661427

add a commentÂ |Â

up vote
0
down vote

You could pass a list comprehension to pd.concat():

import pandas as pd

df = pd.DataFrame([
['A',1,2],
['A',7,6],
['B',1,3],
['B',9,9],
['C',1,8],
['A',4,3],
['C',7,6],
['D',4,2]],
columns=['Var','Val1','Val2'])

last_n = 2
grouped = df.groupby('Var')

pd.concat([grouped.get_group(group) for i, group in enumerate(grouped.groups) if i>=len(grouped)-last_n])

Yields:

 Var Val1 Val2
4 C 1 8
6 C 7 6
7 D 4 2

answered 1 hour ago

rahlf23

3,3661427

You could pass a list comprehension to pd.concat():

import pandas as pd

df = pd.DataFrame([
['A',1,2],
['A',7,6],
['B',1,3],
['B',9,9],
['C',1,8],
['A',4,3],
['C',7,6],
['D',4,2]],
columns=['Var','Val1','Val2'])

last_n = 2
grouped = df.groupby('Var')

pd.concat([grouped.get_group(group) for i, group in enumerate(grouped.groups) if i>=len(grouped)-last_n])

Yields:

 Var Val1 Val2
4 C 1 8
6 C 7 6
7 D 4 2

answered 1 hour ago

rahlf23

3,3661427

answered 1 hour ago

rahlf23

3,3661427

answered 1 hour ago

rahlf23

3,3661427

answered 1 hour ago

rahlf23

3,3661427

add a commentÂ |Â

up vote
0
down vote

use pd.concat on lists comprehension and groupby.get_group

pd.concat([grouped.get_group(x) for x in list(grouped.groups.keys())[-12:]])

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

1

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

add a commentÂ |Â

up vote
0
down vote

use pd.concat on lists comprehension and groupby.get_group

pd.concat([grouped.get_group(x) for x in list(grouped.groups.keys())[-12:]])

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

1

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

add a commentÂ |Â

up vote
0
down vote

use pd.concat on lists comprehension and groupby.get_group

pd.concat([grouped.get_group(x) for x in list(grouped.groups.keys())[-12:]])

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

use pd.concat on lists comprehension and groupby.get_group

pd.concat([grouped.get_group(x) for x in list(grouped.groups.keys())[-12:]])

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

edited 51 mins ago

answered 1 hour ago

Yuca

2,0791420

answered 1 hour ago

Yuca

2,0791420

answered 1 hour ago

Yuca

2,0791420

1

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

add a commentÂ |Â

1

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

I think tail will return the last 12 entries of each group. Unless I misunderstood OP, I don't think that's what is desired...
â€“Â sacul
1 hour ago

sounds right. Re-thinking answer then
â€“Â Yuca
1 hour ago

I just tried this on a dataframe I was working on, and this seemed to be what OP asked for? ed: the concat didn't work, but .tail(12) returned the final 12 groups
â€“Â Mathew Savage
1 hour ago

new version should be aligned to what OP wants :) (although it doesn't provide much vs rahlf23's version)
â€“Â Yuca
44 mins ago

add a commentÂ |Â

up vote
0
down vote

You can use ngroup to subset the original DataFrame to find the last 12 groups

import numpy as np

dates = df.index.date
df[df.groupby(dates, sort=False).ngroup() >= len(np.unique(dates)) - 12]

Sample Data

import pandas as pd
df = pd.DataFrame('dates': pd.date_range('2013-01-01', '2014-01-01', freq ='12H'),
 'vals': np.random.randint(1,12,731)
 )
df = df.set_index('dates')

Output:

 vals
dates 
2013-12-21 00:00:00 5
2013-12-21 12:00:00 8
2013-12-22 00:00:00 3
2013-12-22 12:00:00 8
2013-12-23 00:00:00 2
...
2013-12-31 12:00:00 2
2014-01-01 00:00:00 5

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

add a commentÂ |Â

up vote
0
down vote

You can use ngroup to subset the original DataFrame to find the last 12 groups

import numpy as np

dates = df.index.date
df[df.groupby(dates, sort=False).ngroup() >= len(np.unique(dates)) - 12]

Sample Data

import pandas as pd
df = pd.DataFrame('dates': pd.date_range('2013-01-01', '2014-01-01', freq ='12H'),
 'vals': np.random.randint(1,12,731)
 )
df = df.set_index('dates')

Output:

 vals
dates 
2013-12-21 00:00:00 5
2013-12-21 12:00:00 8
2013-12-22 00:00:00 3
2013-12-22 12:00:00 8
2013-12-23 00:00:00 2
...
2013-12-31 12:00:00 2
2014-01-01 00:00:00 5

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

add a commentÂ |Â

up vote
0
down vote

You can use ngroup to subset the original DataFrame to find the last 12 groups

import numpy as np

dates = df.index.date
df[df.groupby(dates, sort=False).ngroup() >= len(np.unique(dates)) - 12]

Sample Data

import pandas as pd
df = pd.DataFrame('dates': pd.date_range('2013-01-01', '2014-01-01', freq ='12H'),
 'vals': np.random.randint(1,12,731)
 )
df = df.set_index('dates')

Output:

 vals
dates 
2013-12-21 00:00:00 5
2013-12-21 12:00:00 8
2013-12-22 00:00:00 3
2013-12-22 12:00:00 8
2013-12-23 00:00:00 2
...
2013-12-31 12:00:00 2
2014-01-01 00:00:00 5

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

You can use ngroup to subset the original DataFrame to find the last 12 groups

import numpy as np

dates = df.index.date
df[df.groupby(dates, sort=False).ngroup() >= len(np.unique(dates)) - 12]

Sample Data

import pandas as pd
df = pd.DataFrame('dates': pd.date_range('2013-01-01', '2014-01-01', freq ='12H'),
 'vals': np.random.randint(1,12,731)
 )
df = df.set_index('dates')

Output:

 vals
dates 
2013-12-21 00:00:00 5
2013-12-21 12:00:00 8
2013-12-22 00:00:00 3
2013-12-22 12:00:00 8
2013-12-23 00:00:00 2
...
2013-12-31 12:00:00 2
2014-01-01 00:00:00 5

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

edited 24 mins ago

answered 43 mins ago

ALollz

8,41531131

answered 43 mins ago

ALollz

8,41531131

answered 43 mins ago

ALollz

8,41531131

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky