CODEWITHSUNDEEP
pythonpandas
Jake Wagner
Jake Wagner

Reputation: 826

Count a certain value for each country

I am attempting to do a Excel countif function with pandas but hitting a roadblock in doing so.

I have this dataframe. I need to count the YES for each country quarter-wise. I have posted the requested answers below.

result.head(3)

Country    Jan 1    Feb 1   Mar 1   Apr 1   May 1   Jun 1       Quarter_1         Quarter_2 
FRANCE     Yes      Yes      No      No     No       No            2                  0
BELGIUM    Yes      Yes      No     Yes     No       No            2                  1
CANADA     Yes      No       No     Yes     No       No            1                  1

I tried the following but Pandas spats out a total value instead showing a 5 for all the values under Quarter_1. I am oblivious on how to calculate my function below by Country? Any assistance with this please!

result['Quarter_1'] = len(result[result['Jan 1'] == 'Yes']) + len(result[result['Feb 1'] == 'Yes'])
+ len(result[result['Mar 1'] == 'Yes'])

Upvotes: 1

Views: 446

Answers (1)

Erfan
Erfan

Reputation: 42946

We can use the length of your column and take the floor division to create your quarters. Then we groupby on these and take the sum.

Finally to we add the prefix Quarter:

df = df.set_index('Country')
grps = np.arange(len(df.columns)) // 3
dfn = (
    df.join(df.eq('Yes')
            .groupby(grps, axis=1)
            .sum()
            .astype(int)
            .add_prefix('Quarter_'))
    .reset_index()
)

Or using list comprehension to rename your columns:

df = df.set_index('Country')
grps = np.arange(len(df.columns)) // 3

dfn = df.eq('Yes').groupby(grps, axis=1).sum().astype(int)
dfn.columns = [f'Quarter_{col+1}' for col in dfn.columns]

df = df.join(dfn).reset_index()

   Country Jan 1 Feb 1 Mar 1 Apr 1 May 1 Jun 1  Quarter_1  Quarter_2
0   FRANCE   Yes   Yes    No    No    No    No          2          0
1  BELGIUM   Yes   Yes    No   Yes    No    No          2          1
2   CANADA   Yes    No    No   Yes    No    No          1          1

Upvotes: 3

Related Questions