CODEWITHSUNDEEP
pythonpandasdataframe
The Nightman
The Nightman

Reputation: 5759

Combine pandas DataFrames to give unique element counts

I have a few pandas DataFrames and I am trying to find a good way to calculate and plot the number of times each unique entry occurs across DataFrames. As an example if I had the 2 following DataFrames:

    year    month
0    1900    1
1    1950    2
2    2000    3

    year    month
0    1900    1
1    1975    2
2    2000    3

I was thinking maybe there is a way to combine them into a single DataFrame while using a new column counts to keep track of the number of times a unique combination of year + month occurred in any of the DataFrames. From there I figured I could just scatter plot the year + month combinations with their corresponding counts.

    year    month    counts
0    1900    1        2
1    1950    2        1
2    2000    3        2
3    1975    2        1

Is there a good way to achieve this?

Upvotes: 1

Views: 27

Answers (1)

BENY
BENY

Reputation: 323286

concat then using groupby agg

pd.concat([df1,df2]).groupby('year').month.agg(['count','first']).reset_index().rename(columns={'first':'month'})
Out[467]: 
   year  count  month
0  1900      2      1
1  1950      1      2
2  1975      1      2
3  2000      2      3

Upvotes: 1

Related Questions