How to calculate the difference in yellow cards

[Dario Maimone Ansaldo Patti]

Dear All,

I am running an analysis about the behavior of some footbal teams in the Italian championship. An example of data is attached:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte giornate str10(squadra sfid2) byte ammoniti wanted
2 "Atalanta"   "Juventus"   2 1
2 "Bologna"    "Milan"      1
2 "Brescia"    "Lecce"      2
2 "Cagliari"   "Fiorentina" 1
2 "Chievo"     "Livorno"    3
2 "Fiorentina" "Cagliari"   1
2 "Inter"      "Palermo"    3
2 "Juventus"   "Atalanta"   1 -1
2 "Lazio"      "Reggina"    1
2 "Lecce"      "Brescia"    4
2 "Livorno"    "Chievo"     3
2 "Messina"    "Roma"       5
2 "Milan"      "Bologna"    2
2 "Palermo"    "Inter"      1
2 "Parma"      "Udinese"    3
2 "Reggina"    "Lazio"      3
2 "Roma"       "Messina"    3
2 "Sampdoria"  "Siena"      3
2 "Siena"      "Sampdoria"  2
2 "Udinese"    "Parma"      2
end

I need to calculate the difference in the variable "ammoniti" within the same match. Consider the bold cases: THe team Atalanta received 2 yellow cards (ammoniti) when playing against Juventus, which instead received 1 yellow card. What I would like to obtain (wanted) is the difference in the number of yellow cards. In the first case that difference is 1. In the second case, it is -1. It is a bit time consuming to fill in the data "manually". Is there any way I can do it more quickly?

EDIT

I tried the following:

gen diffy=0
levelsof squadra, local(sq)
levelsof sfid2, local(sf)
foreach s of local sq{
foreach f of local sf{
replace diffy=ammoniti-ammoniti if "`s'"=="`f'"
}
}

But it does not work.

Any suggestion is highly appreciated.

Thanks,

Dario

[Andrew Musau]

You probably should structure your data differently. Assuming that you have a home-team identifier, reshape so that you have home team and away team.

[Dario Maimone Ansaldo Patti]

Andrew Musau Thanks. Yes I have a home-team identifier, but I cannot see how I could reshape it.

[Nick Cox]

See https://www.stata-journal.com/articl...article=dm0043

* Example generated by -dataex-. For more info, type help dataex
clear
input byte giornate str10(squadra sfid2) byte ammoniti 
2 "Atalanta"   "Juventus"   2 
2 "Bologna"    "Milan"      1
2 "Brescia"    "Lecce"      2
2 "Cagliari"   "Fiorentina" 1
2 "Chievo"     "Livorno"    3
2 "Fiorentina" "Cagliari"   1
2 "Inter"      "Palermo"    3
2 "Juventus"   "Atalanta"   1 
2 "Lazio"      "Reggina"    1
2 "Lecce"      "Brescia"    4
2 "Livorno"    "Chievo"     3
2 "Messina"    "Roma"       5
2 "Milan"      "Bologna"    2
2 "Palermo"    "Inter"      1
2 "Parma"      "Udinese"    3
2 "Reggina"    "Lazio"      3
2 "Roma"       "Messina"    3
2 "Sampdoria"  "Siena"      3
2 "Siena"      "Sampdoria"  2
2 "Udinese"    "Parma"      2
end

gen first = cond(squadra < sfid2, squadra, sfid2) 
gen second = cond(squadra > sfid2, squadra, sfid2)

egen pair = group(first second), label 

