Search code examples
pythondataframemultiplication

Python multiple two identical dataframe to form a new dataframe

I've 2 dataframes which look similar:

df1:

enter image description here

df2:

enter image description here

I want to update the column "length" in df1 by multiplying the values in df2 (for the "country" - "city" combination):

enter image description here

Did some Google, but could only find something like, merge or concat. Nothing like multiplying between dataframes.

Any help would be much appreciated.

Solution

  • The Pandas Multiindex feature is made for these kind of calculations. Re-index the dataframes to

    df1 = df1.set_index(['country', 'city', 'road'])
df1 
                       length
country city    road    
us      ny      m1     10
                m2     20
        la      m3     30
                m4     40
                m5     50

df2 = df2.set_index(['country', 'city'])
df2
              length
country city    
us      ny    1
        la    2

    and then simply multiply the 2 frames:

    df1 = df1 * df2
df1
                      length
country city    road    
us      la      m3    60
                m4    80
                m5    100
        ny      m1    10
                m2    20

    If you like you can of cause reset the index afterwards:

    df1.reset_index()

country city    road    length
us      la      m3      60
us      la      m4      80
us      la      m5      100
us      ny      m1      10
us      ny      m2      20