How can I replace DataFrame values from List values? -python

Question

I have a list and a dataframe. f.e.:

List1=[1, 4, 6, 15]

DataFrame:

A              B         C         
Daisy          5        -1
Sunflower      3         5
Tulip          15        5
Orchid         8         6
Lotus          23       -1
Poppy          9         6
Lily           40       -1

I want to replace the values of B, where C>0 with the values of the List1. So it would look like this:

A              B         C        D
Daisy          5        -1        5
Sunflower      3         8        1
Tulip          15        5        4
Orchid         8         7        6
Lotus          23       -1        23
Poppy          9         6        15
Lily           40       -1        40

I tried this code using pandas and sqlite3:

Table1 = pd.read_excel(Flowers)
Table1["id2"]=range(1, len(Table1)+1)
List1=[1, 4, 6, 15]
indexes=[]

conn = sqlite3.connect('Table1.db')
crsr = conn.cursor()
conn.commit()

Table1.to_sql('Table1', conn, if_exists='replace', index= False)


crsr.execute("SELECT id2 FROM Table1 WHERE C BETWEEN 0 and 20")
for row in crsr.fetchall():
   indexes.extend(row)


e=0

for i in indexes:

   Table1.loc[i-1,Table1.C[i-1]] = List1[e]
   if e+1>len(List1):
       break
   else:
       e=e+1
print(Table1)

And get this result:

           A   B     C  id2    8    5    7     6
0      Daisy   5    -1    1  NaN  NaN  NaN   NaN
1  Sunflower   3     8    2  1.0  NaN  NaN   NaN
2      Tulip  15     5    3  NaN  4.0  NaN   NaN
3     Orchid   8     7    4  NaN  NaN  6.0   NaN
4      Lotus  23    -1    5  NaN  NaN  NaN   NaN
5      Poppy   9     6    6  NaN  NaN  NaN  15.0
6       Lily  40    -1    7  NaN  NaN  NaN   NaN

How can I replace the values in column B with the List elements? Thank you for the help!

Answer 1

If aways length of list is same like number of values matched condition use:

df['D'] = df['B']
df.loc[df.C > 0, 'D'] = List1
print (df)
           A   B  C   D
0      Daisy   5 -1   5
1  Sunflower   3  5   1
2      Tulip  15  5   4
3     Orchid   8  6   6
4      Lotus  23 -1  23
5      Poppy   9  6  15
6       Lily  40 -1  40

Or:

df.loc[df.C > 0, 'D'] = List1
df['D'] = df['D'].fillna(df['B'])
print (df)
           A   B  C     D
0      Daisy   5 -1   5.0
1  Sunflower   3  5   1.0
2      Tulip  15  5   4.0
3     Orchid   8  6   6.0
4      Lotus  23 -1  23.0
5      Poppy   9  6  15.0
6       Lily  40 -1  40.0

General solution with itertools.zip_longest also working if lengths not match with length of Trues in mask:

from itertools import zip_longest

List1=[1, 4, 6,15,3]

m = df.C > 0
lens = m.sum()
df['D'] = df['B']
df.loc[m, 'D'] = [x for x, y in zip_longest(List1[:lens], range(lens), fillvalue=np.nan)]
print (df)
           A   B  C   D
0      Daisy   5 -1   5
1  Sunflower   3  5   1
2      Tulip  15  5   4
3     Orchid   8  6   6
4      Lotus  23 -1  23
5      Poppy   9  6  15
6       Lily  40 -1  40

---

from itertools import zip_longest

List1=[1, 4]

m = df.C > 0
lens = m.sum()
df['D'] = df['B']
df.loc[m, 'D'] = [x for x, y in zip_longest(List1[:lens], range(lens), fillvalue=np.nan)]
print (df)
           A   B  C     D
0      Daisy   5 -1   5.0
1  Sunflower   3  5   1.0
2      Tulip  15  5   4.0
3     Orchid   8  6   NaN
4      Lotus  23 -1  23.0
5      Poppy   9  6   NaN
6       Lily  40 -1  40.0