Insert row in pandas Dataframe based on Date Column

Question

I have a Dataframe df and a list li, My dataframe column contains:

    Student     Score   Date  
    A             10      15-03-19
    C             11      16-03-19
    A             12      16-03-19
    B             10      16-03-19
    A             9       17-03-19

My list contain Name of all Student li=[A,B,C] If any student have not came on particular day then insert the name of student in dataframe with score value = 0

My Final Dataframe should be like:

    Student   Score   Date
    A         10      15-03-19
    B         0       15-03-19
    C         0       15-03-19
    C         11      16-03-19
    A         12      16-03-19 
    B         10      16-03-19
    A         9       17-03-19
    B         0       17-03-19
    C         0       17-03-19

jezrael · Accepted Answer · 2019-04-10 11:30:39Z

2

Use DataFrame.reindex with MultiIndex.from_product:

li = list('ABC')

mux = pd.MultiIndex.from_product([df['Date'].unique(), li], names=['Date', 'Student'])
df = df.set_index(['Date', 'Student']).reindex(mux, fill_value=0).reset_index()
print (df)
       Date Student  Score
0  15-03-19       A     10
1  15-03-19       B      0
2  15-03-19       C      0
3  16-03-19       A     12
4  16-03-19       B     10
5  16-03-19       C     11
6  17-03-19       A      9
7  17-03-19       B      0
8  17-03-19       C      0

Alternative is use left join with DataFrame.merge and helper DataFrame created by product, last replace missing values by fillna:

from  itertools import product
df1 = pd.DataFrame(list(product(df['Date'].unique(), li)), columns=['Date', 'Student'])
df = df1.merge(df, how='left').fillna(0)
print (df)
       Date Student  Score
0  15-03-19       A   10.0
1  15-03-19       B    0.0
2  15-03-19       C    0.0
3  16-03-19       A   12.0
4  16-03-19       B   10.0
5  16-03-19       C   11.0
6  17-03-19       A    9.0
7  17-03-19       B    0.0
8  17-03-19       C    0.0

edited Apr 10, 2019 at 11:30

answered Apr 10, 2019 at 11:24

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

sourav khanna Over a year ago

Your Alternative worrks fine but the first solution is giving error: Exception: cannot handle a non-unique multi-index!

jezrael Over a year ago

@souravkhanna - Exactly, first solution working only for unique values in index. So please use second solution.

Collectives™ on Stack Overflow

Insert row in pandas Dataframe based on Date Column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related