CODEWITHSUNDEEP
pythondataframerowstranspose
user3423407
user3423407

Reputation: 341

How to display values of a column as separate columns

I want to display the values in a column along with their count in separate columns

Dataframe is
Date     Name    SoldItem
15-Jul    Joe     TV
15-Jul    Joe     Fridge
15-Jul    Joe     Washing Machine
15-Jul    Joe     TV
15-Jul    Joe     Fridge
15-Jul    Mary    Chair
15-Jul    Mary    Fridge
16-Jul    Joe     Fridge
16-Jul    Joe     Fridge
16-Jul    Tim     Washing Machine
17-Jul    Joe     Washing Machine
17-Jul    Jimmy   Washing Machine
17-Jul    Joe     Washing Machine
17-Jul    Joe     Washing Machine

And I get the output as

Date      Name   Count
15-Jul     Joe       2
          Mary       1
16-Jul     Joe       2

I want the final output to be

Date    Joe    Mary 
15-Jul   2        1
16-Jul   2

below is the code

   fields = ['Date', 'Name', 'SoldItem']
   df = pd.read_csv('data.csv', skipinitialspace=True, usecols=fields)
   df_fridge = df.loc[(df['SoldItem'] == 'Fridge')]
   df_fridge_grp = df_fridge.groupby(["Date", "Name"]).size()
   print df_fridge_grp

If anyone can advise some pointers. I am guessing it can be done with loc, iloc, but am wondering then if my approach is wrong. Basically i want to count the values for certain types of items per person and then display that count against the name in a column display.

Upvotes: 2

Views: 84

Answers (2)

brentertainer
brentertainer

Reputation: 2188

Code:

df_new = df[df['SoldItem'] == 'Fridge'].groupby(['Date', 'Name']).count()
df_new = df_new.unstack().fillna(0).astype(int)
print(df_new)

Output:

       SoldItem     
Name        Joe Mary
Date                
15-Jul        2    1
16-Jul        2    0

Upvotes: 1

hchw
hchw

Reputation: 1416

Does 

df_fridge_grp.unstack()

Work?

Upvotes: 3

Related Questions