How do I assign a value basis the order it comes in a column for each group?

user_2069464 0
Viewed 71

I have the following data, values at End1, End2 appears in an order, I want to have another column that determines the order at which it appear at End1. There is a possibility that value at End2 may never reach at End1, but if it appears anywhere, it will have an impact on the order next item.

ID   End1   End2  
1    A      B      
1    A      B      
1    B      A     
1    A      B
1    C      B
1    C      D
1    D      C
1    C      D
1    D      C
2    A      B
2    A      B
2    A      C
2    A      C
2    C      A
2    C      A
2    D      C
2    C      D
2    D      C

I want to have the following output:

ID   End1   End2  Order
1    A      B      1
1    A      B      1
1    B      A      2     
1    A      B      1
1    C      B      3 
1    C      D      3
1    D      C      4
1    C      D      3
1    D      C      4
2    A      B      1
2    A      B      1
2    A      C      1
2    A      C      1 
2    C      A      3
2    C      A      3
2    D      C      4
2    C      D      3
2    D      C      4

I tried different functions, but they are all counting the occurrences of the value. Any help is appreciated.

UPDATE: There are two other requirements here:

  1. The Order resets for each group. While A may have order 1 in ID=1, but it may have order 2 for any other ID.
  2. Some of the suggested solutions are not taking into account that a item at End2 (As for B in ID=2), may never reach at End1. But it will impact the order of items coming after it.

To make it more clear ID=3 within the same data set may have the following data:

ID End1 End2
2  D    C  
.....  
3  B    E 
3  E    B
3  E    B
3  G    B
3  C    B

And the output required would be

ID End1 End2 Order
2  D    C    4 
.....  
3  B    E    1
3  E    B    2
3  E    B    2 
3  G    B    3
3  C    B    4
pandas dataframe python
3 Answers

Set index as ID and use DataFrame.stack to reshape the frame, then use Series.factorize to create a numeric array identifying distinct value thereby creating a series s, then use Series.groupby on s and agg using first(as we have to first give priority to the order for column End1 over End2):

s = pd.Series(df.set_index('ID').stack().factorize()[0] + 1)
df['Order'] = s.groupby(s.index // 2).first()

EDIT: If we need to consider distinct values per group:

s = pd.Series(np.hstack([g.factorize()[0] + 1 for _, g in
                         df.set_index('ID').stack().groupby(level=0)]))
df['Order'] = s.groupby(s.index // 2).first()

Result:

    ID End1 End2  Order
0    1    A    B      1
1    1    A    B      1
2    1    B    A      2
3    1    A    B      1
4    1    C    B      3
5    1    C    D      3
6    1    D    C      4
7    1    C    D      3
8    1    D    C      4
9    2    A    B      1
10   2    A    B      1
11   2    A    C      1
12   2    A    C      1
13   2    C    A      3
14   2    C    A      3
15   2    D    C      4
16   2    C    D      3
17   2    D    C      4
user_128331660

A possible approach could be to concatenate the string values in End1+End2, and use the result as key of dictionary. The algorithm would look something like:

counter = 1
new_column = []
my_dict = dict()
for row in data:
  key_to_check = row[End1]+row[End2]
  if key_to_check in my_dict:
     new_column.append(my_dict[key_to_check])
  else:
     my_dict[key_to_check] = counter
     new_column.append(my_dict[key_to_check])
  counter += 1

## append new_column to the data
user_109902250 
import pandas as pd
df = pd.DataFrame({'ID': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 2, 10: 2, 11: 2, 12: 2, 13: 2, 14: 2, 15: 2, 16: 2, 17: 2},
                   'End1': {0: 'A', 1: 'A', 2: 'B', 3: 'A', 4: 'C', 5: 'C', 6: 'D', 7: 'C', 8: 'D', 9: 'A', 10: 'A', 11: 'A', 12: 'A', 13: 'C', 14: 'C', 15: 'D', 16: 'C', 17: 'D'},
                   'End2': {0: 'B', 1: 'B', 2: 'A', 3: 'B', 4: 'B', 5: 'D', 6: 'C', 7: 'D', 8: 'C', 9: 'B', 10: 'B', 11: 'C', 12: 'C', 13: 'A', 14: 'A', 15: 'C', 16: 'D', 17: 'C'}})

pandas.unique will give order of appearance.

Find the index in sequence of each value of the End1 column. Group by 'ID' so the order is unique to 'ID'. Stacking each group/DataFrame serves to flatten the ['End1','End2'] columns.

df = df.set_index('ID')
gb = df.groupby('ID')
for k,g in gb:
    sequence = pd.unique(g.stack())
    order = (g.End1.to_numpy() == sequence[:,None]).argmax(0) + 1        
    df.loc[k,'Order'] = order
df.Order = df.Order.astype(int)    

def f(g):
    sequence = pd.unique(g.stack())
    order = (g.End1.to_numpy() == sequence[:,None]).argmax(0) + 1
    return order
gb = df.groupby('ID')
orders = gb.apply(f)
df.loc[orders.index,'foo'] = np.concatenate(orders.values)
user_28237550
Related