How to do rolling `get_dummies` for each group of a groupby - pandas

Question

I have a dataframe like below

   A             B          
Customer_1    1000      
Customer_1    2000      
Customer_1    3000      
Customer_2    2000      
Customer_2    3000      
Customer_3    1000      
Customer_3    3000

I want to transform the above dataframe like below , how can i make it ?

A             B           3000      2000    1000
Customer_1    1000         0         0       1
Customer_1    2000         0         1       1
Customer_1    3000         1         1       1
Customer_2    2000         0         1       0
Customer_2    3000         1         1       0
Customer_3    1000         0         0       1
Customer_3    3000         1         0       1

I cant figure out how to do this,please help me on this and let me know if you need clarification.

U13-Forward · Answer 1 · 2019-01-11T01:37:16.683

0

Use get_dummies, of the str accessor, also use join to merge:

print(df.join(df.B.astype(str).str.get_dummies()))

Output:

            A     B  1000  2000  3000
0  Customer_1  1000     1     0     0
1  Customer_1  2000     0     1     0
2  Customer_1  3000     0     0     1
3  Customer_2  2000     0     1     0
4  Customer_2  3000     0     0     1
5  Customer_3  1000     1     0     0
6  Customer_3  3000     0     0     1

It's really just merging df with that value with 0, 1, how the 0, 1 is done? use get_dummies which does it all by itself.

Edit:

print(df.join(df.join(df.groupby('A').apply(lambda x: x['B'].astype(str).str.get_dummies()).fillna(0).astype(int)).groupby('A').cumsum().drop('B',1)))

Output:

            A     B  1000  2000  3000
0  Customer_1  1000     1     0     0
1  Customer_1  2000     1     1     0
2  Customer_1  3000     1     1     1
3  Customer_2  2000     0     1     0
4  Customer_2  3000     0     1     1
5  Customer_3  1000     1     0     0
6  Customer_3  3000     1     0     1

edited Jan 11 '19 at 01:37

answered Jan 10 '19 at 08:55

U13-Forward

69,221
14
89
114

this is not what i needed , pls look the requirement once again. – Jan 10 '19 at 09:04
1

@ramesh What's the difference? – U13-Forward Jan 10 '19 at 09:04
Can you explain the logic perhaps? – yatu Jan 10 '19 at 09:05
@yatu Now? enough? – U13-Forward Jan 10 '19 at 09:07
Enough? You haven't added anything – yatu Jan 10 '19 at 09:09
for customer_1 if i have B column with 1000, 2000 ,3000 then the for customer1 with 3000 row ,should have column values with 1 1 1 for 3000 , 2000 ,1000 .please look at the output which i posted in the question one more time – Jan 10 '19 at 09:10
@ramesh Oh.. that's right. – U13-Forward Jan 10 '19 at 09:14
@ramesh I'll edit my answer in like 15~20 hours, i have to go somewhere a little and then sleep. – U13-Forward Jan 10 '19 at 09:18
@ramesh I can edit now. – U13-Forward Jan 10 '19 at 23:37
@U9-Forward , thanks – Jan 11 '19 at 07:36
@ramesh Don't forget to accept my answer if it works :-), happy to help, :-) – U13-Forward Jan 11 '19 at 08:06
@U9-Forward, i have other question as well, i which needs a solution will post the question url here,pleasee help me out on that as well – Jan 11 '19 at 17:44
https://stackoverflow.com/questions/54152104/pandas-get-rolling-data-of-past-years-for-the-current-year-row – Jan 11 '19 at 18:31
kindly look above url and help me with a solution – Jan 11 '19 at 18:32
@ramesh Ya deleted it, so? – U13-Forward Jan 12 '19 at 12:00

How to do rolling `get_dummies` for each group of a groupby - pandas

1 Answers1