Styjun

What was the last x86 CPU that did not have the x87 floating-point unit built in?

How to delete random line from file using Unix command?

What do you call a plan that's an alternative plan in case your initial plan fails?

Cooking pasta in a water boiler

How long does the line of fire that you can create as an action using the Investiture of Flame spell last?

Reference for the teaching of not-self

What are these Gizmos at Izaña Atmospheric Research Center in Spain?

What's the point in a preamp?

Derivation tree not rendering

What aspect of planet Earth must be changed to prevent the industrial revolution?

Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?

How to copy the contents of all files with a certain name into a new file?

Did the new image of black hole confirm the general theory of relativity?

Was credit for the black hole image misattributed?

Can a novice safely splice in wire to lengthen 5V charging cable?

Why did all the guest students take carriages to the Yule Ball?

Windows 10: How to Lock (not sleep) laptop on lid close?

How do you keep chess fun when your opponent constantly beats you?

Sort a list of pairs representing an acyclic, partial automorphism

How should I replace vector<uint8_t>::const_iterator in an API?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Mortgage adviser recommends a longer term than necessary combined with overpayments

Does Parliament hold absolute power in the UK?

Difference between "generating set" and free product?

How can I perform a value dependent pivot table/Groupby in Pandas?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

1

I have the following dataframe:

 Tran ID Category Quantity 
 0 001 A 5
 1 001 B 2 
 2 001 C 3
 3 002 A 4
 4 002 C 2
 5 003 D 6 

I want to transform it into:

 Tran ID A B C D Quantity 
 0 001 True True True False 10
 1 002 True False True False 6
 2 003 False False False True 6 

I know I can use groupby to get the sum of quantity, but I can't figure out how to perform the pivot that I described.

python pandas pandas-groupby

share|improve this question

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

add a comment | 

1

1

I have the following dataframe:

 Tran ID Category Quantity 
 0 001 A 5
 1 001 B 2 
 2 001 C 3
 3 002 A 4
 4 002 C 2
 5 003 D 6 

I want to transform it into:

 Tran ID A B C D Quantity 
 0 001 True True True False 10
 1 002 True False True False 6
 2 003 False False False True 6 

I know I can use groupby to get the sum of quantity, but I can't figure out how to perform the pivot that I described.

python pandas pandas-groupby

share|improve this question

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

add a comment | 

I have the following dataframe:

 Tran ID Category Quantity 
 0 001 A 5
 1 001 B 2 
 2 001 C 3
 3 002 A 4
 4 002 C 2
 5 003 D 6 

I want to transform it into:

 Tran ID A B C D Quantity 
 0 001 True True True False 10
 1 002 True False True False 6
 2 003 False False False True 6 

I know I can use groupby to get the sum of quantity, but I can't figure out how to perform the pivot that I described.

python pandas pandas-groupby

share|improve this question

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

 Tran ID Category Quantity 
 0 001 A 5
 1 001 B 2 
 2 001 C 3
 3 002 A 4
 4 002 C 2
 5 003 D 6 

 Tran ID A B C D Quantity 
 0 001 True True True False 10
 1 002 True False True False 6
 2 003 False False False True 6 

share|improve this question

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

share|improve this question

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

edited Mar 22 at 7:54

U9-Forward

18.1k51744

edited Mar 22 at 7:54

U9-Forward

18.1k51744

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

asked Mar 22 at 6:48

Alex KinmanAlex Kinman

74321228

2 Answers
2

active

oldest

votes

2

Use get_dummies for indicators with max and add new column with aggregating sum:

#pandas 0.23+
df1 = pd.get_dummies(df.set_index('Tran ID')['Category'], dtype=bool).max(level=0)
#oldier pandas versions
#df1 = pd.get_dummies(df.set_index('Tran ID')['Category']).astype(bool).max(level=0)
s = df.groupby('Tran ID')['Quantity'].sum()

df2 = df1.assign(Quantity = s).reset_index()
print (df2)
 Tran ID A B C D Quantity
0 001 True True True False 10
1 002 True False True False 6
2 003 False False False True 6

share|improve this answer

edited Mar 22 at 7:01

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

add a comment | 

2

Or you can use:

print(df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool)).groupby('Tran ID',as_index=False).sum())

Or little easier to read:

df1 = df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool))
print(df1.groupby('Tran ID',as_index=False).sum())

Both output:

 Tran ID Quantity A B C D
0 1 10 True True True False
1 2 6 True False True False
2 3 6 False False False True

pandas.DataFrame.groupby with pandas.Series.str.get_dummies is the way to do it.

share|improve this answer

edited Mar 22 at 7:17

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think you're missing a parenthesis at the end
  
  – Alex Kinman
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Solution working with sample data, but if change second row to 1;001;A;2 it working uncorrect.Reason is aggregating sum
  
  – jezrael
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael I am gonna try.
  
  – U9-Forward
  Mar 22 at 7:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael it works for me with those values...
  
  – U9-Forward
  Mar 22 at 7:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @U9-Forward - Really old, upgrade it... :)
  
  – jezrael
  Mar 22 at 7:27
  

  
  
  
  

 | 
show 4 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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55294266%2fhow-can-i-perform-a-value-dependent-pivot-table-groupby-in-pandas%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

Use get_dummies for indicators with max and add new column with aggregating sum:

#pandas 0.23+
df1 = pd.get_dummies(df.set_index('Tran ID')['Category'], dtype=bool).max(level=0)
#oldier pandas versions
#df1 = pd.get_dummies(df.set_index('Tran ID')['Category']).astype(bool).max(level=0)
s = df.groupby('Tran ID')['Quantity'].sum()

