How can I multiply two data frames with different column labels in pandas?

Question

I am trying to propagate (add / split / etc) two data frames that have different column labels.

I'm sure this is possible, but what is the best way to do this? I tried using rename to first change the columns on the same df, but (1) I would prefer not to do this and (2) my real data has a multi-index in the columns (where only one layer of the multi-index is marked differently) and renaming seems complicated ad hoc...

So, to try to summarize my question, how can I get df1 * df2 using map to determine the columns to multiply together?

 df1 = pd.DataFrame([1,2,3], index=['1', '2', '3'], columns=['a', 'b', 'c']) df2 = pd.DataFrame([4,5,6], index=['1', '2', '3'], columns=['d', 'e', 'f']) map = {'a': 'e', 'b': 'd', 'c': 'f'} df1 * df2 = ? 

Answer 1

Assuming the index is already aligned, you probably just want to align the columns in the DataFrame in the correct order and separate the .values both DataFrames.

Estimated mapping = {'a' : 'e', 'b' : 'd', 'c' : 'f'} :

 v1 = df1.reindex(columns=['a', 'b', 'c']).values v2 = df2.reindex(columns=['e', 'd', 'f']).values rs = DataFrame(v1 / v2, index=v1.index, columns=['a', 'b', 'c']) 

Answer 2

I was also worried about this problem. It seems that pandas requires matrix multiplication, it is necessary that both data files have the same column names.

I searched a lot and found an example in increasing the parameter by adding one column to the dataframe.

According to your question

 rs = pd.np.multiply(ds2, ds1) 

Rs will have the same column names as ds2.

Suppose we want to multiply several columns by other server columns in the same data frame and add these results to the original data frame.

For example, ds1, ds2 are in the same digital frame ds. We can

 ds[['r1', 'r2', 'r3']] = pd.np.multiply(ds[['a', 'b', 'c']], ds[['d', 'e', 'f']]) 

I hope this helps.

Answer 3

I just stumbled upon the same problem. It seems that pandas wants the column and row index to be aligned to multiply by elements, so you can just rename with your display during multiplication:

 >>> df1 = pd.DataFrame([[1,2,3]], index=['1', '2', '3'], columns=['a', 'b', 'c']) >>> df2 = pd.DataFrame([[4,5,6]], index=['1', '2', '3'], columns=['d', 'e', 'f']) >>> df1 abc 1 1 2 3 2 1 2 3 3 1 2 3 >>> df2 def 1 4 5 6 2 4 5 6 3 4 5 6 >>> mapping = {'a' : 'e', 'b' : 'd', 'c' : 'f'} >>> df1.rename(columns=mapping) * df2 def 1 8 5 18 2 8 5 18 3 8 5 18 

If you need a “natural” column order, you can create a mapping on the fly, for example:

 >>> df1 * df2.rename(columns=dict(zip(df2.columns, df1.columns))) 

for example, to make the “internal Frobenius product” of two matrices, you could do:

 >>> (df1 * df2.rename(columns=dict(zip(df2.columns, df1.columns)))).sum().sum() 96