Ggtcf

Recursively move files within sub directories

Make a Bowl of Alphabet Soup

Not hide and seek

Air travel with refrigerated insulin

Reason why a kingside attack is not justified

Strange behavior in TikZ draw command

C++ lambda syntax

How to detect sounds in IPA spelling

Calculate Pi using Monte Carlo

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

1 John in Luther’s Bibel

Taking the numerator and the denominator

What should be the ideal length of sentences in a blog post for ease of reading?

Can creatures abilities target that creature itself?

New Order #2: Turn My Way

Why is participating in the European Parliamentary elections used as a threat?

What is the purpose of using a decision tree?

What 1968 Moog synthesizer was used in the Movie Apollo 11?

Do native speakers use "ultima" and "proxima" frequently in spoken English?

What is the period/term used describe Giuseppe Arcimboldo's style of painting?

How to test the sharpness of a knife?

Is this saw blade faulty?

Why doesn't Gödel's incompleteness theorem apply to false statements?

How to get directions in deep space?

Advanced Pivot Table in Pandas

Add one row to pandas DataFrameSelecting multiple columns in a pandas dataframeRenaming columns in pandasAdding new column to existing DataFrame in Python pandasDelete column from pandas DataFrame by column name“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 pandasOrganizing data read from Excel to Pandas DataFrameGet list from pandas DataFrame column headers

1

I am trying to optimize some table transformation scripts in Python Pandas, which I am trying to feed with huge data sets (above 50k rows). I wrote a script that iterates through every index and parses values into a new data frame (see example below), but I am experiencing performance issues. Is there any pandas function, that could get the same results without iterating?

Example code:

from datetime import datetime
import pandas as pd

date1 = datetime(2019,1,1)
date2 = datetime(2019,1,2)

df = pd.DataFrame("ID": [1,1,2,2,3,3],
 "date": [date1,date2,date1,date2,date1,date2],
 "x": [1,2,3,4,5,6],
 "y": ["a","a","b","b","c","c"])


new_df = pd.DataFrame()
for i in df.index:

 new_df.at[df.at[i, "ID"], "y"] = df.at[i, "y"]

 if df.at[i, "date"] == datetime(2019,1,1):
 new_df.at[df.at[i, "ID"], "x1"] = df.at[i, "x"]
 elif df.at[i, "date"] == datetime(2019,1,2):
 new_df.at[df.at[i, "ID"], "x2"] = df.at[i, "x"]

output:

 ID date x y
0 1 2019-01-01 1 a
1 1 2019-01-02 2 a
2 2 2019-01-01 3 b
3 2 2019-01-02 4 b
4 3 2019-01-01 5 c
5 3 2019-01-02 6 c

 y x1 x2
1 a 1.0 2.0
2 b 3.0 4.0
3 c 5.0 6.0

The transformation basically groups the rows by the "ID" column and gets the "x1" values from the rows with date 2019-01-01, and the "x2" values from the rows with date 2019-01-02. The "y" value is the same within the same "ID". "ID" columns become the new indexes.

I'd appreciate any advice on this matter.

python pandas pivot-table

share|improve this question

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

add a comment | 

1

I am trying to optimize some table transformation scripts in Python Pandas, which I am trying to feed with huge data sets (above 50k rows). I wrote a script that iterates through every index and parses values into a new data frame (see example below), but I am experiencing performance issues. Is there any pandas function, that could get the same results without iterating?

Example code:

from datetime import datetime
import pandas as pd

date1 = datetime(2019,1,1)
date2 = datetime(2019,1,2)

df = pd.DataFrame("ID": [1,1,2,2,3,3],
 "date": [date1,date2,date1,date2,date1,date2],
 "x": [1,2,3,4,5,6],
 "y": ["a","a","b","b","c","c"])


new_df = pd.DataFrame()
for i in df.index:

 new_df.at[df.at[i, "ID"], "y"] = df.at[i, "y"]

 if df.at[i, "date"] == datetime(2019,1,1):
 new_df.at[df.at[i, "ID"], "x1"] = df.at[i, "x"]
 elif df.at[i, "date"] == datetime(2019,1,2):
 new_df.at[df.at[i, "ID"], "x2"] = df.at[i, "x"]

output:

 ID date x y
0 1 2019-01-01 1 a
1 1 2019-01-02 2 a
2 2 2019-01-01 3 b
3 2 2019-01-02 4 b
4 3 2019-01-01 5 c
5 3 2019-01-02 6 c

 y x1 x2
1 a 1.0 2.0
2 b 3.0 4.0
3 c 5.0 6.0

The transformation basically groups the rows by the "ID" column and gets the "x1" values from the rows with date 2019-01-01, and the "x2" values from the rows with date 2019-01-02. The "y" value is the same within the same "ID". "ID" columns become the new indexes.

I'd appreciate any advice on this matter.

python pandas pivot-table

share|improve this question

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

add a comment | 

