CODEWITHSUNDEEP
pythonpandasreplaceiterationpandas-groupby
Arnoldo Oliva
Arnoldo Oliva

Reputation: 123

Pandas replace values (grouping by and iteration)

Good morning

I have a current problem when trying to replace some values. I have a dataframe that has a column "loc10p" that separates the records into 10 groups, and for each group I have grouped those records into smaller groups, but each group has a starting range of 1 of the subgroups instead of counting the last subgroup. For example:

c2[c2.loc10p.isin([1,2])].sort_values(['loc10p','subgrupoloc10'])[['loc10p','subgrupoloc10']]

    loc10p  subgrupoloc10
1   1   1
7   1   1
15  1   1
0   1   2
14  1   2
30  1   2
31  1   2
2   2   1
8   2   1
9   2   1
16  2   1
17  2   1
18  2   2
23  2   2

How can I transform that into something like the following:

    loc10p  subgrupoloc10
1   1   1
7   1   1
15  1   1
0   1   2
14  1   2
30  1   2
31  1   2
2   2   3
8   2   3
9   2   3
16  2   3
17  2   3
18  2   4
23  2   4

I tried to do a loop that separates each group category into a different dataframe and then, replacing the values of the subgroup with a counter of the previous group, but it didn't replace anything:

w=1
temporal=[]
for e in range(1,11):
    temp=c2[c2['loc10p']==e]
    temporal.append(temp)
    
for e,i in zip(temporal,range(1,9)):
    try:
        e.loc[,'subgrupoloc10']=w
        w+=1
    except:
        pass

Any help will be really appreciated!!

Upvotes: 1

Views: 324

Answers (2)

BENY
BENY

Reputation: 323326

Try with ngroup

df['out'] = df.groupby(['loc10p','subgrupoloc10']).ngroup()+1
Out[204]: 
1     1
7     1
15    1
0     2
14    2
30    2
31    2
2     3
8     3
9     3
16    3
17    3
18    4
23    4
dtype: int64

Upvotes: 3

Andrej Kesely
Andrej Kesely

Reputation: 195543

Try:

groups = (df["subgrupoloc10"] != df["subgrupoloc10"].shift()).cumsum()
df["subgrupoloc10"] = groups
print(df)

Prints:

    loc10p  subgrupoloc10
1        1              1
7        1              1
15       1              1
0        1              2
14       1              2
30       1              2
31       1              2
2        2              3
8        2              3
9        2              3
16       2              3
17       2              3
18       2              4
23       2              4

Upvotes: 3

Related Questions