In apply step, I explicitly want to ignore a few cells of a dataframe, but not able to do that

CaptainCurious 0
Viewed 26

In my symmetric DataFrame I want to fill in values such that ans.loc[index, column] is the p-value result of two columns (these columns being 'index' and 'column') of separate DataFrame(adf).

My code:

adf = sports_team_performance().head(100)
sports = ['NFL', 'NBA', 'NHL', 'MLB']
ans = pd.DataFrame({k:np.nan for k in sports}, index=sports)

def p_map(row):
   sports = ['NFL', 'NBA', 'NHL', 'MLB']
   for x in sports:
       if row.name == x:
           continue
       else:
           row.loc[row.name, x] = stats.ttest_rel(adf[x], adf[row.name], nan_policy='omit')[1]
           print(row.name, x)
   return row

ans.apply(p_map, axis = 1)

Here adf has around 50 rows with indexes being cities and 4 columns: ['NFL', 'NBA', 'NHL', 'MLB']
and the ans df is a 4x4 nan df initally.

Now I'm using the above function and an using the apply step for above df. My goal is ans['NHL', 'NBA'] should be p-value of NHL & NBA columns of my adf and ans['MLB', 'MLB'] should be NaN The if else statement should ideally take care of that, and I'm using the print in the function to see if it is working well. Yet the entire DataFrame is getting populated.

Output:

Answer

dataframe pandas python
1 Answers

This is happening because row.loc[row.name, x] is a Series. And you are assigning the values of 2 sports to that series:

For example, after the first iteration, we know it continues to the next since row.name and x are 'NFL' == 'NFL'. Then the next iteration, row.name is 'NFL' and x is 'NBA'

enter image description here

row.loc[row.name, x] = stats.ttest_rel(adf[x], adf[row.name], nan_policy='omit')[1] is saying, "take the p-value, and put that in the Series of index row.name and index x (so 'NFL' and 'NBA')"

enter image description here

What you really want is to just put it on index 'NBA', which is x. So to fix it, just lose the row.name in row.loc

Code:

adf = sports_team_performance().head(100)
sports = ['NFL', 'NBA', 'NHL', 'MLB']
ans = pd.DataFrame({k:np.nan for k in sports}, index=sports)

def p_map(row):
   sports = ['NFL', 'NBA', 'NHL', 'MLB']
   for x in sports:
       if row.name == x:
           continue
       else:
           row.loc[x] = stats.ttest_rel(adf[x], adf[row.name], nan_policy='omit')[1]   #<--- change made here
           print(row.name, x)
   return row

ans.apply(p_map, axis = 1)
chitown880
Related