How to quickly group multiple features of data from a dataframe using pandas

user_15617519 0
Viewed 127

Let's say this is my original data:

data = {'library_id': ['A123', 'A456','B123', 'A123', 'A123', 'B123', 'A456'],
     'school': ['A1','A', 'B', 'A', 'A2', 'B', 'A'],
     'charge ($)': [5.0, 10.0, 3.0, 2.5, 1.0, 4.0, 2.5], 
     'check_in_month': [12, 3, 4, 5, 4, 9, 5]}
library =pd.DataFrame(data)

enter image description here

I am trying to get this outcome:

enter image description here

And this is my code to get the outcome:

df = pd.DataFrame(library.library_id.value_counts())

school_list = []
for i in df.index:
    school_list.append(library[library.library_id == i].school.unique())
df['school'] = school_list
df

My question is how I can avoid using lists (i.e. school_list = []) to get the same outcome. Because when I have large data, using lists to append data to a data frame is super time-consuming. Is there any other faster way?

pandas dataframe python
1 Answers

You can try:

result = library.groupby("library_id").agg({"library_id": "size", "school": "unique"})

to get

            library_id       school
library_id
A123                 3  [A1, A, A2]
A456                 2          [A]
B123                 2          [B]

We group by the library_id and then aggregrate it over the group size and the unique entries.

If you don't want library_id appearing on top of the index, you can write result.index.name = None since it is the name of the index of the result.

user_93321870
Related