CODEWITHSUNDEEP
pythonpandaspandas-groupby
Jessica
Jessica

Reputation: 3173

Looping through groupby to assign numbers to name in pandas

I have a sample data set: 

import pandas as pd
d = {
  'ID': ['ID-1','ID-1','ID-1','ID-1','ID-2','ID-2','ID-2'],
  'OBR':[100,100,100,100,200,200,200],
  'OBX':['A','B','C','D','A','B','C'],
  'notes':['hello','hello2','','','bye','',''],
}
df = pd.DataFrame(d)

it looks like: 

    ID   OBR  OBX   notes
   ID-1  100   A    hello
   ID-1  100   B    hello2
   ID-1  100   C        
   ID-1  100   D        
   ID-2  200   A    bye
   ID-2  200   B        
   ID-2  200   C

i want to loop through each row, and for each ID, OBR combination, assign a number to OBX and notes name that increment by 1 and assign the values correspondingly.

So for the first ID, OBR combo: the ID and OBR name stay the same, since there are 4 different OBX values, the names for the OBX would be OBX1, OBX2, OBX3 and OBX4, and since there are 2 different notes values, the names for notes would be note1 and note2.

the second ID, OBR combo: the ID and OBR name stay the same, since there are 3 different OBX values, the names for the OBX would be OBX1, OBX2 and OBX3, and since there is 1 notes value, the name for notes would be note1.

desire output: to print and assign the values

ID = ID-1
OBR= 100
OBX1=A
OBX2=B
OBX3=C
OBX4=D
note1 = hello
note2 = hello2

ID = ID-2
OBR= 200
OBX1 = A
OBX2 = B
OBX3 = C
note1 = bye

my attempt: 

count = 0
grouped = df.groupby(['ID','OBR'])
for a, group in grouped:
    ID = a[0]
    OBR = a[1]
    OBX+str(count) = group['OBX']  #this gives an error, can't use OBX+str(count) as the name
    note+str(count) = group['notes'] #this gives an error as well
    count +=1 #Is using count correct? 
    print(....)

Upvotes: 1

Views: 96

Answers (1)

jpp
jpp

Reputation: 164673

One way is to groupby to tuples:

res = df.groupby(['ID', 'OBR'])\
        .agg({'OBX': lambda x: tuple(x), 'notes': lambda x: tuple(filter(None, x))})\
        .reset_index()

print(res)

     ID  OBR           OBX            notes
0  ID-1  100  (A, B, C, D)  (hello, hello2)
1  ID-2  200     (A, B, C)           (bye,)

Then iterate rows, with enumerate where applicable:

for row in res.itertuples():
    print('\nID =', row.ID)
    print('OBR =', row.OBR)
    for i, obx in enumerate(row.OBX, 1):
        print('OBX'+str(i)+' =', obx)
    for i, note in enumerate(row.notes, 1):
        print('notes'+str(i)+' =', note)

Result:

ID = ID-1
OBR = 100
OBX1 = A
OBX2 = B
OBX3 = C
OBX4 = D
notes1 = hello
notes2 = hello2

ID = ID-2
OBR = 200
OBX1 = A
OBX2 = B
OBX3 = C
notes1 = bye

Upvotes: 1

Related Questions