Aggregating multiple columns Pandas-CodePudding

Currently my csv looks like this:

title	field1	field2	field3	field4
A	A1	A11	553	0
A	A1	A12	94	0
A	A1	A13	30	0
A	A1	{n/a}	0	9586
A	A2	A21	200	0
A	A2	{n/a}	0	3950
A	A3	A31	35	0
A	A3	{n/a}	0	2929

But I am wanting it to look like this:

title	field1	field2	field3	field4
A	A1	A11	553	9586
A	A1	A12	94	9586
A	A1	A13	30	9586
A	A2	A21	200	3950
A	A3	A31	35	2929

This is my code:

def fun(df, cols_to_aggregate, cols_order):
    df = df.groupby(['field1', 'field2'], as_index=False)\
                .agg(cols_to_aggregate)
    df['title'] = 'A'
    df = df[cols_order]
    return df


def create_csv(df, month_date):
    cols_to_aggregate = {'field3': 'sum', 'field4': 'sum'}
    cols_order = ['title', 'field1', 'field2', 'field3']
    funCSV = fun(df, cols_to_aggregate, cols_order)
    return funCSV

Any help would be appreciated as I can't figure out how to match field4 to all of the relevant field2's.

CodePudding user response：

Use:

def fun(df, cols_to_aggregate, cols_order):
    df = df.groupby(['field1', 'field2'], as_index=False)\
                .agg(cols_to_aggregate)
    df['title'] = 'A'
    #aggregate field4 to new column
    df['field4'] = df.groupby('field1')['field4'].transform('sum')
    df = df[cols_order]
    return df


def create_csv(df, month_date):
    cols_to_aggregate = {'field3': 'sum', 'field4': 'sum'}
    #aded value 'field4'
    cols_order = ['title', 'field1', 'field2', 'field3','field4']
    funCSV = fun(df, cols_to_aggregate, cols_order)
    return funCSV

print (create_csv(df, '2015-01').loc[lambda x: x['field2'].ne('{n/a}')])
  title field1 field2  field3  field4
0     A     A1    A11     553    9586
1     A     A1    A12      94    9586
2     A     A1    A13      30    9586
4     A     A2    A21     200    3950
6     A     A3    A31      35    2929

Or if need first non 0 value per field1 use:

def fun(df, cols_to_aggregate, cols_order):
    df = df.groupby(['field1', 'field2'], as_index=False)\
                .agg(cols_to_aggregate)
    df['title'] = 'A'
    df['field4'] = df.groupby('field1')['field4'].transform('first')
    df = df[cols_order]
    return df


def create_csv(df, month_date):
    cols_to_aggregate = {'field3': 'sum', 'field4': 'first'}
    cols_order = ['title', 'field1', 'field2', 'field3','field4']
    funCSV = fun(df, cols_to_aggregate, cols_order)
    return funCSV

print (create_csv(df.replace({'field4':{0:np.nan}}), '2015-01').loc[lambda x: x['field2'].ne('{n/a}')])
  title field1 field2  field3  field4
0     A     A1    A11     553  9586.0
1     A     A1    A12      94  9586.0
2     A     A1    A13      30  9586.0
4     A     A2    A21     200  3950.0
6     A     A3    A31      35  2929.0