Pandas: group and custom transfrom dataframe long to wide

user_11932905 0
Viewed 65

I have a dataframe in following form:

+---------+-------+-------+---------+---------+
| payment | type  |  err  | country | source  |
+---------+-------+-------+---------+---------+
| visa    | type1 | OK    | AR      | source1 |
| paypal  | type1 | OK    | DE      | source1 |
| mc      | type2 | ERROR | AU      | source2 |
| visa    | type3 | OK    | US      | source2 |
| visa    | type2 | OK    | FR      | source3 |
| visa    | type1 | OK    | FR      | source2 |
+---------+-------+-------+---------+---------+

df = pd.DataFrame({'payment':['visa','paypal','mc','visa','visa','visa'], 
             'type':['type1','type1','type2','type3','type2','type1'],
             'err':['OK','OK','ERROR','OK','OK','OK'],
             'country':['AR','DE','AU','US','FR','FR'],
             'source':['source1','source1','source2','source2','source3','source2'],
             })

My goal is to transform it so that I have group by payment and country, but create new columns:
number_payments - just count for groupby,
num_errors - number of ERROR values for group,
num_type1.. num_type3 - number of corresponding values in column type (only 3 possible values),
num_source1.. num_source3 - number of corresponding values in column source (only 3 possible values).

Like this:

+---------+---------+-----------------+------------+-----------+-----------+-----------+-------------+-------------+-------------+
| payment | country | number_payments | num_errors | num_type1 | num_type2 | num_type3 | num_source1 | num_source2 | num_source3 |
+---------+---------+-----------------+------------+-----------+-----------+-----------+-------------+-------------+-------------+
| visa    | AR      |               1 |          0 |         1 |         0 |         0 |           1 |           0 |           0 |
| visa    | US      |               1 |          0 |         0 |         0 |         1 |           0 |           1 |           0 |
| visa    | FR      |               2 |          0 |         1 |         2 |         0 |           0 |           1 |           1 |
| mc      | AU      |               1 |          1 |         0 |         1 |         0 |           0 |           1 |           0 |
+---------+---------+-----------------+------------+-----------+-----------+-----------+-------------+-------------+-------------+

I tried to combine pandas groupby and pivot, but failed to make all and it's ugly. I'm pretty sure that there are some good and fast methods to do this..
Appreciate any help.

pandas-groupby pandas dataframe python
2 Answers

First it is better to clean the data for your stated purposes:

df['err_bool'] = (df['err'] == 'ERROR').astype(int)

Then we use groupby for applicable columns:

df_grouped = df.groupby(['country','payment']).agg({
        'number_payments' : 'count',
        'err_bool':sum})

Then we can use the pivot for type and source:

df['dummy'] = 1
df_type = df.pivot(
    index=['country','payment'], 
    columns='type', 
    value='dummy', 
    aggfunc = np.sum
)

df_source = df.pivot_table(
    index=['country','payment'], 
    columns='source', 
    value='dummy', 
    aggfunc = np.sum
)

Then we join everything together:

df_grouped = df_grouped.join(df_type).join(df_source)
user_51165590

You can use get dummies and then select the 2 grouper columns and create the group, then join the size with sum:

c = df['err'].eq("ERROR")
g = (df[['payment','country']].assign(num_errors=c,
                   **pd.get_dummies(df[['type','source']],prefix=['num','num']))
 .groupby(['payment','country']))
out = g.size().to_frame("number_payments").join(g.sum()).reset_index()

print(out)

  payment country  number_payments  num_errors  num_type1  num_type2  \
0      mc      AU                1           1          0          1   
1  paypal      DE                1           0          1          0   
2    visa      AR                1           0          1          0   
3    visa      FR                2           0          1          1   
4    visa      US                1           0          0          0   

   num_type3  num_source1  num_source2  num_source3  
0          0            0            1            0  
1          0            1            0            0  
2          0            1            0            0  
3          0            0            1            1  
4          1            0            1            0
user_98406370
Related