Ggtcf

Can you teleport closer to a creature you are Frightened of?

What steps are necessary to read a Modern SSD in Medieval Europe?

What flight has the highest ratio of timezone difference to flight time?

Film where the government was corrupt with aliens, people sent to kill aliens are given rigged visors not showing the right aliens

Is Nisuin Biblical or Rabbinic?

Purpose of level-shifter with same in and out voltages

Is fine stranded wire ok for main supply line?

What does "shotgun unity" refer to here in this sentence?

What difference does it make using sed with/without whitespaces?

Spaces in which all closed sets are regular closed

Is it ever safe to open a suspicious HTML file (e.g. email attachment)?

Why don't programming languages automatically manage the synchronous/asynchronous problem?

Getting Stale Gas Out of a Gas Tank w/out Dropping the Tank

Could a dragon use its wings to swim?

Airplane gently rocking its wings during whole flight

Does higher Oxidation/ reduction potential translate to higher energy storage in battery?

IC has pull-down resistors on SMBus lines?

How can the PCs determine if an item is a phylactery?

If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?

Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?

What would be the main consequences for a country leaving the WTO?

How to get the last not-null value in an ordered column of a huge table?

Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?

Lucky Feat: How can "more than one creature spend a luck point to influence the outcome of a roll"?

Pandas: Pad data-frame to max row length

0

I have data-frame like the following:

df = pd.DataFrame("id": [100, 200, 200, 300, 300, 300], "val1": [1.5, 2.5, 4.5, np.nan, 6.5, np.nan], "val2": [9.5, 7.5, 8.5, 3.5, np.nan, np.nan])

What I want to achieve is to zero pad each group (assuming that the data-frame is grouped by id), to the max number of rows for all groups. The max number of rows per id for the data-frame above is 3, so the resulting data-frame should look like the following:

df_true = pd.DataFrame("id": [100, 100, 100, 200, 200 ,200, 300, 300, 300], "val1": [1.5, 0, 0, 2.5, 4.5, 0, np.nan, 6.5, np.nan], "val2": [9.5, 0, 0, 7.5, 8.5, 0, 3.5, np.nan, np.nan])

Can someone point me in the right direction how to achieve that?

python pandas pandas-groupby

share|improve this question

asked Mar 8 at 17:35

gorjangorjan

1,443615

add a comment | 

0

I have data-frame like the following:

df = pd.DataFrame("id": [100, 200, 200, 300, 300, 300], "val1": [1.5, 2.5, 4.5, np.nan, 6.5, np.nan], "val2": [9.5, 7.5, 8.5, 3.5, np.nan, np.nan])

What I want to achieve is to zero pad each group (assuming that the data-frame is grouped by id), to the max number of rows for all groups. The max number of rows per id for the data-frame above is 3, so the resulting data-frame should look like the following:

df_true = pd.DataFrame("id": [100, 100, 100, 200, 200 ,200, 300, 300, 300], "val1": [1.5, 0, 0, 2.5, 4.5, 0, np.nan, 6.5, np.nan], "val2": [9.5, 0, 0, 7.5, 8.5, 0, 3.5, np.nan, np.nan])

Can someone point me in the right direction how to achieve that?

python pandas pandas-groupby

share|improve this question

asked Mar 8 at 17:35

gorjangorjan

1,443615

add a comment | 

I have data-frame like the following:

df = pd.DataFrame("id": [100, 200, 200, 300, 300, 300], "val1": [1.5, 2.5, 4.5, np.nan, 6.5, np.nan], "val2": [9.5, 7.5, 8.5, 3.5, np.nan, np.nan])

What I want to achieve is to zero pad each group (assuming that the data-frame is grouped by id), to the max number of rows for all groups. The max number of rows per id for the data-frame above is 3, so the resulting data-frame should look like the following:

df_true = pd.DataFrame("id": [100, 100, 100, 200, 200 ,200, 300, 300, 300], "val1": [1.5, 0, 0, 2.5, 4.5, 0, np.nan, 6.5, np.nan], "val2": [9.5, 0, 0, 7.5, 8.5, 0, 3.5, np.nan, np.nan])

