converting series to dataframe using 'as_index' and 'reset

I am trying to convert this series of data into dataframe using as_index = False inside groupby method. My goal is to show the total value for month and weekday.

My data This is my main data uber-15.

 Dispatching   Pickup_date       Affiliated locationID month weekDay day hour minute
    0   B02617  2015-05-17 09:47:00   B02617       141      5  Sunday 17   9 47
    1   B02617  2015-05-17 09:47:00   B02617       65       5 Sunday  17   9 47

From this I am extracting month and weekDay.

temp = uber_15.groupby(['month', "weekDay"]).size()

Next I am converting this series to dataframe using as_index.

temp = uber_15.groupby(['month', "weekDay"], as_index=False).size()

But the result is same when I use as_index=False but not working.

I also tried finding online solution where I find about reset_index but this there is column header with "0" which was supposed to be 'size' with reset_index.

temp = uber_15.groupby(['month', "weekDay"]).size().reset_index()

This the goal I am trying to achieve.

this is the output I am getting.

CodePudding user response：

To convert a Pandas Series object to a DataFrame with columns named after the Series indices, you can use the to_frame() method on the Series object. This method converts the Series to a DataFrame with a single column, where the column name is the name of the Series index. Here is an example of how you can use this method to convert your Series object to a DataFrame:

# Create a Series object with the size of each group
temp = uber_15.groupby(['month', "weekDay"]).size()

# Convert the Series to a DataFrame
temp_df = temp.to_frame()

# Rename the column to 'size'
temp_df.columns = ['size']

After running this code, the DataFrame temp_df will have two columns named 'month' and 'weekDay', and a third column named 'size' containing the size of each group.

Alternatively, you can use the reset_index() method on the Series object to convert the Series to a DataFrame, and then rename the 'level_0' column to 'size'. Here is an example of how you can do this:

# Create a Series object with the size of each group
temp = uber_15.groupby(['month', "weekDay"]).size()

# Convert the Series to a DataFrame and rename the 'level_0' column to 'size'
temp_df = temp.reset_index().rename(columns={'level_0': 'size'})

In this case, the resulting DataFrame will have the same structure as the one shown in your goal image.

Note that in both examples, the groupby() method is called with the as_index parameter set to False by default. This means that the month and weekDay values will be used as indices in the resulting Series object, rather than as columns in the DataFrame. This is why you do not see the month and weekDay columns in the output of the groupby() method. If you want to include these values as columns in the DataFrame, you can set the as_index parameter to True when calling the groupby() method, like this:

Copy code

# Create a DataFrame with month and weekDay as columns
temp = uber_15.groupby(['month', "weekDay"], as_index=True).size()

With this change, the resulting DataFrame will have three columns named 'month', 'weekDay', and 'size', where the 'size' column contains the size of each group. You can then use the to_frame() or reset_index() method to convert this DataFrame to the final format you want.

I hope this helps!