Generate a list (including missing values) from a Pandas data frame column

Question

Here's the simple Pandas data frame I'm using:

>>> df2
    Name  Day  Score
0  Allen    1     25
5  Allen    3      9

What I'm trying to do is generate a list of Allen's scores, and also signal days when there is no score for all days from 1 to 5. Here's what I've been able to do:

>>> [df2[df2['Day']==i]['Score'].values[0] if i in list(df2['Day']) else None for i in range(1,6)]
[25, None, 9, None, None]

Surely there's a less clunky way to do this? What is it?

On a related note, I'd like to do this for each person in a larger data frame. What's the best way to do that? For now all I can think of is using apply with the expression above, but again that seems clunkier than this should have to be. Thanks for any suggestions!

JohnE · Accepted Answer · 2016-03-12 16:46:11Z

2

I would start by setting the index to 'Day' and re-indexing:

>>> df2 = df2.set_index('Day').reindex(range(1,6))

      Name  Score
Day              
1    Allen     25
2      NaN    NaN
3    Allen      9
4      NaN    NaN
5      NaN    NaN

From there you have lots of options for proceding further. If you just want a list:

>>> df2['Score'].tolist()

[25.0, nan, 9.0, nan, nan]

You may want to look at fillna() for different options on specifying the missing value.

answered Mar 12, 2016 at 16:46

JohnE

30.7k9 gold badges86 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Monica Heddneck Over a year ago

I don't understand what role reindexing played?

JohnE Over a year ago

@MonicaHeddneck changes index from [0,5] to [1,2,3,4,5]. Look at the OP's original data.

Monica Heddneck Over a year ago

Sure, but why is resetting an index necessary?

JohnE Over a year ago

@MonicaHeddneck It's not necessary in and of itself, it's just an intermediate step I used to get the answer here. If you have an alternative approach that you like better I would sincerely encourage you to post it. This was merely the best I could come up with at the time and I haven't looked at this since I originally posted it (so my overall memory of this is quite hazy)

Collectives™ on Stack Overflow

Generate a list (including missing values) from a Pandas data frame column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related