Ruffy26 Ruffy26 - 1 month ago 6
Python Question

How to run a loop within a Pandas dataframe to append a column?

I have a dataframe that is as follows:

MID POSITION
1 22596394 R8

2 22596394 R8

3 22596394 R8

4 22591549 R6

5 22591549 R6

6 22591549 R6


Now I have another dataframe which will be the output after running some code which will look like the following:

Position Usage
R1 0
R2 0
R3 0
R4 0
R5 0
R6 1
R7 0
R8 1
L1 0
L2 0
L3 0
...
L8 0


I would like to fill out the Usage column according to the logic below:

Wherever MID changes, note the corresponding POSITION and fill the Usage row corresponding in the output dataframe, for eg: in the above dataframe, R8 and R6 Usage rows should be filled with 1 and the rest Position columns with 0. Similarly if MID changes twice for the same position say R6 for example the R6 Usage row should be filled with 2 and so on. What would be the best way to do this? Thanks in advance!

Answer

I think you need nunique and then reindex:

print (df1.groupby('POSITION')['MID'].nunique())
POSITION
R6    1
R8    1
Name: MID, dtype: int64

print (df1.groupby('POSITION')['MID']
          .nunique()
          .reindex(df2.set_index('Position').index, fill_value=0)
          .rename('Usage')
          .reset_index())
   Position  Usage
0        R1      0
1        R2      0
2        R3      0
3        R4      0
4        R5      0
5        R6      1
6        R7      0
7        R8      1
8        L1      0
9        L2      0
10       L3      0
Comments