Sum all columns with a wildcard name search using Python Pandas

Question

I have a dataframe in python pandas with several columns taken from a CSV file.

For instance, data =:

Day P1S1 P1S2 P1S3 P2S1 P2S2 P2S3
1   1    2    2    3    1    2
2   2    2    3    5    4    2

And what I need is to get the sum of all columns which name starts with P1... something like P1* with a wildcard.

Something like the following which gives an error:

P1Sum = data["P1*"]

Is there any why to do this with pandas?

I'm also not getting it. I mean, it can be a really basic thing, but I searched the documentation of Pandas and didn't find any answers. — jbssm, Sep 24 '12 at 17:45
http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.filter.html?highlight=filter#pandas.DataFrame.filter — Wouter Overmeire, Sep 24 '12 at 17:46
Thank you, but that was not trivial. The documentation doesn't say anything on how to use the function, just says there is a function. Anyway I found the answer and I will post it below. — jbssm, Sep 24 '12 at 18:03

score 65 · Accepted Answer · answered Sep 24 '12 at 18:05

65

I found the answer.

Using the data, dataframe from the question:

from pandas import *

P1Channels = data.filter(regex="P1")
P1Sum = P1Channels.sum(axis=1)

answered Sep 24 '12 at 18:05

jbssm

6,861
13
54
81

score 5 · Answer 2 · answered Dec 06 '18 at 16:45

5

List comprehensions on columns allow more filters in the if condition:

In [1]: df = pd.DataFrame(np.arange(15).reshape(5, 3), columns=['P1S1', 'P1S2', 'P2S1'])

In [2]: df
Out[2]: 
   P1S1  P1S2  P2S1
0     0     1     2
1     3     4     5
2     6     7     8
3     9    10    11
4    12    13    14

In [3]: df.loc[:, [x for x in df.columns if x.startswith('P1')]].sum(axis=1)
Out[3]: 
0     1
1     7
2    13
3    19
4    25
dtype: int64

answered Dec 06 '18 at 16:45

Anton Tarasenko

8,099
11
66
91

I find using `fnmatch` in the standard lib gives a lot of power in these "if" list comprehensions. – Brian Larsen Aug 31 '20 at 20:13

score 1 · Answer 3 · edited Sep 01 '16 at 18:20

1

Thanks for the tip jbssm, for anyone else looking for a sum total, I ended up adding .sum() at the end, so:

P1Sum= P1Channels.sum(axis=1).sum()

edited Sep 01 '16 at 18:20

Cristik

30,989
25
91
127

answered Sep 01 '16 at 18:18

jarvis

983
8
5

Sum all columns with a wildcard name search using Python Pandas

3 Answers3