CODEWITHSUNDEEP
pandasdataframedivision
TylerNG
TylerNG

Reputation: 941

Pandas dividing rows from 2 df

Is it to perform rows division between 2 dfs by matching columns. For example,

df1:

Name    1    2   3   5  Total
-----------------------------
A       2    2   2   2   8
B       1    1   1   1   4
C       0    1   2   3   6

df2:

Alias   1    2   3   4  Total
-----------------------------
X       5    5   5   5  20
Y       10   10  0   0  20
Z       1    2   3   4  10

The result would be:

r

NewName 1    2     3     4     5   Total   
----------------------------------------        (These rows will be set manually) 
I       2/5   2/5   2/5   0/5   -   8/20    <---I = A/X 
J       1/5   1/5   1/5   0/5   -   4/20    <---J = B/X
K       1/10  1/10   -     -    -   4/20    <---K = B/Y 
L       0/5   1/5   2/5   0/5   -   6/20    <---L = C/X

Thanks! :)

Upvotes: 0

Views: 301

Answers (3)

jo9k
jo9k

Reputation: 710

I = df1.T['A']/df2.T['X']
J = df1.T['B']/df2.T['X']
K = df1.T['B']/df2.T['Y'] 
L = df1.T['C']/df2.T['X']

df = pd.concat([I, J, K, L], axis=1).rename(columns={0:'I', 1:'J', 2:'K', 3:'L'}).T

Then, to make it look more like the output you wanted:

df[np.isfinite(df)].fillna('-')

--

Edit

More universally, to not cascade divisions, you can do:

pairs = [('A','X'), ('B','X'), ('B','Y'), ('C','X')]
series_to_concat = [df1.T[col_df1]/df2.T[col_df2] for (col_df1, col_df2) in pairs]
names = ['I', 'J', 'K', 'L']
col_names = {col_num : name for col_num, name in enumerate(names)}

df = pd.concat(series_to_concat, axis=1).rename(columns=col_names).T

Upvotes: 1

cs95
cs95

Reputation: 402423

This needs an involved solution, but can be done. First, declare your manually controlled parameters.

i = ['A', 'B', 'B', 'C']
j = ['X', 'X', 'Y', 'X']
k = ['I', 'J', 'K', 'L']

Now, the idea is to align the two dataframes. 

x = df1.set_index('Name')
y = df2.set_index('Alias')

x, y = x.align(y)

Perform division, and create a new dataframe. Since we're dividing numpy arrays, you might encounter runtime warnings. Ignore them. 

z = x.reindex(i, axis=0).values / y.reindex(j, axis=0).values   

df = pd.DataFrame(z, index=k, columns=x.columns)
df

     1    2         3   4   5  Total
I  0.4  0.4  0.400000 NaN NaN    0.4
J  0.2  0.2  0.200000 NaN NaN    0.2
K  0.1  0.1       inf NaN NaN    0.2
L  0.0  0.2  0.400000 NaN NaN    0.3

Edit; on older versions, reindex does not accept an axis parameter. In that case, use 

z = x.reindex(index=i).values / y.reindex(index=j).values

Additionally, to fill up non-finite values, use np.isfinite -

df[np.isfinite(df)].fillna('-')

     1    2    3  4  5  Total
I  0.4  0.4  0.4  -  -    0.4
J  0.2  0.2  0.2  -  -    0.2
K  0.1  0.1    -  -  -    0.2
L  0.0  0.2  0.4  -  -    0.3

Upvotes: 1

Gabriel A
Gabriel A

Reputation: 1827

It looks like you don't care about indices so this should work.

r = df1.reset_index(drop=True) / df2.reset_index(drop=True)

Upvotes: -1

Related Questions