pythonpandasdataframegroup-by

How to get second pandas dataframe showing net trade based on first pandas dataframe containing one-directional trade in Python?

I have a pandas dataframe df1 as shown below: It shows exports volume from A to B, B to A and A to C in three rows. Trade is possible in both directions. enter image description here

df1.to_dict() returns

{'Country1': {0: 'A', 1: 'B', 2: 'A'}, 'Country2': {0: 'B', 1: 'A', 2: 'C'}, 'Value': {0: 3, 1: 5, 2: 3}}

I want a second dataframe df2 based on df1 which shows the net trade volume between countries. For example, A to C has a net trade volume of 3 units, and B to A has a net trade volume of 2 units (5-3). This needs to be reflected in the second dataframe as shown below: enter image description here

How can I automate creating df2 based on df1? I have large number of countries, so I want to automate this process.

Solution

  • You could swap the names, merge and filter:

    val = (df[['Country1', 'Country2']]
 .merge(df.rename(columns={'Country1': 'Country2',
                           'Country2': 'Country1'}),
        how='left')['Value']
 .rsub(df['Value'], fill_value=0)
)

out = (df.assign(**{'Net Value': val})
         .query('`Net Value` >= 0')
         .drop(columns='Value')
      )

    Output:

      Country1 Country2  Net Value
1        B        A        2.0
2        A        C        3.0