CODEWITHSUNDEEP
pythonpandas
Viktor.w
Viktor.w

Reputation: 2297

Pandas: add a column to a categorical dataframe

My raw data looks like: 

Bin         A     B     C
CPB%                     
0.00000     0    57  1728
0.00100     0  1579  1240
0.00200  1360   488   869
0.00300   184   499   597
0.00400   265   283   461

I obtained it thanks to that code:

import operator
bins = np.linspace(0, 1, num=1000)

df_b = pd.crosstab(pd.cut(df['CPB%'], bins=bins).map(operator.attrgetter('left')), df.Bin)

What I tried to do is the following:

totalb = df_b['A'].sum()
idxb = totalb
proba_b = []


for index, row in df_b.iterrows():
    idxb = idxb - row['A']
    prob = float(idxb)/float(totalb)
    proba_b.append(prob)

df_b['Proba-b'] = proba_b

But when I try to add a new column to this categorical dataframe, I have the following error:'cannot insert an item into a CategoricalIndex that is not already an existing category'

I tried to append a new dataframe to the existing one but did not work... any idea? Thanks!

Upvotes: 1

Views: 1215

Answers (1)

jezrael
jezrael

Reputation: 862641

You need CategoricalIndex.add_categories for add new category by new column name(s):

df_b.columns = df_b.columns.add_categories('Proba-b')
df_b['Proba-b'] = proba_b
print (df_b)

          A     B     C   Proba-b
Bin                              
0.000     0    57  1728  1.000000
0.001     0  1579  1240  1.000000
0.002  1360   488   869  0.248203
0.003   184   499   597  0.146490
0.004   265   283   461  0.000000

For improve performance instead iterrows is possible use:

s = df_b['A']
df_b['Proba-b'] = (s.iloc[::-1].cumsum()).shift().fillna(0) / s.sum()
print (df_b)

          A     B     C   Proba-b
Bin                              
0.000     0    57  1728  1.000000
0.001     0  1579  1240  1.000000
0.002  1360   488   869  0.248203
0.003   184   499   597  0.146490
0.004   265   283   461  0.000000

Upvotes: 3

Related Questions