create new pandas column that is a tabulation of rows above

user_2123706 0
Viewed 35

I have:

pd.DataFrame({'col1':['A','A','B','F']})

    col1
0   A
1   A
2   B
3   F

I want:

pd.DataFrame({'col1':['A','A','B','F'],'col2':['1A:0B:0C:0D:0E:0F','2A:0B:0C:0D:0E:0F','2A:1B:0C:0D:0E:0F','2A:1B:0C:0D:0E:1F']})

    col1    col2
0   A   1A:0B:0C:0D:0E:0F
1   A   2A:0B:0C:0D:0E:0F
2   B   2A:1B:0C:0D:0E:0F
3   F   2A:1B:0C:0D:0E:1F

Requirements:

I have a column that can take one of 6 values (A:F). I want to create a new column that shows the running total of the values of that row and above.

any suggestions?

pandas-groupby pandas
1 Answers

You can use get_dummies + cumsum. That output is generally easier to work with, but if you need that single string output, you can join the columns with the counts. The .reindex and .fillna ensure everything is ordered and includes exactly the categories you want. 

import pandas as pd
df = pd.DataFrame({'col1':['A','A','B','F']})

df = (pd.get_dummies(df['col1'])
        .reindex(list('ABCDEF'), axis=1)
        .fillna(0, downcast='infer')
        .cumsum())
#   A  B  C  D  E  F
#0  1  0  0  0  0  0
#1  2  0  0  0  0  0
#2  2  1  0  0  0  0
#3  2  1  0  0  0  1

df['res'] = [':'.join(x) for x in (df.astype(str)+df.columns).to_numpy()]
#   A  B  C  D  E  F                res
#0  1  0  0  0  0  0  1A:0B:0C:0D:0E:0F
#1  2  0  0  0  0  0  2A:0B:0C:0D:0E:0F
#2  2  1  0  0  0  0  2A:1B:0C:0D:0E:0F
#3  2  1  0  0  0  1  2A:1B:0C:0D:0E:1F
user_43333590
Related