Hello,
I am doing some analysis in the US airline industry. At the moment I am trying to compute in which countries the airline fly (in percentage of the Airtime for each Airline in each Quarter), depending on the airtime. Therefore I have a table like this:
The Airline always start or lands in the US. Now I want to create for each country a new Variable and compute the percentage of airtime for each. If it is a international flight each country accounts for the half Airtime. So the result should be something like this:
After this there have to be one final step. Every Airline should only have one row per Quarter
The real table contains around 400,000 observations and probably every country there is in the world. I hope someone of you have a solution for my problem and sorry if my English isn´t the best =)
Thanks
I am doing some analysis in the US airline industry. At the moment I am trying to compute in which countries the airline fly (in percentage of the Airtime for each Airline in each Quarter), depending on the airtime. Therefore I have a table like this:
Airtime | Airline_ID | Origin | Destination | Year | Quarter |
60 | 1 | US | CA | 2016 | 1 |
30 | 1 | US | US | 2016 | 1 |
30 | 1 | CA | US | 2016 | 1 |
60 | 2 | US | JAM | 2016 | 1 |
30 | 2 | CA | US | 2016 | 2 |
Airtime | Airline_ID | Origin | Destination | Year | Quarter | Sum_Airtime | US | CA | JAM |
60 | 1 | US | CA | 2016 | 1 | 120 | 0.25 | 0.25 | |
30 | 1 | US | US | 2016 | 1 | 120 | 0.25 | ||
30 | 1 | CA | US | 2016 | 1 | 120 | 0.125 | 0.125 | |
60 | 2 | US | JAM | 2016 | 1 | 60 | 0.5 | 0.5 | |
30 | 2 | CA | US | 2016 | 2 | 30 | 0.5 | 0.5 |
Airline_ID | Year | Quarter | Sum_Airtime | US | CA | JAM |
1 | 2016 | 1 | 120 | 0.625 | 0.375 | |
2 | 2016 | 1 | 60 | 0.5 | 0.5 | |
2 | 2016 | 2 | 30 | 0.5 | 0.5 |
Thanks
Comment