merge two similar data and sum the value using python?-CodePudding

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')

CodePudding user response：

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')

CodePudding user response：

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

CodePudding user response：

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