Count occurences for each day in pandas dataframe

Question

I have a dataframe with historical weather data from 2008 to 2018 for each day. Like this:

   Date        precipMM    Rain_Type
0 2008-07-01    0.0        No rain
1 2008-07-02    0.0        No rain
2 2008-07-03    0.0        No rain
3 2008-07-04    0.3        Light Rain
4 2008-07-05    1.1        Light Rain
...
5 2018-07-06    0.3        Light Rain
6 2018-07-07    0.3        Light Rain
7 2018-07-08    0.0        No rain
8 2018-07-09    0.0        No rain

I want to get new columns as 'Light Rain', 'No Rain' and etc with percentages of these values (like value_counts) in initial dataframe. So, in my dataframe I have 10 observations for 1 Dec (e.x) and 8 days from them were with 'Light Rain' so percentage for each 1 Dec in dataframe will be 80%. I want smth like this:

   Date        precipMM    Rain_Type.  Light_rain_prct.  No_rain_pct
0 2008-07-01    0.0        Light_rain      80             20
0 2008-07-02    0.0        No rain         30             70
0 2008-07-03    0.0        No rain         50             50

Are there any fast ways to do it? I did the same but only with lots of manipulations (groupby, pivot_table and etc), but it takes time to calculate it, as the dataset is large.

jezrael · Answer 1 · 2018-12-12T10:14:11.153

print (df)
         Date  precipMM   Rain_Type
0  2008-07-01       0.0     No rain
1  2008-07-02       0.0     No rain
2  2008-07-03       0.0     No rain
3  2008-07-01       0.3  Light Rain
4  2008-07-01       1.1  Light Rain
5  2018-07-02       0.3  Light Rain
6  2018-07-07       0.3  Light Rain
7  2018-07-01       0.0     No rain
8  2018-07-02       0.0     No rain

Use value_counts per groups and reshape by unstack, then add_suffix for new column names:

df1 = (df.groupby('Date')['Rain_Type']
         .value_counts(normalize=True)
         .unstack(fill_value=0)
         .add_suffix('_pct'))

Another solution is use crosstab with parameter normalize:

df1 = pd.crosstab(df['Date'], df['Rain_Type'],normalize=0).add_suffix('_pct')

And last join to original:

df2 = df.join(df1 * 100, on='Date')
print (df2)
         Date  precipMM   Rain_Type  Light Rain_pct  No rain_pct
0  2008-07-01       0.0     No rain       66.666667    33.333333
1  2008-07-02       0.0     No rain        0.000000   100.000000
2  2008-07-03       0.0     No rain        0.000000   100.000000
3  2008-07-01       0.3  Light Rain       66.666667    33.333333
4  2008-07-01       1.1  Light Rain       66.666667    33.333333
5  2018-07-02       0.3  Light Rain       50.000000    50.000000
6  2018-07-07       0.3  Light Rain      100.000000     0.000000
7  2018-07-01       0.0     No rain        0.000000   100.000000
8  2018-07-02       0.0     No rain       50.000000    50.000000

yatu · Accepted Answer · 2018-12-12T10:57:22.697

Use pd.crosstab followed by a pd.merge. Using slightly modified dataframe:

print(df)
    Date        precipMM  Rain_Type
0 2008-07-01       0.0     Norain
1 2008-07-01       0.0     Norain
2 2008-07-01       0.0     Norain
3 2008-07-01       0.0  LightRain
4 2008-07-02       0.0     Norain
5 2008-07-03       0.0     Norain
6 2008-07-04       0.3  LightRain
7 2008-07-05       1.1  LightRain

df2 = pd.crosstab(df.Date,df.Rain_Type)
df2 = (df2.div(df2.sum(axis=1), axis='rows') * 100).reset_index()

df.merge(df2, on = 'Date')

   Date         precipMM  Rain_Type  LightRain  Norain
0 2008-07-01       0.0     Norain       25.0    75.0
1 2008-07-01       0.0     Norain       25.0    75.0
2 2008-07-01       0.0     Norain       25.0    75.0
3 2008-07-01       0.0  LightRain       25.0    75.0
4 2008-07-02       0.0     Norain        0.0   100.0
5 2008-07-03       0.0     Norain        0.0   100.0
6 2008-07-04       0.3  LightRain      100.0     0.0
7 2008-07-05       1.1  LightRain      100.0     0.0

Count occurences for each day in pandas dataframe

2 Answers2