0

I'm working on an airline dataset. I've to calculate the number of adults, children's and infants per airline_pnr number and then append those values as a column in a data frame.

Pax Type: Passenger type(Adult(ADT), Children(CHD), Infant(INF))

+-------------+----------+
| airline_pnr |Pax_Type  |
+-------------+----------+
| EIPBGB      | ADT      |
| EIPBGB      | ADT      |
| EIPBGB      | CHD      |
| EIPBGB      | INF      |
| UH7EQV      | ADT      |
| UH7EQV      | ADT      |
| YVEEW       | ADT      |
| YVEEW       | ADT      |
| DR6YWR      | ADT      |
| DR6YWR      | ADT      |
| DR6YWR      | ADT      |
| DR6YWR      | CHD      |
| DR6YWR      | INF      |
| QJ2ESP      | ADT      |
| QJ2ESP      | CHD      |
| JL6E9T      | ADT      |
| VGYD5V      | ADT      |
| YVEG1       | ADT      |
| YVEG1       | ADT      |
+-------------+----------+

Expected output:

+--------+----------+--------------+-----------------+---------------+
|air_pnr | Pax Type | no_of_adults | no_of_childrens | no_of_infants |
+--------+----------+--------------+-----------------+---------------+
| EIPBGB | ADT      |            2 |               1 |             1 |
| UH7EQV | ADT      |            2 |               0 |             0 |
| YVEEW  | ADT      |            2 |               0 |             0 |
| DR6YWR | ADT      |            3 |               1 |             1 |
| QJ2ESP | ADT      |            1 |               1 |             0 |
| JL6E9T | ADT      |            1 |               0 |             0 |
| VGYD5V | ADT      |            1 |               0 |             0 |
| YVEG1  | ADT      |            2 |               0 |             0 |
+--------+----------+--------------+-----------------+---------------+

My Efforts:

df= df.value_counts(['airline_pnr', 'Pax Type']) 

df = df.to_frame()

df= df.rename(columns = {0: "freq"})

But not getting the desired results

Karan Kundra
  • 71
  • 5

1 Answers1

0

you can use groupby on the 'air_pnr' variable, and them use the size() which counts the number of occurrences of each value.

df.groupby(['air_pnr','Pax_Type']).size()
David
  • 871
  • 1
  • 5
  • 13
  • 1
    The community encourages adding explanations alongisde code, rather than purely code-based answers (see [here](https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers)). – costaparas Jan 29 '21 at 13:15
  • Hey, your code gives an AttributeError: 'DataFrameGroupBy' object has no attribute 'value_counts' – Karan Kundra Jan 31 '21 at 08:21