-1

I have a pandas dataframe with column "Code" (categorical) with more than 100 unique values. I have multiple rows for same "Name" and would like to capture all of information pertaining to a unique "Name" in one row. Therefore, I'd like to transpose the column "Code" with the values from "Counter". How do I transpose "Code" in such a way that the following table:

Name Col1 Col2 Col3 Code Counter
Alice a1 4
Alice a2 3
Bob b1 9
Bob c2 1
Bob a2 4

becomes this:

Name Col1 Col2 Col3 a1 a2 b1 c2
Alice 4 3 0 0
Bob 0 4 9 1
Nirali
  • 9
  • 2

2 Answers2

0

try

df.pivot(index='Name', columns='Code', values='Counter').fillna(0)

output

Code    a1   a2   b1   c2
Name                     
Alice  4.0  3.0  0.0  0.0
Bob    0.0  4.0  9.0  1.0
Yuca
  • 6,010
  • 3
  • 22
  • 42
  • This is a sample table, but in reality, I have n number of columns that I want to include in the new data frame. I'd like the result to be a dataframe. How can I do that? – Nirali Aug 11 '22 at 16:58
  • 1
    the answers are based on your question. Your original question is answered, if you need the answer for a more complex question then work on your question so people can answer accordingly – Yuca Aug 11 '22 at 17:00
0

I can't comment yet but the above answer (from Yuca) should work for you - you can assign the pivot table to a variable and it will be your dataframe. you can also to be sure use Pandas to make it a dataframe:

import pandas as pd
Pivoted = df.pivot(index='Name', columns='Code', values='Counter').fillna(0)

dataframe = pd.Dataframe (data = Pivoted)
CatDad
  • 114
  • 6
  • how do I add multiple columns to the dataframe? The index only gives one "Name"? – Nirali Aug 15 '22 at 18:42
  • a simple search on stackoverflow gives you multiple options. here is one that will help, if this answers your question please check mark this answer to close the question.https://stackoverflow.com/questions/39050539/how-to-add-multiple-columns-to-pandas-dataframe-in-one-assignment - – CatDad Aug 15 '22 at 18:45