I have two dataframes, of shape m*5 and 5*n. The column names of the 1st dataframe with 5 columns is same as the index of the 2nd dataframe with 5 rows. I want to multiply each element of each row in the 1st dataframe with the 1st 2 columns of the 2nd dataframe with having corresponding row index in the 2nd dataframe. Please find below the dataframes for reference:
DataFrame 1:
%age_paid_0.0 %age_paid_0.1 %age_paid_0.2 %age_paid_0.3 \
account_angaza_id
AC005839 0.299221 0.377086 0.454950 0.532814
AC005842 0.299221 0.299221 0.521691 0.521691
AC005843 0.299221 0.377086 0.454950 0.532814
AC005851 0.243604 0.310345 0.354839 0.354839
AC005852 0.299221 0.377086 0.454950 0.532814
AC005853 0.299221 0.377086 0.454950 0.532814
AC005856 0.299221 0.377086 0.454950 0.532814
AC005858 0.299221 0.377086 0.454950 0.532814
AC005859 0.332592 0.432703 0.543938 0.650723
AC005860 0.288098 0.365962 0.421580 0.532814
%age_paid_0.4 %age_paid_0.5
account_angaza_id
AC005839 0.610679 0.688543
AC005842 0.521691 0.521691
AC005843 0.610679 0.766407
AC005851 0.510567 0.555061
AC005852 0.610679 0.766407
AC005853 0.610679 0.688543
AC005856 0.610679 0.766407
AC005858 0.543938 0.588432
AC005859 0.650723 0.739711
AC005860 0.532814 0.632925
Dataframe 2:
0 1
%age_paid_0.0 0.369886 0.673442
%age_paid_0.1 0.409603 0.374386
%age_paid_0.2 0.425269 0.058336
%age_paid_0.3 0.425229 -0.191075
%age_paid_0.4 0.415484 -0.369895
%age_paid_0.5 0.401384 -0.479141
Expected dataframe:
0 1
AC005839 1.xxxxxx 2.xxxxxx
AC005840 xxxxxxxx xxxxxxxx
AC005840 xxxxxxxx xxxxxxxx
The formula is
dataframe3.loc['AC005839',0] = dataframe1.loc['AC005839',%age_paid_0.1]*dataframe2.loc[%age_paid_0.1,0]+dataframe1.loc['AC005839',%age_paid_0.2]*dataframe2.loc[%age_paid_0.2,0]+...+dataframe1.loc['AC005839',%age_paid_0.5]*dataframe2.loc[%age_paid_0.5,0]
dataframe3.loc['AC005839',1] = dataframe1.loc['AC005839',%age_paid_0.1]*dataframe2.loc[%age_paid_0.1,1]+dataframe1.loc['AC005839',%age_paid_0.2]*dataframe2.loc[%age_paid_0.2,1]+...+dataframe1.loc['AC005839',%age_paid_0.5]*dataframe2.loc[%age_paid_0.5,1]
Any kind of help will be really appreciated. Basically I am trying to convert a variables into the same plane as the principle components are. Thanks in advance!
This is a dot product. Since your index/column labels match, all you need is:
df1.dot(df2)