How to average DataFrame row with another row only if the first row is a substring of other next row

user_13365279 0
Viewed 42

I have a dataframe called 'data':

USER    VALUE
XOXO      21
ABC-1      2
ABC-1B     4
ABC-2      4
ABC-2B     6
PEPE      12

I want to combine 'ABC-1' with 'ABC-1B' into a single row using the first USER name and then averaging the two values to arrive here:

USER    VALUE
XOXO      21
ABC-1      3
ABC-2      5
PEPE      12

The dataframe may not be in order and there are other values in there as well that are unrelated that don't need averaging. I only want to average the two rows where 'XXX-X' is in 'XXX-XB'

data = pd.DataFrame({'USER':['XOXO','ABC-1','ABC-1B','ABC-2','ABC-2B', 'PEPE'], 'VALUE':[21,2,4,4,6,12]})
pandas matching dataframe wildcard python
1 Answers

Let's try,

df.USER = df.USER.str.replace('(-\d)B', r"\1")
df = df.groupby("USER", as_index=False, sort=False).VALUE.mean()

print(df)

    USER  VALUE
0   XOXO     21
1  ABC-1      3
2  ABC-2      5
3   PEPE     12
user_49850990
Related