Creating a DataFrame in a For Loop Produces Nan Error

Hi kim and welcome to StackOverflow. I'll try to explain better than I can.

Question 1

The loop return the result in this format:

dtype: int64
Basque Pelota Year    0

dtype: int64
Aeronautics 0

As you can see Basque Pelota Year 0 contains Year but Aeronautics 0 don't have it.

In the first case the type of years_played is <class 'pandas.core.series.Series'> but in the second case is <class 'numpy.int64'>. That means that in the first case you have to access the value of the series like: years_played['Year'] but in the second case if you try this it will raise the exception IndexError: invalid index to scalar variable. because it is an int64 type and not a series.

In your case would be enough to check the type of years_played and get the value based on the type:

print(sport, years_played['Year'] if isinstance(years_played, Series) else years_played)

Take a look the the reference for the built-in function isinstance(). You can also use the type() function anyway avoid the usage of type() because it simply returns the type of an object whereas, isinstance() returns true if the object argument is an instance of the classinfo argument, or of a (direct, indirect or virtual) subclass thereof.

Final code

Essentially the final code will result in something like this:

import pandas as pd
from pandas.core.series import Series

olympics = pd.read_csv("athlete_events.csv")

sports = olympics.set_index('Sport')
sport_name = olympics['Sport'].unique()

for sport in sport_name:
    years_played = sports.loc[sport, ['Year']].max() - sports.loc[sport, ['Year']].min()
    print(type(years_played)) 
    print(sport, years_played['Year'] if isinstance(years_played, Series) else years_played)

Another way to solve the problem could be to solve the type problem at the start:

import pandas as pd
from pandas.core.series import Series

olympics = pd.read_csv(r"C:\Users\carlo.zanocco\Desktop\archive\athlete_events.csv")

sports = olympics.set_index('Sport')
sport_name = olympics['Sport'].unique()

for sport in sport_name:
    years_played = sports.loc[sport, 'Year'].max() - sports.loc[sport, 'Year'].min()
    print(type(years_played)) 
    print(sport, years_played)

Here I have changed sports.loc[sport, ['Year']].max() - sports.loc[sport, ['Year']].min() with sports.loc[sport, 'Year'].max() - sports.loc[sport, 'Year'].min() so it will return only the value as type <class 'numpy.int64'> and you don't need to check the type when printing the result.

Question 2

The error KeyError: nan means that you are trying to access a Key that doesn't exist. In you case the key nan.

Essentially this exception map the not found key.

Gosh, I can see why that was hard to debug. What's missing from your output screenshot is the last part

Year    80
dtype: int64
Year    52
dtype: int64
...
Year    12
dtype: int64
Year    0
dtype: int64
0

Notice how the last line doesn't follow the pattern of the rest! If you inspect sport and years_played at the end of the loop, nothing appears to be amiss. But if you inspect

• sports.loc[sport[0], ['Year']] and
• sports.loc[sport[-1], ['Year']]

and compare them you'll find out what's happening.

The first is a DataFrame. You'll see it is indexed by an index called Sport, every entry of which is Basketball. Like this:

            Year
Sport           
Basketball  1992
Basketball  2008
Basketball  1952
Basketball  2000
Basketball  1972
...          ...
Basketball  2004
Basketball  1996
Basketball  2004
Basketball  2008
Basketball  2016

[4536 rows x 1 columns]

However, the second is a Series:

Year    1936
Name: Aeronautics, dtype: object

... which is what you get if you select only one row from a DataFrame.

I suspect you kind of know this already - I notice that you wrapped 'Year' in a list, which ensures you get a DataFrame back in the first case. Only one column is called Year, so if you don't do this you'll get back a Series:

sports.loc[sport, 'Year'] ->

Sport
Basketball    1992
Basketball    2008
Basketball    1952
Basketball    2000
Basketball    1972
              ... 
Basketball    2004
Basketball    1996
Basketball    2004
Basketball    2008
Basketball    2016
Name: Year, Length: 4536, dtype: int64

(spot the difference with the previous output)

Now, if you do sports.loc["Aeronautics", "Year"] you just get back an integer, 1936, because you've selected a single cell. This then would lead to an error, because an int doesn't have a max and min method, which your code is expecting.

One solution is to force it to always come back with a DataFrame. You can do that like this ...

for sport in sport_name:
    data = sports.loc[[sport], ['Year']]
    years_played = data.max() - data.min()
    print(sport, years_played)

... but that just makes the real reason for your problem apparent, rather than solving it. Now data.max() - data.min() comes back with

Year    0
dtype: int64

This is a pandas series with one entry. The index of the Series is "Year" (because that was the column name of the DataFrame data created above). That's why you were getting the strange output. The actual fix is simple - simply select the single value, now that we've ensured we'll always get a series:

for sport in sport_name:
    data = sports.loc[[sport], ['Year']]
    years_played = data.max() - data.min()
    print(sport, years_played[0])

The second bit of code you posted will never work:

for sport in sport_name:
    years_played = sports.loc[sport, ['Year']].max() - sports.loc[sport, ['Year']].min()
    rows = []
    rows = rows.append([sport, years_played])
    pd.DataFrame(rows, columns = ['Sport', 'Years Played'])

The last line creates a new DataFrame from the single row you've put into the list but doesn't assign it to anything. However, in my case it also doesn't throw an error, so I suspect my version of pandas (v1.1.0) has different behaviour to yours. Regardless, the issue will be the same I'm sure: that years_played is either a Series or an int depending on whether there were multiple years in which the sport was played or not.