df2 = df1.assign(Quantity = s).reset_index()
print (df2)
 Tran ID A B C D Quantity
0 001 True True True False 10
1 002 True False True False 6
2 003 False False False True 6

share|improve this answer

edited Mar 22 at 7:01

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

add a comment | 

2

Use get_dummies for indicators with max and add new column with aggregating sum:

#pandas 0.23+
df1 = pd.get_dummies(df.set_index('Tran ID')['Category'], dtype=bool).max(level=0)
#oldier pandas versions
#df1 = pd.get_dummies(df.set_index('Tran ID')['Category']).astype(bool).max(level=0)
s = df.groupby('Tran ID')['Quantity'].sum()

df2 = df1.assign(Quantity = s).reset_index()
print (df2)
 Tran ID A B C D Quantity
0 001 True True True False 10
1 002 True False True False 6
2 003 False False False True 6

share|improve this answer

edited Mar 22 at 7:01

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

add a comment | 

Use get_dummies for indicators with max and add new column with aggregating sum:

#pandas 0.23+
df1 = pd.get_dummies(df.set_index('Tran ID')['Category'], dtype=bool).max(level=0)
#oldier pandas versions
#df1 = pd.get_dummies(df.set_index('Tran ID')['Category']).astype(bool).max(level=0)
s = df.groupby('Tran ID')['Quantity'].sum()

df2 = df1.assign(Quantity = s).reset_index()
print (df2)
 Tran ID A B C D Quantity
0 001 True True True False 10
1 002 True False True False 6
2 003 False False False True 6

share|improve this answer

edited Mar 22 at 7:01

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

#pandas 0.23+
df1 = pd.get_dummies(df.set_index('Tran ID')['Category'], dtype=bool).max(level=0)
#oldier pandas versions
#df1 = pd.get_dummies(df.set_index('Tran ID')['Category']).astype(bool).max(level=0)
s = df.groupby('Tran ID')['Quantity'].sum()

df2 = df1.assign(Quantity = s).reset_index()
print (df2)
 Tran ID A B C D Quantity
0 001 True True True False 10
1 002 True False True False 6
2 003 False False False True 6

share|improve this answer

edited Mar 22 at 7:01

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

answered Mar 22 at 6:53

jezraeljezrael

358k26324403

2

Or you can use:

print(df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool)).groupby('Tran ID',as_index=False).sum())

Or little easier to read:

df1 = df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool))
print(df1.groupby('Tran ID',as_index=False).sum())

Both output:

 Tran ID Quantity A B C D
0 1 10 True True True False
1 2 6 True False True False
2 3 6 False False False True

pandas.DataFrame.groupby with pandas.Series.str.get_dummies is the way to do it.

share|improve this answer

edited Mar 22 at 7:17

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think you're missing a parenthesis at the end
  
  – Alex Kinman
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Solution working with sample data, but if change second row to 1;001;A;2 it working uncorrect.Reason is aggregating sum
  
  – jezrael
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael I am gonna try.
  
  – U9-Forward
  Mar 22 at 7:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael it works for me with those values...
  
  – U9-Forward
  Mar 22 at 7:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @U9-Forward - Really old, upgrade it... :)
  
  – jezrael
  Mar 22 at 7:27
  

  
  
  
  

 | 
show 4 more comments

2

Or you can use:

print(df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool)).groupby('Tran ID',as_index=False).sum())

Or little easier to read:

df1 = df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool))
print(df1.groupby('Tran ID',as_index=False).sum())

Both output:

 Tran ID Quantity A B C D
0 1 10 True True True False
1 2 6 True False True False
2 3 6 False False False True

pandas.DataFrame.groupby with pandas.Series.str.get_dummies is the way to do it.

share|improve this answer

edited Mar 22 at 7:17

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I think you're missing a parenthesis at the end
  
  – Alex Kinman
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Solution working with sample data, but if change second row to 1;001;A;2 it working uncorrect.Reason is aggregating sum
  
  – jezrael
  Mar 22 at 7:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael I am gonna try.
  
  – U9-Forward
  Mar 22 at 7:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jezrael it works for me with those values...
  
  – U9-Forward
  Mar 22 at 7:23
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @U9-Forward - Really old, upgrade it... :)
  
  – jezrael
  Mar 22 at 7:27
  

  
  
  
  

 | 
show 4 more comments

Or you can use:

print(df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool)).groupby('Tran ID',as_index=False).sum())

Or little easier to read:

df1 = df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool))
print(df1.groupby('Tran ID',as_index=False).sum())

Both output:

 Tran ID Quantity A B C D
0 1 10 True True True False
1 2 6 True False True False
2 3 6 False False False True

pandas.DataFrame.groupby with pandas.Series.str.get_dummies is the way to do it.

share|improve this answer

edited Mar 22 at 7:17

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

print(df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool)).groupby('Tran ID',as_index=False).sum())

df1 = df.drop('Category',1).join(df['Category'].str.get_dummies().astype(bool))
print(df1.groupby('Tran ID',as_index=False).sum())

 Tran ID Quantity A B C D
0 1 10 True True True False
1 2 6 True False True False
2 3 6 False False False True

share|improve this answer

edited Mar 22 at 7:17

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744

answered Mar 22 at 6:54

U9-ForwardU9-Forward

18.1k51744