How to divide two dfs with dissimilar column length?

user_14736736 0
Viewed 71

Imagine you have the following df:

lines

+----+-----------------+-----------------+-----------------+-----------------+-----------------+
|    |   line amount#1 |   line amount#2 |   line amount#3 |   line amount#4 |   line amount#5 |
|----+-----------------+-----------------+-----------------+-----------------+-----------------|
|  0 |             250 |          250    |           250   |          250    |             250 |
|  1 |             250 |          250    |           250   |          250    |             250 |
|  2 |              14 |            7.35 |             9.9 |           27.75 |             nan |
+----+-----------------+-----------------+-----------------+-----------------+-----------------+








perc
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
|   btw-percentage#1 |   btw-percentage#2 |   btw-percentage#3 |   btw-percentage#4 |   btw-percentage#5 |   btw-percentage#6 |
|--------------------+--------------------+--------------------+--------------------+--------------------+--------------------|
|                10  |                nan |                nan |                nan |                nan |                nan |
|                nan |                nan |                nan |                nan |                nan |                nan |
|                nan |                nan |                nan |                nan |                nan |                nan |
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+




update  = lines.to_numpy() / perc.to_numpy()

when dividing I get the following error:

ValueError: operands could not be broadcast together with shapes (3,5) (3,6)

Which is logical since the columns are not the same shape.

I want to trim the column shape of the perc column based on the length of the lines column. So that would become #5. Is this possible with numpy? desired output after division:

+----+-----------------+-----------------+-----------------+-----------------+-----------------+
|    |   line amount#1 |   line amount#2 |   line amount#3 |   line amount#4 |   line amount#5 |
|----+-----------------+-----------------+-----------------+-----------------+-----------------|
|  0 |             25  |          250    |           250   |          250    |             250 |
|  1 |             250 |          250    |           250   |          250    |             250 |
|  2 |              14 |            7.35 |             9.9 |           27.75 |             nan |
+----+-----------------+-----------------+-----------------+-----------------+-----------------+

Please help!

pandas dataframe numpy python
3 Answers

Try this,

for x, y in zip(lines.columns, perc.columns):
    lines[x] = lines[x] / perc[y].fillna(1)
user_49850990

Let us try with np.divide

np.divide(lines, perc.iloc[:, :lines.shape[1]].fillna(1))

In case the order and number of columns is not fixed then we can filter the common columns by using Index.intersection

c1 = lines.columns.str.replace('line amount', 'btw-percentage')
c2 = c1.intersection(perc.columns, sort=False)

np.divide(lines.loc[:, c1.isin(c2)], perc[c2].fillna(1))

   line amount#1  line amount#2  line amount#3  line amount#4  line amount#5
0           25.0         250.00          250.0         250.00          250.0
1          250.0         250.00          250.0         250.00          250.0
2           14.0           7.35            9.9          27.75            NaN
user_128331660

You can try this:

update = np.divide(lines.to_numpy(), np.delete(perc.to_numpy(), lines.shape, axis=1))
user_156044840
Related