I have a data set in a dataframe that's almost 9 million rows and 30 columns. As the columns count up, the data becomes more specific thus leading the data in the first columns to be very repetitive. See example:

I'm looking to feed that information in to a result set like an object for example:

park code: acad
campgrounds: campground 1, campground 2
parking lots: parking_lot1, parking_lot2, parking_lot3

park code: bisc
campgrounds: campground3, ....
.......

etc.

I'm completely at a loss how to do this with pandas, and I'm learning as I go as I'm used to working in SQL and databases not with pandas. If you want to see the code that's gotten me this far, here it is:

function call:

data_handler.fetch_results(['Wildlife Watching', 'Arts and Culture'], ['Restroom'], ['Acadia National 
    Park'], ['ME'])

def fetch_results(self, activities_selection, amenities_selection, parks_selection, states_selection):
    activities_selection_df = self.activities_df['park_code'][self.activities_df['activity_name'].
    isin(activities_selection)].drop_duplicates()
    amenities_selection_df = self.amenities_parks_df['park_code'][self.amenities_parks_df['amenity_name'].
    isin(amenities_selection)].drop_duplicates()
    states_selection_df = self.activities_df['park_code'][self.activities_df['park_states'].
    isin(states_selection)].drop_duplicates()
    parks_selection_df = self.activities_df['park_code'][self.activities_df['park_name'].
    isin(parks_selection)].drop_duplicates()
    data = activities_selection_df[activities_selection_df.isin(amenities_selection_df) &
                                   activities_selection_df.isin(states_selection_df) & activities_selection_df.
                                   isin(parks_selection_df)].drop_duplicates()
    pandas_select_df = pd.DataFrame(data, columns=['park_code'])

    results_df = pd.merge(pandas_select_df, self.activities_df, on='park_code', how='left')
    results_df = pd.merge(results_df, self.amenities_parks_df[['park_code', 'amenity_name', 'amenity_url']],
                          on='park_code', how='left')
    results_df = pd.merge(results_df, self.campgrounds_df[['park_code', 'campground_name', 'campground_url',
                                                           'campground_road', 'campground_classification',
                                                           'campground_general_ADA',
                                                           'campground_wheelchair_access',
                                                           'campground_rv_info', 'campground_description',
                                                           'campground_cell_reception', 'campground_camp_store',
                                                           'campground_internet', 'campground_potable_water',
                                                           'campground_toilets',
                                                           'campground_campsites_electric',
                                                           'campground_staff_volunteer']], on='park_code',
                                                            how='left')
    results_df = pd.merge(results_df, self.places_df[['park_code', 'places_title', 'places_url']],
                          on='park_code', how='left')
    results_df = pd.merge(results_df, self.parking_lot_df[
        ['park_code', "parking_lots_name", "parking_lots_ADA_facility_description",
         "parking_lots_is_lot_accessible", "parking_lots_number_oversized_spaces",
         "parking_lots_number_ADA_spaces",
         "parking_lots_number_ADA_Step_Free_Spaces", "parking_lots_number_ADA_van_spaces",
         "parking_lots_description"]], on='park_code', how='left')
    # print(self.campgrounds_df.to_string(max_rows=20))
    print(results_df.to_string(max_rows=40))

Any help will be appreciated.

CodePudding user response：

In general, you can group by park_code and collect other columns into lists, then - transform to a dictionary:

df.groupby('park_code').agg({'camp_ground': list, 'parking_lot': list}).to_dict(orient='index')

Sample result:

{'acad ': {'camp_ground': ['campground1 ', 'campground1 ', 'campground2 '],
  'parking_lot': ['parking_lot1', 'parking_lot2', 'parking_lot3']},
 'bisc ': {'camp_ground': ['campground3 '], 'parking_lot': ['parking_lot4']}}