pandas get rolling data based on a past year and create columns in a dataframe

Admin 1
Viewed 85

I have a dataframe with 3 columns including A , B , C .i need to create columns using B,C columns as shown in the output section below.

dataframe:

A       B        C      
C_1    pink     1971    
C_1    pink     1972    
C_1    blue     1972    
C_1    red      1973    
C_1    pink     1973    
C_1    white    1974

output:

Here for the first row , the C year is 1971 which is the least one and b column count is 1 so for this row the output should be 1 for B_cnt_C-0 and further it should be 0 as we dont info before 1971 but for 2nd and 3rd row v have info for 1971 and 1972.so output should be as shown below for 2nd and 3rd row and same procedure for all remaining rows.

A       B        C       B_cnt_C-0  B_cnt_C-1  B_cnt_C-2    B_cnt_C-3
C_1    pink     1971         1         0            0           0
C_1    pink     1972         2         1            0           0
C_1    blue     1972         2         1            0           0
C_1    red      1973         2         2            1           0
C_1    pink     1973         2         2            1           0
C_1    white    1974         1         2            2           1
pandas-groupby pandas pivot numpy python
1 Answers

Use:

#get number of unique values per groups
s = df.groupby(['A','C'])['B'].nunique()
#get max number of groups
a = df.groupby('A')['C'].nunique().max()
#shift per groups and join together
df1 = pd.concat([s.groupby(level=0).shift(x).fillna(0, downcast='int') 
                 for x in range(a)], axis=1)
#new columns names
df1.columns = ['B_cnt_C-{}'.format(x) for x in range(len(df1.columns))]
print (df1)
          B_cnt_C-0  B_cnt_C-1  B_cnt_C-2  B_cnt_C-3
A   C                                               
C_1 1971          1          0          0          0
    1972          2          1          0          0
    1973          2          2          1          0
    1974          1          2          2          1
#join to original
df = df.join(df1, on=['A','C'])
print (df)
     A      B     C  B_cnt_C-0  B_cnt_C-1  B_cnt_C-2  B_cnt_C-3
0  C_1   pink  1971          1          0          0          0
1  C_1   pink  1972          2          1          0          0
2  C_1   blue  1972          2          1          0          0
3  C_1    red  1973          2          2          1          0
4  C_1   pink  1973          2          2          1          0
5  C_1  white  1974          1          2          2          1

Solution with more groups:

print (df)
      A      B     C
0   C_1   pink  1971
1   C_1   pink  1972
2   C_1   blue  1972
3   C_1    red  1973
4   C_1   pink  1973
5   C_1  white  1974
6   C_2   pink  1975
7   C_2   pink  1976
8   C_3   blue  1976
9   C_3    red  1978
10  C_3   pink  1979
11  C_3  white  1979

s = df.groupby(['A','C'])['B'].nunique()
a = df.groupby('A')['C'].nunique().max()
df1 = pd.concat([s.groupby(level=0).shift(x).fillna(0, downcast='int') 
                 for x in range(a)], axis=1)

df1.columns = ['B_cnt_C-{}'.format(x) for x in range(len(df1.columns))]
df = df.join(df1, on=['A','C'])
print (df)
      A      B     C  B_cnt_C-0  B_cnt_C-1  B_cnt_C-2  B_cnt_C-3
0   C_1   pink  1971          1          0          0          0
1   C_1   pink  1972          2          1          0          0
2   C_1   blue  1972          2          1          0          0
3   C_1    red  1973          2          2          1          0
4   C_1   pink  1973          2          2          1          0
5   C_1  white  1974          1          2          2          1
6   C_2   pink  1975          1          0          0          0
7   C_2   pink  1976          1          1          0          0
8   C_3   blue  1976          1          0          0          0
9   C_3    red  1978          1          1          0          0
10  C_3   pink  1979          2          1          1          0
11  C_3  white  1979          2          1          1          0
user_29010020
Related