CODEWITHSUNDEEP
pythonpandas
C. Cooney
C. Cooney

Reputation: 591

How to populate a dataframe list with missing values

I have a dataframe with the following:

colA  colB
ABC   0.12
GHI   0.01

And a unique list for which I want to create a dataframe with:

ABC
DEF
GHI

The dataframe I need to create would have:

colA   colB
ABC    0.12
DEF    0.00
GHI    0.01

What would be the fastest way to populate my new dataframe (i.e. my intution would be to loop).

Upvotes: 0

Views: 91

Answers (3)

wwnde
wwnde

Reputation: 26676

Another way is to pd.Series the list, append to existing dataframe, and drop duplicates;

df.append(pd.DataFrame(l,columns=['colA'])).drop_duplicates(subset=['colA'], keep='first').fillna(0)

 colA  colB
0  ABC  0.12
1  DEF  0.01
2  GHI  0.00

Upvotes: 0

David Erickson
David Erickson

Reputation: 16673

You could use .combine_first if you create a second dataframe from the list and use .set_index('colA') for both dataframes:

df1 = pd.DataFrame({'colA': {0: 'ABC', 1: 'GHI'}, 'colB': {0: 0.12, 1: 0.01}})
lst = ['ABC','DEF','GHI']
df2 = pd.DataFrame({'colA' : lst})
df3 = df1.set_index('colA').combine_first(df2.set_index('colA')).reset_index().fillna(0)
df3
Out[1]: 
  colA  colB
0  ABC  0.12
1  DEF  0.00
2  GHI  0.01

You could use .combine_first if you create a second dataframe from the list and use .set_index('colA') for both dataframes:

df1 = pd.DataFrame({'colA': {0: 'ABC', 1: 'GHI'}, 'colB': {0: 0.12, 1: 0.01}})
lst = ['ABC','DEF','GHI']
df2 = pd.DataFrame({'colA' : lst})
df3 = df1.set_index('colA').combine_first(df2.set_index('colA')).reset_index().fillna(0)
df3
Out[1]: 
  colA  colB
0  ABC  0.12
1  DEF  0.00
2  GHI  0.01

I was curious to see which method was faster between combine_first and reindex. Sammy's approach was faster at least for this dataframe.

df1 = pd.DataFrame({'colA': {0: 'ABC', 1: 'GHI'}, 'colB': {0: 0.12, 1: 0.01}}).set_index('colA')
lst = ['ABC','DEF','GHI']
df2 = pd.DataFrame({'colA' : lst}).set_index('colA')

def f1(): 
    return df1.combine_first(df2).reset_index().fillna(0)


def f2(): 
    return df1.reindex(lst, fill_value=0).reset_index()

%timeit f1()
%timeit f2()

2.35 ms ± 140 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
784 µs ± 25 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28649

Try this:

df.set_index("colA").reindex(["ABC", "DEF", "GHI"], fill_value=0).reset_index()



   colA colB
0   ABC 0.12
1   DEF 0.00
2   GHI 0.01

Upvotes: 1

Related Questions