Let's say I have a table as follows. Each row represents a cohort and each column a time period. Some values are nan as that time period hasn't occurred yet.
For each column, I want to get the mean of the first four values (excluding nan). Is there an easy way to accomplish this in pandas?
| name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|----------|----:|-----------:|------------:|-----------:|-------------:|-------------:|------------:|-------------:|-------------:|-------------:|-------------:|-------------:|
| 2019 | nan | -0.0127273 | -0.0202578 | -1 | nan | nan | nan | nan | nan | nan | nan | nan |
| 2018 | nan | -0.0245184 | -0.0143627 | -0.0309654 | -0.0131579 | -0.0171429 | -1 | nan | nan | nan | nan | nan |
| 2017 | nan | -0.0122807 | -0.00888099 | -0.0340502 | -0.0222635 | -0.0151803 | -0.0346821 | 0.011976 | -0.0118343 | -1 | nan | nan |
| 2016 | nan | -0.0196429 | -0.010929 | -0.0570902 | -0.0195312 | 0.00199203 | -0.027833 | -0.0102249 | -0.0103306 | 0.00835073 | -0.00828157 | -0.0605428 |
| 2015 | nan | -0.0158451 | -0.0125224 | -0.0525362 | -0.0114723 | -0.0154739 | -0.0314342 | -0.00811359 | -0.00817996 | -0.00206186 | 0.00206612 | -0.0206186 |
| 2014 | nan | -0.0175439 | -0.00892857 | -0.0486486 | -0.00757576 | -0.0114504 | -0.030888 | -0.0059761 | -0.0140281 | 0.0121951 | -0.00803213 | -0.0425101 |
| 2013 | nan | -0.0250447 | -0.00550459 | -0.0682657 | -0.0158416 | -0.00402414 | -0.0323232 | -0.0167015 | -0.00212314 | -0.0191489 | 0.0021692 | -0.017316 |
| 2012 | nan | -0.0118443 | -0.00171233 | -0.0703259 | -0.00184502 | 0.00554529 | -0.0294118 | -0.00757576 | -0.00763359 | -0.00769231 | -0.00387597 | 0.00194553 |
| 2011 | nan | -0.0174825 | -0.0106762 | -0.0377698 | -0.0261682 | -0.00767754 | -0.0174081 | -0.015748 | -0.004 | -0.0200803 | 0.00409836 | -0.0285714 |
| 2010 | nan | -0.0234657 | -0.00369686 | -0.0352505 | -0.0269231 | 0.00395257 | -0.019685 | -0.0100402 | -0.00608519 | -0.0102041 | -0.0123711 | -0.00626305 |
IIUC, we can stack to remove any NaN values, then apply a groupby to get the count of each value, creating a pseudo index of sorts.
We can then pass a final groupby and concat the mean at the bottom.
s = df.iloc[:, 1:].T.stack().to_frame("vals")
s["idx"] = s.groupby(level=0).cumcount()
s1 = (
s[s["idx"].le(3)]
.groupby(level=0)
.mean()
.drop("idx", 1)
.stack()
.unstack(0)
.reset_index(0, drop=True)
)
# s1.columns = s1.columns.astype(str) if columns are strings.
df_mean = pd.concat([df,s1])
Another method would be to melt with append.
s = pd.melt(df.iloc[:,1:]).dropna()
s1 = s.assign(key=s.groupby("variable").cumcount()).query("key <= 3").groupby("variable")[
"value"
].mean().to_frame('mean').T
df_mean = df.append(s1)
name 1 2 3 4 5 6 7 \
1 2019.0 NaN -0.012727 -0.020258 -1.000000 NaN NaN NaN
2 2018.0 NaN -0.024518 -0.014363 -0.030965 -0.013158 -0.017143 -1.000000
3 2017.0 NaN -0.012281 -0.008881 -0.034050 -0.022263 -0.015180 -0.034682
4 2016.0 NaN -0.019643 -0.010929 -0.057090 -0.019531 0.001992 -0.027833
5 2015.0 NaN -0.015845 -0.012522 -0.052536 -0.011472 -0.015474 -0.031434
6 2014.0 NaN -0.017544 -0.008929 -0.048649 -0.007576 -0.011450 -0.030888
7 2013.0 NaN -0.025045 -0.005505 -0.068266 -0.015842 -0.004024 -0.032323
8 2012.0 NaN -0.011844 -0.001712 -0.070326 -0.001845 0.005545 -0.029412
9 2011.0 NaN -0.017483 -0.010676 -0.037770 -0.026168 -0.007678 -0.017408
10 2010.0 NaN -0.023466 -0.003697 -0.035250 -0.026923 0.003953 -0.019685
mean NaN NaN -0.017292 -0.013608 -0.280526 -0.016606 -0.011451 -0.273487
8 9 10 11 12
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN
3 0.011976 -0.011834 -1.000000 NaN NaN
4 -0.010225 -0.010331 0.008351 -0.008282 -0.060543
5 -0.008114 -0.008180 -0.002062 0.002066 -0.020619
6 -0.005976 -0.014028 0.012195 -0.008032 -0.042510
7 -0.016702 -0.002123 -0.019149 0.002169 -0.017316
8 -0.007576 -0.007634 -0.007692 -0.003876 0.001946
9 -0.015748 -0.004000 -0.020080 0.004098 -0.028571
10 -0.010040 -0.006085 -0.010204 -0.012371 -0.006263
mean -0.003085 -0.011093 -0.245379 -0.003020 -0.035247
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments