Subset multiple row values into new table and and count string pattren occurences

user_4181846 0
Viewed 56

I have the following table for processing (Table1):

Fuse    Ident   Grade
A1  BLU123  skyline
A1  RED235  blue
A1  RED345  ortho
B1  RED160  linx
B1  BLU760  milli
B2  BLU222  moli
B2  RED201  straw
C1  RED201  straw
C2  BLU222  moli
D1  RED235  blue
D1  BLU123  skyline
E1  NA  NA  NA
E2  NA  NA  NA
F1  BLU999  monte
F1  BLU23  nome

I'm trying to process the above table to obtain a final table (Table 2) as follows:

Option1:

Grade1  Grade2  row#
blue    skyline 2
ortho   skyline 1
linx    milli   1
straw    moli   2

Option 2:

Grade1  Grade2  row#
blue;ortho  skyline 1
linx    milli   1
straw   moli    2

The following table explains how to arrive at the final tables above:

RED BLU Grade1  Grade2
RED235  BLU123  blue    skyline
RED345  BLU123  ortho   skyline
RED160  BLU760  linx    milli
RED201  BLU222  blue    skyline

Column "Fuse" in Table 1 has multiple entries for a particular value (e.g., A1). Within the "A1" table subset for column "Fuse", the corresponding "Ident" column must have at least an entring starting with "BLU" and "RED" to make a complete set for recording values for Grade1 and Grade2 columns. Two complete sets can be derived from "A1" subset (RED235,BLU123; RED345 BLU123). Finally as in Table 2, numbers of occurences of combined Grade 1 and Grade2 columns are recorded. Other conditions are: empty rows for columns "Fuse" and "Ident" are ignored, and when there are no complementary sets of strings starting with "BLU" and "RED" in column "Ident", the rows are ingores

Here's my attempt:

grp=df.groupby(["Fuse","Ident"])
[grp.get_group(x) for x in grp.groups]

My code did not work. Can someone help with a better code? Thanks

pandas python
1 Answers
count the aggregated occurrences
out = (df
         # extract BLU/RED as col (other methods are possible)
 .assign(group=df['Ident'].str[:3])
 .groupby(['Fuse', 'group'])['Grade'].agg(';'.join) # aggregate multiple occurrences
 .unstack().value_counts()  # count values
 .reset_index(name='count')
)

output:

       BLU         RED  count
0    milli        linx      1
1     moli       straw      1
2  skyline        blue      1
3  skyline  blue;ortho      1
older answer

You can use a pivot as main step:

out = (df
         # extract BLU/RED as col (other methods are possible)
 .assign(col=df['Ident'].str[:3],
         # get position of each BLU/RED per group
         idx=lambda d: d.groupby(['Fuse', 'col']).cumcount()
        )
 .pivot(index=['Fuse', 'idx'], columns='col') # pivot using new columns
 .dropna(how='all', axis=1) # drop empty columns
 .dropna(how='any', axis=0) # drop rows with missing values
 .droplevel('idx')          # drop intermediate level
)

output:

       Ident            Grade       
col      BLU     RED      BLU    RED
Fuse                                
A1    BLU123  RED235  skyline   blue
B1    BLU760  RED160    milli   linx
B2    BLU222  RED201     moli  straw
D1    BLU123  RED235  skyline   blue

Then you can count the values:

out['Grade'].value_counts()

output:

BLU      RED  
skyline  blue     2
milli    linx     1
moli     straw    1
user_163434640
Related