Create sum rows based on conditions

user_6150310 0
Viewed 101

I have a large dataframe with dates and numbers for US states and counties. What I want to do is create a new row that does state level sum based on the county level numbers for each day.

An example dataframe, assume there are more rows and columns:

date        state.  county.  sales .....
2020-01-22. CA      1        100
2020-01-22. CA      2        100
2020-01-22  FL      3        100
2020-01-22  FL      6        100
2020-01-22  NY      9        100
2020-01-22  NY      7        100
2020-01-23  CA      2        200
2020-01-23  FL      2        400
2020-01-23  NY      2        300
2020-01-24  CA      3        600
2020-01-24  FL      3        700
2020-01-24  NY      3        100
.
.
.

The county numbers are arbitrary. What I would want to do is to create a new row for each day for each state to do a sum of the sales for that day All other columns can be empty. The summation row for the states should not have any counties, so that it signals its state level.

Resulting dataframe:

date        state.  county.  sales .....
2020-01-22. CA      1        100
2020-01-22. CA      2        100
2020-01-22  FL      3        100
2020-01-22  FL      6        100
2020-01-22  NY      9        100
2020-01-22  NY      7        100
2020-01-23  CA      2        200
2020-01-23  FL      2        400
2020-01-23  NY      2        300
2020-01-24  CA      3        600
2020-01-24  FL      3        700
2020-01-24  NY      3        100
2020-01-22. CA               200
2020-01-22. FL               200
2020-01-22. NY               200
.
.
.

Any help would be appreciated!

pandas dataframe python-3.x sum python
1 Answers

I don't think there is a simple built-in way to do this, but you could do it pretty easily with a for loop and df[column].unique() it would look something like this:

# Save columns to a list
col_list = df.columns.values.tolist()
new_list = []
for state in df['state'].unique():
    for date in df['date'].unique():
        temp = df[(df['state'] == state) & (df['date'] == date)]
        new_list.append([date, state, ' ', temp['sales'].sum(), 
        'nan', 'nan', 'nan', 'nan', 'nan', 'nan'])
        # Make sure you add other columns in new_list.append() so that when you append 
        # the new data to the original dataframe so that you can append the new data 
        # to the original dataframe without error. The ... in the new_list.append()
        # represents additional columns in your original dataframe.

df2 = pd.Dataframe(new_list, columns=col_list)
df = df.append(df2)

If the other columns in your dataframe are label columns, or columns that you will not be using to calculate a singular output, you will need to do something similar to what is done with the state and date columns to insure that when you append it to the list in the for loop you're not appending a series. It would help if you showed ALL the columns, because it is unclear whether or not the above code would result in precisely what you are looking to accomplish not knowing what you want to do with additional columns. You may need to use zip(df['state'].unique(), df['other_col'].unique()) in the for loops to make sure that other columns are processed properly. I hope my answer is helpful. If I knew what you wanted to do with the other columns I could answer more precisely and clearly and help you accomplish exactly what you want to accomplish.

Also, do you NEED to have the results (the summation of county data per state) in the same dataframe? If you don't you can simply ignore the last line in the above code.

If this is something that you will be doing more than once, it may make sense to turn it into a function for reuse:

def sum_county_data(data, state_col, date_col, sum_cols):
    new_list = []
    for state in df[state_col].unique():
        for date in df[date_col].unique():
            temp = df[(df[state_col] == state) & (df[date_col] == date)]
            new_list.append([date, state, ' ', temp[sum_cols[0].sum(),
            'nan', 'nan', 'nan', 'nan', 'nan', 'nan'])
    df2 = pd.Dataframe(new_list, columns=df.columns.values.tolist())
    return df2

sum_df = sum_county_data(df, 'state', 'date', ['sales'])
df = df.append(sum_df)

When you use df.append() you have to make sure that the columns match precisely. You can add ignore_index=True to df.append() to make sure there's no error as a result of duplicated index values.

user_119799510
Related