CODEWITHSUNDEEP
pythonpandas
Johanna Marklund
Johanna Marklund

Reputation: 293

Python Panda count occurences depending on multiple columns

I have added the x,y coordinate of my cars into a panda. I would like to be able to count how many cars are in a certain area. In this case I would like to get the count of all cars in x = 2 and y=1 to 3. I would like to get the count 2 since there is no car in location (2,2). I'm not that used to pandas yet so I want to loop everything.

def index_used_x_y(x,y):
    try:
        cars .loc[(cars ['x'] == x) & (cars ['y'] == y)].index
        return True
    except ValueError:
        return False

cars = pd.DataFrame()
cars ['x'] = np.array([1,1,1,2,2,3,3,3,4,4,4])
cars ['y'] = np.array([1,2,3,1,3,1,2,3,1,2,3])

count_cars = 0
print(cars )

x_from = 2
x_to = 2
y_from = 1
y_to = 3

for x in range(x_from,x_to+1):
    for y in range(y_from,y_to+1):
        if  index_used_x_y(x,y):
            count_cars +=1

print(count_cars )

Upvotes: 2

Views: 62

Answers (2)

anky
anky

Reputation: 75150

You can use series.between:

(cars['x'].between(x_from,x_to) & cars['y'].between(y_from,y_to)).sum()

2

Upvotes: 1

9769953
9769953

Reputation: 12261

I know you mention you'd like to use a loop, but the power of Pandas is to avoid loops (at least loops in plain Python: under the (Pandas) hood, there are loops implemented).

You could use the following:

import pandas as pd
 
cars = pd.DataFrame({'x': [1,1,1,2,2,3,3,3,4,4,4],
                     'y': [1,2,3,1,3,1,2,3,1,2,3]})
count = ( (cars['x'] == 2) & ((cars['y'] >= 1) | (cars['y'] <= 3)) ).sum()
print(count)

The logic here is in the comparisons, which are combined through boolean logic. You will need to pay attention to the parentheses, otherwise things will mess up: y >= 1 ored with y <= 3, and that results is then anded with x == 2.

The intermediate series, before summing it, is then the following:

0     False
1     False
2     False
3      True
4      True
5     False
6     False
7     False
8     False
9     False
10    False
dtype: bool

and the .sum() method will interpret True as 1, and False as 0, resulting in a total of 2.

Upvotes: 2

Related Questions