How do I sum rows in pandas by the first word of each row into an aggregate for that first word?

user_4295397 0
Viewed 112

for instance in column named 'fruit'

'fruit'      val1 val2
apple bottom  1    2 
apple shot    3    4

output:

apple 4 6

If the above is tin a dataframe how do I get the output?

enter image description here

pandas dataframe numpy
2 Answers

A little shorter version of proposed answer by @Nk03:

df.groupby(df.fruit.str.split(' ').str[0]).sum()

output:

       val1 val2
fruit       
apple   4   6

if your column name has a space in it like OP's comment, use:

df.groupby(df[' line item'].str.split(' ').str[0]).sum()
user_49759810

Suppose if you have got a df like this:

          fruit  val1  val2
0  apple bottom     1     2
1    apple shot     3     4

You can achieve the result via groupby:

df = df.groupby(df.fruit.str.split(' ').str[0].values).agg({'val1':  sum, 'val2' : sum})

Output:

       val1  val2
apple     4     6

NOTE: You can specify the different method in the dict inside agg. If you need just the sum then you can always use the smaller version.

df = df.groupby(df.fruit.str.split(' ').str[0]).sum()
user_154380330
Related