local a ammoniti 
bysort pair : gen wanted = cond(_n == 1, `a'[1] - `a'[2], `a'[2] - `a'[1])

l pair sq sf ammoniti wanted, sepby(pair)

    +-------------------------------------------------------------------+
     |                pair      squadra        sfid2   ammoniti   wanted |
     |-------------------------------------------------------------------|
  1. |   Atalanta Juventus     Atalanta     Juventus          2        1 |
  2. |   Atalanta Juventus     Juventus     Atalanta          1       -1 |
     |-------------------------------------------------------------------|
  3. |       Bologna Milan        Milan      Bologna          2        1 |
  4. |       Bologna Milan      Bologna        Milan          1       -1 |
     |-------------------------------------------------------------------|
  5. |       Brescia Lecce      Brescia        Lecce          2       -2 |
  6. |       Brescia Lecce        Lecce      Brescia          4        2 |
     |-------------------------------------------------------------------|
  7. | Cagliari Fiorentina     Cagliari   Fiorentina          1        0 |
  8. | Cagliari Fiorentina   Fiorentina     Cagliari          1        0 |
     |-------------------------------------------------------------------|
  9. |      Chievo Livorno      Livorno       Chievo          3        0 |
 10. |      Chievo Livorno       Chievo      Livorno          3        0 |
     |-------------------------------------------------------------------|
 11. |       Inter Palermo      Palermo        Inter          1       -2 |
 12. |       Inter Palermo        Inter      Palermo          3        2 |
     |-------------------------------------------------------------------|
 13. |       Lazio Reggina        Lazio      Reggina          1       -2 |
 14. |       Lazio Reggina      Reggina        Lazio          3        2 |
     |-------------------------------------------------------------------|
 15. |        Messina Roma         Roma      Messina          3       -2 |
 16. |        Messina Roma      Messina         Roma          5        2 |
     |-------------------------------------------------------------------|
 17. |       Parma Udinese      Udinese        Parma          2       -1 |
 18. |       Parma Udinese        Parma      Udinese          3        1 |
     |-------------------------------------------------------------------|
 19. |     Sampdoria Siena    Sampdoria        Siena          3        1 |
 20. |     Sampdoria Siena        Siena    Sampdoria          2       -1 |
     +-------------------------------------------------------------------+

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte giornate str10(squadra sfid2) byte ammoniti float(wanted home)
2 "Atalanta"   "Juventus"   2  1 1
2 "Bologna"    "Milan"      1  . 1
2 "Brescia"    "Lecce"      2  . 1
2 "Cagliari"   "Fiorentina" 1  . 1
2 "Chievo"     "Livorno"    3  . 1
2 "Fiorentina" "Cagliari"   1  . 0
2 "Inter"      "Palermo"    3  . 1
2 "Juventus"   "Atalanta"   1 -1 0
2 "Lazio"      "Reggina"    1  . 1
2 "Lecce"      "Brescia"    4  . 0
2 "Livorno"    "Chievo"     3  . 0
2 "Messina"    "Roma"       5  . 1
2 "Milan"      "Bologna"    2  . 0
2 "Palermo"    "Inter"      1  . 0
2 "Parma"      "Udinese"    3  . 1
2 "Reggina"    "Lazio"      3  . 0
2 "Roma"       "Messina"    3  . 0
2 "Sampdoria"  "Siena"      3  . 1
2 "Siena"      "Sampdoria"  2  . 0
2 "Udinese"    "Parma"      2  . 0
end

frame put giornate squadra sfid2 ammoniti if !home, into(away)
drop if !home
frlink 1:1 giornate squadra sfid2, frame(away giornate sfid2 squadra)
frget ammoniti2= ammoniti, from(away)
drop wanted home away
frame drop away
gen wanted= ammoniti- ammoniti2

So squadra is the home team and sfid2 the away team. You may go back to the duplicated match days, but I do not see how you analyze data in that way if the observation is not a match.

Res.:

. l, sep(0)

     +------------------------------------------------------------------+
     | giornate     squadra        sfid2   ammoniti   ammoni~2   wanted |
     |------------------------------------------------------------------|
  1. |        2    Atalanta     Juventus          2          1        1 |
  2. |        2     Bologna        Milan          1          2       -1 |
  3. |        2     Brescia        Lecce          2          4       -2 |
  4. |        2    Cagliari   Fiorentina          1          1        0 |
  5. |        2      Chievo      Livorno          3          3        0 |
  6. |        2       Inter      Palermo          3          1        2 |
  7. |        2       Lazio      Reggina          1          3       -2 |
  8. |        2     Messina         Roma          5          3        2 |
  9. |        2       Parma      Udinese          3          2        1 |
 10. |        2   Sampdoria        Siena          3          2        1 |
     +------------------------------------------------------------------+

[Dario Maimone Ansaldo Patti]

Nick Cox Thanks a lot for your suggestion. Andrew Musau Thanks for your suggestion. In my research the unit of observation is not the match but the team itself. Therefore, for each match I have two observations: the home team and the away team.