I am trying to optimize some table transformation scripts in Python Pandas, which I am trying to feed with huge data sets (above 50k rows). I wrote a script that iterates through every index and parses values into a new data frame (see example below), but I am experiencing performance issues. Is there any pandas function, that could get the same results without iterating?

Example code:

from datetime import datetime
import pandas as pd

date1 = datetime(2019,1,1)
date2 = datetime(2019,1,2)

df = pd.DataFrame("ID": [1,1,2,2,3,3],
 "date": [date1,date2,date1,date2,date1,date2],
 "x": [1,2,3,4,5,6],
 "y": ["a","a","b","b","c","c"])


new_df = pd.DataFrame()
for i in df.index:

 new_df.at[df.at[i, "ID"], "y"] = df.at[i, "y"]

 if df.at[i, "date"] == datetime(2019,1,1):
 new_df.at[df.at[i, "ID"], "x1"] = df.at[i, "x"]
 elif df.at[i, "date"] == datetime(2019,1,2):
 new_df.at[df.at[i, "ID"], "x2"] = df.at[i, "x"]

output:

 ID date x y
0 1 2019-01-01 1 a
1 1 2019-01-02 2 a
2 2 2019-01-01 3 b
3 2 2019-01-02 4 b
4 3 2019-01-01 5 c
5 3 2019-01-02 6 c

 y x1 x2
1 a 1.0 2.0
2 b 3.0 4.0
3 c 5.0 6.0

The transformation basically groups the rows by the "ID" column and gets the "x1" values from the rows with date 2019-01-01, and the "x2" values from the rows with date 2019-01-02. The "y" value is the same within the same "ID". "ID" columns become the new indexes.

I'd appreciate any advice on this matter.

python pandas pivot-table

share|improve this question

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

from datetime import datetime
import pandas as pd

date1 = datetime(2019,1,1)
date2 = datetime(2019,1,2)

df = pd.DataFrame("ID": [1,1,2,2,3,3],
 "date": [date1,date2,date1,date2,date1,date2],
 "x": [1,2,3,4,5,6],
 "y": ["a","a","b","b","c","c"])


new_df = pd.DataFrame()
for i in df.index:

 new_df.at[df.at[i, "ID"], "y"] = df.at[i, "y"]

 if df.at[i, "date"] == datetime(2019,1,1):
 new_df.at[df.at[i, "ID"], "x1"] = df.at[i, "x"]
 elif df.at[i, "date"] == datetime(2019,1,2):
 new_df.at[df.at[i, "ID"], "x2"] = df.at[i, "x"]

 ID date x y
0 1 2019-01-01 1 a
1 1 2019-01-02 2 a
2 2 2019-01-01 3 b
3 2 2019-01-02 4 b
4 3 2019-01-01 5 c
5 3 2019-01-02 6 c

 y x1 x2
1 a 1.0 2.0
2 b 3.0 4.0
3 c 5.0 6.0

share|improve this question

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

share|improve this question

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

edited Mar 7 at 20:16

Brian Tompsett - 汤莱恩

4,2421339102

asked Mar 7 at 20:11

canbe90canbe90

82

asked Mar 7 at 20:11

canbe90canbe90

82

1 Answer
1

active

oldest

votes

0

Using pivot_tables will get what you are looking for:

result = df.pivot_table(index=['ID', 'y'], columns='date', values='x')
result.rename(columns=date1: 'x1', date2: 'x2').reset_index('y')

share|improve this answer

answered Mar 7 at 20:20

busybearbusybear

3,3691926

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
);



);

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%2f55052056%2fadvanced-pivot-table-in-pandas%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Using pivot_tables will get what you are looking for:

result = df.pivot_table(index=['ID', 'y'], columns='date', values='x')
result.rename(columns=date1: 'x1', date2: 'x2').reset_index('y')

share|improve this answer

answered Mar 7 at 20:20

busybearbusybear

3,3691926

add a comment | 

0

Using pivot_tables will get what you are looking for:

result = df.pivot_table(index=['ID', 'y'], columns='date', values='x')
result.rename(columns=date1: 'x1', date2: 'x2').reset_index('y')

share|improve this answer

answered Mar 7 at 20:20

busybearbusybear

3,3691926

add a comment | 

Using pivot_tables will get what you are looking for:

result = df.pivot_table(index=['ID', 'y'], columns='date', values='x')
result.rename(columns=date1: 'x1', date2: 'x2').reset_index('y')

share|improve this answer

answered Mar 7 at 20:20

busybearbusybear

3,3691926

result = df.pivot_table(index=['ID', 'y'], columns='date', values='x')
result.rename(columns=date1: 'x1', date2: 'x2').reset_index('y')

share|improve this answer

answered Mar 7 at 20:20

busybearbusybear

3,3691926

answered Mar 7 at 20:20

busybearbusybear

3,3691926

answered Mar 7 at 20:20

busybearbusybear

3,3691926