Insert rows in Pandas dataframe after comparing with list

user_8151881 0
Viewed 159

I have a pandas dataframe which has four columns. Following is an example of the pandas dataframe:

import pandas as pd 

data = {"Name" : ['A1', 'A1', 'A1', 'A1'], "String1" : ["B1", "B2", "B6", "B7"] , "Values1" : [5, 12, 21, 99], "Values2" : [50, 120, 210, 990] } 

df = pd.DataFrame(data)

print( df )

   Name String1  Values1  Values2
0   A1      B1        5       50
1   A1      B2       12      120
2   A1      B6       21      210
3   A1      B7       99      990

One of the columns, i.e. Name has constant entries only, while two other columns, Values1 and Values2 have numerical values.

I have a list (say String2) which contains all elements of the column String1 and some additional elements.

An example of String2 is the following:

String2 = [ "B1", "B2", "B3", "B4" , "B5", "B6", "B7" ]

I want to find insert all elements which are in String2 and not in String1 (i.e. "B3", "B4" , "B5") in the column String1 in the Pandas dataframe in separate rows. For all these rows where new elements have been inserted in String1, I want to put Null in columns Value1 and Value2. In the constant column (Name), I want to keep the same constant entry (i.e. A1).

In other words, following is how I want the new dataframe to be like:

   Name String1  Values1  Values2
0   A1      B1        5       50
1   A1      B2       12      120
2   A1      B3       Null    Null
3   A1      B4       Null    Null
4   A1      B5       Null    Null
5   A1      B6       21      210
6   A1      B7       99      990

How can I do this using python and pandas?

pandas dataframe numpy python
3 Answers

Create new DataFrame with product for all combinations of unique values of Name and then use left join in DataFrame.merge, there is no on parameter, so joined by intersection of columns names in both df, here Name, String1 columns:

from  itertools import product

df1 = pd.DataFrame(list(product(df['Name'].unique(), String2)), columns=['Name','String1'])
df2 = df1.merge(df, how='left')
print (df2)
  Name String1  Values1  Values2
0   A1      B1      5.0     50.0
1   A1      B2     12.0    120.0
2   A1      B3      NaN      NaN
3   A1      B4      NaN      NaN
4   A1      B5      NaN      NaN
5   A1      B6     21.0    210.0
6   A1      B7     99.0    990.0

Or create MultiIndex by MultiIndex.from_product and use DataFrame.reindex:

mux = pd.MultiIndex.from_product([df['Name'].unique(), String2], names=['Name','String1'])

df2 = df.set_index(['Name','String1']).reindex(mux).reset_index()
print (df2)
  Name String1  Values1  Values2
0   A1      B1      5.0     50.0
1   A1      B2     12.0    120.0
2   A1      B3      NaN      NaN
3   A1      B4      NaN      NaN
4   A1      B5      NaN      NaN
5   A1      B6     21.0    210.0
6   A1      B7     99.0    990.0
user_29010020

You can temporarily set String1 as the index, reindex and reset back to previous form : 

#hold the current column composition
cols = df.columns

(df 
.set_index("String1")
.reindex(String2)
.reset_index()
 #get columns into previous form
.reindex(cols,axis=1)
 #since Name column is constant we can 
 #forward fill on the Name column
.assign(Name=lambda x: x.Name.ffill())
 )


   Name String1 Values1 Values2
0   A1  B1       5.0    50.0
1   A1  B2      12.0    120.0
2   A1  B3      NaN     NaN
3   A1  B4      NaN     NaN
4   A1  B5      NaN     NaN
5   A1  B6      21.0    210.0
6   A1  B7      99.0    990.0
user_71757130

Similar to sammywemmy's answer, but slightly different:

>>> df.drop('Name', 1).set_index('String1').reindex(String2).reset_index().assign(Name=df.loc[0, 'Name'])[df.columns]
  Name String1  Values1  Values2
0   A1      B1      5.0     50.0
1   A1      B2     12.0    120.0
2   A1      B3      NaN      NaN
3   A1      B4      NaN      NaN
4   A1      B5      NaN      NaN
5   A1      B6     21.0    210.0
6   A1      B7     99.0    990.0
user_36200030
Related