Combining similar rows in Stata / python

Question

I am doing some data prep for graph analysis and my data looks as follows.

country1   country2   pair      volume
USA         CHN       USA_CHN   10
CHN         USA       CHN_USA   5 
AFG         ALB       AFG_ALB   2
ALB         AFG       ALB_AFG   5

I would like to combine them such that

country1   country2   pair      volume
USA         CHN       USA_CHN   15
AFG         ALB       AFG_ALB   7 

Is there a simple way for me to do so in Stata or python? I've tried making a duplicate dataframe and renamed the 'pair' as country2_country1, then merged them, and dropped duplicate volumes, but it's a hairy way of going about things: I was wondering if there is a better way.

If it helps to know, my data format is for a directed graph, and I am converting it to undirected.

Answer 1

Your key must consist of sets of two countries, so that they compare equal regardless of order. In Python/Pandas, this can be accomplished as follows.

import pandas as pd
import io

# load in your data
s = """
country1   country2   pair      volume
USA        CHN        USA_CHN   10
CHN        USA        CHN_USA   5
AFG        ALB        AFG_ALB   2
ALB        AFG        ALB_AFG   5
"""
data = pd.read_table(io.BytesIO(s), sep='\s+')

# create your key (using frozenset instead of set, since frozenset is hashable)
key = data[['country1', 'country2']].apply(frozenset, 1)

# group by the key and aggregate using sum()
print(data.groupby(key).sum())

This results in

            volume
(CHN, USA)      15
(AFG, ALB)       7

which isn't exactly what you wanted, but you should be able to get it into the right shape from here.