Geek asks and answers

Calculate pairwise difference from specific columns in a data frame

I have the following framework where I show how many times I saw the transition from Item1 to Item 2. For example, there is one transition from A to B, 2 from A to C, 1 from C to A

    Item1   Item2   Moves
  1  A       B       1
  2  A       C       2
  3  B       D       3
  4  C       A       1
  5  C       B       5
  6  D       B       4
  7  D       C       1

I would like to calculate the difference between the two elements, so the new built Dataframe will be as follows

    Item1   Item2   Moves
  1  A       B       1
  2  A       C       1
  3  B       D      -1
  4  C       B       5
  5  D       C       1

Does anyone know how to do this using Pandas? I think I need to index the first two columns, but I'm brand new in Pandas, and I have a lot of difficulties. Thanks

EDIT There can be no duplicate pairs. For example, you cannot see twice a-> b (but you can, of course, see b-> a)

+5
source share
2 answers

, - , , , . , , "Item1" , "Item2". "Item1" "Item2" "" . groupby .

>>> df
  Item1 Item2  Moves
0     A     B      1
1     A     C      2
2     B     D      3
3     C     A      1
4     C     B      5
5     D     B      4
6     D     C      1
>>> swapidx = df['Item1'] < df['Item2']
>>> df1 = df[swapidx]
>>> df2 = df[swapidx^True]
>>> df1
  Item1 Item2  Moves
0     A     B      1
1     A     C      2
2     B     D      3
>>> df2
  Item1 Item2  Moves
3     C     A      1
4     C     B      5
5     D     B      4
6     D     C      1
>>> df2[['Item1', 'Item2']] = df2[['Item2', 'Item1']]
>>> df2['Moves'] = df2['Moves']*-1
>>> df2
  Item1 Item2  Moves
3     A     C     -1
4     B     C     -5
5     B     D     -4
6     C     D     -1
>>> df3 = df1.append(df2)
>>> df3.groupby(['Item1', 'Item2'], as_index=False).sum()
  Item1 Item2  Moves
0     A     B      1
1     A     C      1
2     B     C     -5
3     B     D     -1
4     C     D     -1
+3

:

, Item1 Item2.

In [11]: df['Items'] = df.apply(lambda row: row['Item1'] + row['Item2'], axis=1)

In [12]: df
Out[12]: 
  Item1 Item2  Moves Items
1     A     B      1    AB
2     A     C      2    AC
3     B     D      3    BD
4     C     A      1    CA
5     C     B      5    CB
6     D     B      4    DB
7     D     C      1    DC

Items , , Moves:

In [13]: df[['Items','Moves']] = df.apply(lambda row: (row[['Items', 'Moves']])
                                                       if row['Items'][0] <= row['Items'][1]
                                                       else (row['Items'][::-1], -row['Moves']),
                                          axis=1)

In [14]: df
Out[14]: 
  Item1 Item2  Moves Items
1     A     B      1    AB
2     A     C      2    AC
3     B     D      3    BD
4     C     A     -1    AC
5     C     B     -5    BC
6     D     B     -4    BD
7     D     C     -1    CD

In [15]: g = df.groupby('Items')

In [16]: g.sum()
Out[16]: 
       Moves
Items       
AB         1
AC         1
BC        -5
BD        -1
CD        -1

, .

, hackey :

In [17]: df1 = g.first() # the first row in each group

In [18]: df1.Moves = g.sum()

In [19]: df2 = df1.reset_index(drop=True)

In [20]: df2
Out[20]: 
  Item1 Item2  Moves
0     A     B      1
1     A     C      1
2     C     B     -5
3     B     D     -1
4     D     C     -1

, ( , , DC, CD):

In [21]: df2.Moves = df2.apply(lambda row: row['Moves']
                                            if row['Item1'] <= row['Item2']
                                            else -row['Moves'],
                                axis=1)

In [22]: df2
Out[22]: 
  Item1 Item2  Moves
0     A     B      1
1     A     C      1
2     C     B      5
3     B     D     -1
4     D     C      1
+1

More articles:


All Articles