Creating a DataFrame from a list based on specific values in that list

Question

I am helping my wife out with some work for her primary school.

I have an excel looking like this simplified:

              score
NameA
test1           10
test2           23
test4           15
NameB
test1           10
test3           17
NameC
etc. 

What I would (ultimately) want is:

           Test1.   Test2.   Test3.   Test4.   etc
NameA.       10       23      Nan       Nan
NameB        10       Nan.     17.      Nan
NameC       
etc

So far I have imported the Excel with with all columns cocatenated, thinking to make a list of lists for all test of a given pupil, but cannot get it to work.

Looking for inspiration so all suggestions are welcome.

Maarten

YOLO · Accepted Answer · 2018-05-07 12:40:59Z

1

I would suggest the following:

1.Re-structure your data in excel in this format:

   Names   test  score
0  NameA  test1     10
1  NameA  test2     23
2  NameA  test4     15
3  NameB  test1     10
4  NameB  test3     17

2.Then, a simple unstack command will go the magic.

df = df.set_index(['Names','test'])['score'].unstack(-1)
df.index.name = None
df.columns.name = None

print(df)
        test1  test2  test3  test4
NameA   10.0   23.0    NaN   15.0
NameB   10.0    NaN   17.0    NaN

answered May 7, 2018 at 12:40

YOLO

22k5 gold badges25 silver badges42 bronze badges

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

Comments

BENY · Accepted Answer · 2018-05-07 13:43:14Z

0

You can just change reformat your df in pandas

df=df.reset_index()
df['New']=df.A.loc[df.score=='']

df.ffill().loc[df.score!=''].pivot('New','A','score')
Out[406]: 
A     test1 test2 test3 test4
New                          
NameA    10    23  None    15
NameB    10  None    17  None

answered May 7, 2018 at 13:43

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

Maartenk Over a year ago

The forward fill is a suggestion I had not considered...it works! When I do further Boolean masking (I grossly simplified the complexity) it complains about the NaN's, so end up going back and forth with Nan's and other values; I can, of course forward fill with some other value...coming to think of it. Or would you have any other suggestion?

Collectives™ on Stack Overflow

Creating a DataFrame from a list based on specific values in that list

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related