merge two similar data and sum the value using python?

Question

how do I merge and sum`` genre1 and genre2 total movie?

genre1= movie_overavg.groupby('genre_1',as_index=False)['movie_title'].count()
genre1.columns=['genre','total movie']
genre1=genre1.set_index('genre')
print(genre1)


genre3= movie_overavg.groupby('genre_3',as_index=False)['movie_title'].count()
genre3.columns=['genre','total movie']
genre3=genre3.set_index('genre')

Check [this](https://stackoverflow.com/a/41181829/8353711). Concat data vertically and apply `groupby` as you did. — shaik moeed, Sep 08 '22 at 10:41

jezrael · Answer 1 · 2022-09-08T10:46:24.177

1

Use DataFrame.add:

df = genre1.add(genre3, fill_value=0)

Or concat wth aggregate size:

s = pd.concat([movie_overavg['genre_1'], movie_overavg['genre_3']])

df = s.value_counts().sort_index().to_frame('total movie')

df = s.groupby(s).size().to_frame('total movie')

edited Sep 08 '22 at 10:46

answered Sep 08 '22 at 10:41

jezrael

822,522
95
1,334
1,252

score 0 · Answer 2 · answered Sep 08 '22 at 10:43

0

pd.merge(genre1, genre2, on=['genre']).set_index(['genre']).sum(axis=1)

answered Sep 08 '22 at 10:43

Zaid Parkar

74
4

score -1 · Accepted Answer · answered Sep 08 '22 at 10:50

Lets say

df = table 1

    genre   total
0   action      450
1   adventure   200
2   animation   300
3   crime       10

df2 = table 2

genre   total
0   action      450
1   adventure   200
2   animation   300

these can be done by

f = [df, df2]

result = pd.concat(f)
new_df = result.groupby('genre')['total'].sum().reset_index()

output

genre        total
0   action       900
1   adventure    400
2   animation    600
3   crime        10

merge two similar data and sum the value using python?

3 Answers3