0

I have a dataframe which contains duplicate records with columns x,y,z,A 

X    Y   Z   A
a   US   88  2016
a   IND  88  2016
a   IND  88  2017
a   RSA  45  2017
a   RSA  45  2018
b   US   65  2017
b   RSA  58  2018
c   RSA  58  2016

I want to create columns from the values of column A by having count of distinct countries for each vaue of X column.like below.

X     Z   2016  2017 2018 
a     88    2     1    0
a     45    0     1    1 
b     65    0     1    0
c     58    1     0    0

i couldn't figure out how to do this in python , pleas help me on this

  • Thanks Scott ! i have a question for u what if i have levels from 2000 to 2018 in the column and want to have only 2016,2017,2018 as columns from those levels.i thought to drop the unnecessary years but i believe there would be a smart way of doing it.pls suggest me on this –  Dec 29 '18 at 10:58

1 Answers1

0

You can use pivot_table:

df.pivot_table('Y',['X','Z'],'A',aggfunc='count', fill_value=0).reset_index()

Output:

A  X   Z  2016  2017  2018
0  a  45     0     1     1
1  a  88     2     1     0
2  b  58     0     0     1
3  b  65     0     1     0
4  c  58     1     0     0
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • Thanks Scott ! i have a question for u what if i have levels from 2000 to 2018 in the column and want to have only 2016,2017,2018 as columns from those levels.i thought to drop the unnecessary years but i believe there would be a smart way of doing it.pls suggest me on this. –  Dec 29 '18 at 10:52
  • @rakesh filter your df first to only those years you want then apply pivot_table. – Scott Boston Dec 29 '18 at 10:59
  • thank u scott and i 'm facing a problem like in my dataframe , i have duplicates because of other columns which i didn't post in the question so i need distinct countries count. for example :if i have IND , RSA , AFG,IND ,IND in column Y.i need count as 3 as of now it is coming up as 5.Kindly suggest me on this. –  Dec 29 '18 at 11:35
  • Check out the the attached duplicate post. "How to pivot a dataframe" very good documentation there. – Scott Boston Dec 29 '18 at 11:38