Group a column of a data frame based on another dataframe

manpreet Best Answer 2 years ago

Based on this dataframe

  df1  Name     Age
       Johny     15
       Diana     35
       Doris     97
       Peter     25
       Antony    55

I have this dataframe with the number of ranges that I want to use, for example

 df2 Header   Init1   Final1   Init2  Final2  Init3  Final3
     Names    NaN      NaN     NaN     NaN     NaN    NaN
     Age       0        20      21      50      51    100

What I'm looking for is to get a result like this

  df3  Name     Age
       Johny    0-20
       Diana    21-50
       Doris    51-100
       Peter    21-50
       Antony   51-100

I don't know if a possible solution is with cut () but I'm new to python.

manpreet 2 years ago

Using pd.cut:

l = df2.iloc[1,1:].tolist()
labels = [str(t[0])+'-'+str(t[1]) for t in zip(l[::1],l[1::1])]

df['Age'] = pd.cut(df['Age'], bins=l, labels=labels)

print(df)
     Name     Age
0   Johny    0-20
1   Diana   21-50
2   Doris  51-100
3   Peter   21-50
4  Antony  51-100