Calculate non null values in a pandas dataframe

Question

I am trying to parse data into a DataFrame. I want to print out dataframe that contains the list of countries in a region and the non null count.

inputYear = int(input('Enter year period (1: 1978-1987, 2: 1988-1997, 3: 1998-2007, 4: 2008-2017): '))

if inputYear > 4 or inputYear < 1:
  exit()

inputRegion = int(input('Enter region (1: Asia, 2: Europe, 3: Others): '))

if inputRegion > 3 or inputRegion < 1:
  exit()

def parseData(inputYear, inputRegion):
  print('*** Parsed Data (Regions) ***')
  countries = pd.read_csv('/content/drive/My Drive/Int_Monthly_Visitor.csv')
  cols = [e.strip() for e in list(countries.columns)]
  regions = {
      'Asia': cols[1:19],
      'Europe': cols[19:30],
      'Others': cols[30:]
  }

  if inputYear == 1:
    year1 = 0
    year2 = 120
  elif inputYear == 2:
    year1 = 120
    year2 = 240
  elif inputYear == 3:
    year1 = 241
    year2 = 360
  elif inputYear == 4:
    year1 = 360
    year2 = 480

  if inputRegion == 1:
    regionlist = pd.DataFrame({'Columns': regions['Asia'], 'Non-Null count': countries.count(regions['Asia'].iloc[year1:year2])})
  elif inputRegion == 2:
    regionlist = pd.DataFrame({'Columns': regions['Europe'], 'Non-Null count': countries.count(regions['Europe'].iloc[year1:year2])})
  elif inputRegion == 3:
    regionlist = pd.DataFrame({'Columns': regions['Others'], 'Non-Null count': countries.count(regions['Ote=hers'].iloc[year1:year2])})

  print(regionlist)

parseData(inputYear, inputRegion)

What my output is (Note: I tried everything to make this work but couldn't):

AttributeError: 'list' object has no attribute 'iloc'

For example, if the user enters 1 for the inputYear variable, it would look up the years 1978 - 1987 (10 years). The variables under the if operator of inputYear is indexes. For example putting this in iloc function would select the cells between year1 and year2 and count the non null values. The inputRegion function would select the columns depending on the region, so for this I wanted a solution that prints out the country, and how many non null values does it have over a period of 10 years.

What the expected output should be (If the user selects year 1: as period between 1978-1987 and region as 3: Others):

Enter year period (1: 1978-1987, 2: 1988-1997, 3: 1998-2007, 4: 2008-2017): 1
Enter region (1: Asia, 2: Europe, 3: Others): 3

*** Parsed Data (Regions) ***
    Columns      Non-Null Count
0   USA          120 non-null
1   Canada       120 non-null
2   Australia    120 non-null
3   New Zealand  120 non-null
4   Africa       120 non-null

This sounds a little confusing at first, but to put it simply, basically I wanted to print out a DataFrame that shows a list of countries in a region, each country listed and how many non-null values it has for that country. Non null value is counted through a specific 10 year period of a country. I tried to make it work but in the end i could not.

user_19261326 · Answer

You could use .loc to achieve this.

Provide the range of rows you need to slice, and the column labels neeeded.

# Returns a pandas dataframe with data of interest
countries.loc[year1:year2, regions['Asia']]

To get count of non-nulls simply use .count() on this dataframe:

countries.loc[year1:year2, regions['Asia']].count()

So, the code should look like:

if inputRegion == 1:
    regionlist = pd.DataFrame({'Columns': regions['Asia'], 'Non-Null count': countries.loc[year1:year2, regions['Asia']].count()})
elif inputRegion == 2:
    regionlist = pd.DataFrame({'Columns': regions['Europe'], 'Non-Null count': countries.loc[year1:year2, regions['Europe']].count()})
elif inputRegion == 3:
    regionlist = pd.DataFrame({'Columns': regions['Others'], 'Non-Null count': countries.loc[year1:year2, regions['Others']].count()})

Calculate non null values in a pandas dataframe

1 Answers