CODEWITHSUNDEEP
pythonpandas
Ahmed Haque
Ahmed Haque

Reputation: 7544

Conditionally replace missing data pandas

Say, I have two datasets:

A:

ATime          X       Y      Z
1.2            2      15      2
1.4            3      12      1
1.5            1      10      6
1.6            2       9     10
1.9            1       1      9

B:

Btime         C       D      E
1.2           12      25     12
1.3           13      22     11
1.4           11      20     16
1.7           12      29     12
1.9           11      21     19
2.0           08      10     11
2.4           10      12     15

That I am merging into a dataset that looked like this:

Btime         C       D      E         ATime          X       Y       Z
1.2           12      25     12          1.2           2      15      2         
1.3           13      22     11          1.4           1      10      6
1.4           11      20     16          1.5           2       9     10
1.7           12      29     12          1.6           1       1      9
1.9           11      21     19          1.9           0       0      0  
2.0           08      10     11           0            0       0      0 
2.4           10      12     15           0            0       0      0

Would there be an easy way to replace the two 0s only in Atime with the times (2.0, 2.4) listed in Btime?

...

Right now I'm merging two datasets in pandas using something like this:

a = pd.read_csv("a")
b = pd.read_csv("b")
merged = a.join(b, how="outer")
filled = merged.fillna(0)

But that just gives me the situation with the 0s.

Upvotes: 1

Views: 209

Answers (2)

EdChum
EdChum

Reputation: 394389

Use loc and boolean indexing to select the values and assign back:

In [327]:
# filter just those rows where 'ATime' is 0 and take the corresponding 'Btime' value
merged.loc[merged["ATime"] == 0, "ATime"] = merged["Btime"]
merged
Out[327]:
   ATime  X   Y   Z  Btime   C   D   E
0    1.2  2  15   2    1.2  12  25  12
1    1.4  3  12   1    1.3  13  22  11
2    1.5  1  10   6    1.4  11  20  16
3    1.6  2   9  10    1.7  12  29  12
4    1.9  1   1   9    1.9  11  21  19
5    2.0  0   0   0    2.0   8  10  11
6    2.4  0   0   0    2.4  10  12  15

Upvotes: 0

filmor
filmor

Reputation: 32278

Yes, you can pass a series to fillna:

merged["Atime"] = merged["Atime"].fillna(b.Btime)

Upvotes: 2

Related Questions