applying a conditional lambda statement across a dataframe

user_12222319 0
Viewed 121

I have a dataframe of around 15000 rows by 1000 columns, snippet below:

ID                     1211    2332    3423    4534
Time

2019-10-03 07:00:00     0       1      1        1
2019-10-03 07:10:00     0       0      0        1
2019-10-03 07:20:00     1       1      0        1
2019-07-03 07:30:00     1       1      1        1

This was made into pivot2 using the following commands:

python command 
pivot=df.pivot(index='Time',columns='ID',values='result')

I'd like to summarise this so that I can see daily data, with values from each day having had a sum / count formula applied.

I'm looking to get the data in the following shape:

The rows are by day.

The columns are the count by ID, of all the values for that day which are 1, divided by the total number of observations for that day.

For example, for ID 1211 above, for the date of 2019-10-03, there is 1 occurrence of the value 1, out of three total values. So I'd like a value of 1/3 = 0.33.

ID                     1211    2332    3423    4534
Time

2019-10-03              0.33    0.67   0.33     1.00
2019-07-03              1.00    1.00   1.00     1.00

I hope this makes sense. Any help would be very gratefully received!

pandas apply pivot-table dataframe python
2 Answers

I think we can use groupby + mean

s=df.groupby(df.index.date).mean()
user_79645270

You can use groupby + mean as suggested @WenYoBen

mean_df=df.groupby(df.index.date).mean()
print(mean_df)
ID              1211      2332      3423  4534
2019-07-03  1.000000  1.000000  1.000000   1.0
2019-10-03  0.333333  0.666667  0.333333   1.0

to count values ​​you can use Series.value_counts: 

my_count=df.groupby(df.index.date).apply(lambda x: pd.concat([x[col].value_counts() for col in x.columns],axis=1)).fillna(0)
print(my_count)
              1211  2332  3423  4534
2019-07-03 1     1     1     1   1.0
2019-10-03 0     2     1     2   0.0
           1     1     2     1   3.0
user_118842370
Related