python count occurrences in csv with pandas

Question

I'm new to Python and I'm trying to work on a small project and got a little confused.

I have 2 csv files that looks like this:

all_cars:

first_Car,second_car
Mazda, Skoda
Ferrari, Volkswagen
Volkswagen, Toyota
BMW, Ferrari
BMW, Mercedes

super_cars:

super_car_name
Ferrari
BMW
Mercedes

What I'm basicly trying to do is just to count how many times a car from file 2 represented in file 1. If the car represented only in file 1 and not in file 2, I don't want it.

What I'm trying to do based on my example files is :

Ferrari : 2
BMY : 2
Mercedes : 1

MaxU - stand with Ukraine · Accepted Answer · 2017-12-27 00:22:52Z

2

I'd do it this way:

In [220]: d1.stack().value_counts().to_frame('car').loc[d2.super_car_name]
Out[220]:
          car
Ferrari     2
BMW         2
Mercedes    1

where d1 and d2 - your source DataFrames (which can be easily parsed from CSV files using pd.read_csv() method):

In [218]: d1
Out[218]:
    first_Car  second_car
0       Mazda       Skoda
1     Ferrari  Volkswagen
2  Volkswagen      Toyota
3         BMW     Ferrari
4         BMW    Mercedes

In [219]: d2
Out[219]:
  super_car_name
0        Ferrari
1            BMW
2       Mercedes

edited Dec 27, 2017 at 0:22

answered Dec 27, 2017 at 0:15

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

andrew_reece · Accepted Answer · 2017-12-27 00:32:49Z

2

You can use isin to find the matches, then stack and value_counts to get everything in one table:

df1[df1.isin(df2.super_car_name.values)].stack().value_counts()

Ferrari     2
BMW         2
Mercedes    1
dtype: int64

edited Dec 27, 2017 at 0:32

answered Dec 27, 2017 at 0:26

andrew_reece

21.4k3 gold badges40 silver badges64 bronze badges

Collectives™ on Stack Overflow

python count occurrences in csv with pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related