Create table with calculations from two tables. Python. Pandas

user_5011113 0
Viewed 438

I have two tables like this:

import pandas as pd
import numpy as np

data1 = [[1, 2000], [2, 3000]]
df_players = pd.DataFrame(data1, columns = ['cluster', 'users'])
df_players
cluster users
1 2000
2 3000 
data2 = [[1, 2], [2, 4]]
df_multiplyer = pd.DataFrame(data2, columns = ['cluster', 'multiplyer'])
df_multiplyer
cluster multiplyer
1 2
2 4

And I have an array

coef = np.arange(.05, 0.16, 0.05)
coef

0.05

0.1

0.15

A want to create the table as shown below with calculated field users*myltiplyer*coeff

Desired output:

cluster num_users multiplyer coeffs calculated_field
1 2000 2 0.05 200
1 2000 2 0.1 400
1 2000 2 0.15 600
2 3000 4 0.05 600
2 3000 4 0.1 1200
2 3000 4 0.15 1800

Thanks for helping me!

pandas python
1 Answers

Create helper DataFrame, then use merge by cluster and then cross join by new DataFrame:

df1 = pd.DataFrame({'coeffs':coef, 'a':1})
df = (df_players.merge(df_multiplyer, on='cluster')
                .assign(a = 1)
                .merge(df1, on='a')
                .drop('a', axis=1))
df['calculated_field'] = df[['users','multiplyer','coeffs']].prod(axis=1)
print (df)
   cluster  users  multiplyer  coeffs  calculated_field
0        1   2000           2    0.05             200.0
1        1   2000           2    0.10             400.0
2        1   2000           2    0.15             600.0
3        2   3000           4    0.05             600.0
4        2   3000           4    0.10            1200.0
5        2   3000           4    0.15            1800.0
user_29010020
Related