Can someone point me in the right direction how to achieve that?

python pandas pandas-groupby

share|improve this question

asked Mar 8 at 17:35

gorjangorjan

1,443615

df = pd.DataFrame("id": [100, 200, 200, 300, 300, 300], "val1": [1.5, 2.5, 4.5, np.nan, 6.5, np.nan], "val2": [9.5, 7.5, 8.5, 3.5, np.nan, np.nan])

df_true = pd.DataFrame("id": [100, 100, 100, 200, 200 ,200, 300, 300, 300], "val1": [1.5, 0, 0, 2.5, 4.5, 0, np.nan, 6.5, np.nan], "val2": [9.5, 0, 0, 7.5, 8.5, 0, 3.5, np.nan, np.nan])

share|improve this question

asked Mar 8 at 17:35

gorjangorjan

1,443615

share|improve this question

asked Mar 8 at 17:35

gorjangorjan

1,443615

asked Mar 8 at 17:35

gorjangorjan

1,443615

asked Mar 8 at 17:35

gorjangorjan

1,443615

1 Answer
1

active

oldest

votes

1

So we do with cumcount with id then using stack and unstack

df['new']=df.groupby('id').cumcount()
df_true=df.set_index(['id','new']).unstack(fill_value=0).stack(dropna=False).reset_index('id')
df_true
Out[908]: 
 id val1 val2
new 
0 100 1.5 9.5
1 100 0.0 0.0
2 100 0.0 0.0
0 200 2.5 7.5
1 200 4.5 8.5
2 200 0.0 0.0
0 300 NaN 3.5
1 300 6.5 NaN
2 300 NaN NaN

share|improve this answer

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

add a comment | 

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55068265%2fpandas-pad-data-frame-to-max-row-length%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

So we do with cumcount with id then using stack and unstack

df['new']=df.groupby('id').cumcount()
df_true=df.set_index(['id','new']).unstack(fill_value=0).stack(dropna=False).reset_index('id')
df_true
Out[908]: 
 id val1 val2
new 
0 100 1.5 9.5
1 100 0.0 0.0
2 100 0.0 0.0
0 200 2.5 7.5
1 200 4.5 8.5
2 200 0.0 0.0
0 300 NaN 3.5
1 300 6.5 NaN
2 300 NaN NaN

share|improve this answer

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

add a comment | 

1

So we do with cumcount with id then using stack and unstack

df['new']=df.groupby('id').cumcount()
df_true=df.set_index(['id','new']).unstack(fill_value=0).stack(dropna=False).reset_index('id')
df_true
Out[908]: 
 id val1 val2
new 
0 100 1.5 9.5
1 100 0.0 0.0
2 100 0.0 0.0
0 200 2.5 7.5
1 200 4.5 8.5
2 200 0.0 0.0
0 300 NaN 3.5
1 300 6.5 NaN
2 300 NaN NaN

share|improve this answer

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

add a comment | 

So we do with cumcount with id then using stack and unstack

df['new']=df.groupby('id').cumcount()
df_true=df.set_index(['id','new']).unstack(fill_value=0).stack(dropna=False).reset_index('id')
df_true
Out[908]: 
 id val1 val2
new 
0 100 1.5 9.5
1 100 0.0 0.0
2 100 0.0 0.0
0 200 2.5 7.5
1 200 4.5 8.5
2 200 0.0 0.0
0 300 NaN 3.5
1 300 6.5 NaN
2 300 NaN NaN

share|improve this answer

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

df['new']=df.groupby('id').cumcount()
df_true=df.set_index(['id','new']).unstack(fill_value=0).stack(dropna=False).reset_index('id')
df_true
Out[908]: 
 id val1 val2
new 
0 100 1.5 9.5
1 100 0.0 0.0
2 100 0.0 0.0
0 200 2.5 7.5
1 200 4.5 8.5
2 200 0.0 0.0
0 300 NaN 3.5
1 300 6.5 NaN
2 300 NaN NaN

share|improve this answer

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571

answered Mar 8 at 17:39

Wen-BenWen-Ben

122k83571