CODEWITHSUNDEEP
python-3.xpandasaggregate
user9431057
user9431057

Reputation: 1253

Get Propportions of One Hot Encoded Values While Aggregation - Pandas

I have a df like this,

    Date        Value

0   2019-03-01  0
1   2019-04-01  1
2   2019-09-01  0
3   2019-10-01  1
4   2019-12-01  0
5   2019-12-20  0
6   2019-12-20  0
7   2020-01-01  0

Now, I need to group them by quarter and get the proportions of 1 and 0. So, I get my final output like this,

  Date          Value1 Value0

0 2019-03-31    0      1     
1 2019-06-30    1      0
2 2019-09-30    0      1
3 2019-12-31    0.25   0.75
4 2020-03-31    0      1

I tried the following code, doesn't seem to work.

def custom_resampler(array):
    import numpy as np

    return array/np.sum(array)

>>df.set_index('Date').resample('Q')['Value'].apply(custom_resampler)

Is there a pandastic way I can achieve my desired output?

Upvotes: 0

Views: 28

Answers (1)

sammywemmy
sammywemmy

Reputation: 28709

Resample by quarter, get the value_counts, and unstack. Next, rename the columns, using the name property of the columns. Last, divide each row value by the total per row : 

df = pd.read_clipboard(sep='\s{2,}', parse_dates = ['Date'])


res = (df
       .resample(rule="Q",on="Date")
       .Value
       .value_counts()
       .unstack("Value",fill_value=0)
      )


res.columns = [f"{res.columns.name}{ent}" for ent in res.columns]

res = res.div(res.sum(axis=1),axis=0)
res


          Value0   Value1
Date        
2019-03-31  1.00    0.00
2019-06-30  0.00    1.00
2019-09-30  1.00    0.00
2019-12-31  0.75    0.25
2020-03-31  1.00    0.00

Upvotes: 1

Related Questions