1

I have a dataframe like this:

Corp TDS VVT SOAR
Steam 3429.0 450
Steam 1365
Bliz 3425.1 11
Bliz 353.3 34
Tesla 18
Tesla 2243.3 32
OYV 15
OYV 16

What is the best way to replace emptiness in the columns with the same value from another row if the Corp value is equal?

The result should be like this:

Corp TDS VVT SOAR
Steam 3429.0 450
Steam 3429.0 1365
Bliz 3425.1 34 11
Bliz 353.3 34 11
Tesla 2243.3 18
Tesla 2243.3 32
OYV 15 16
OYV 15 16
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Anna Shevtsova
  • 125
  • 7
  • Will there always be two rows per Corp? If that's not the case, how do you handle what to do when you have multiple values in a column to fill another row? – aaossa Feb 24 '22 at 11:52
  • What if there are to different values in other rows for the same Corp, how do you decide which you take to replace an empty value? – sunnytown Feb 24 '22 at 11:52
  • Does this answer your question? [Pandas fillna using groupby](https://stackoverflow.com/questions/46391128/pandas-fillna-using-groupby) – aaossa Feb 24 '22 at 11:57
  • @aaossa now this is always two rows per Corp :) – Anna Shevtsova Feb 24 '22 at 11:58

1 Answers1

3

We could use GroupBy.apply with ffill and bfill:

df.groupby('Corp').apply(lambda x: x.ffill().bfill())

    Corp     TDS     VVT  SOAR
0  Steam  3429.0   450.0   NaN
1  Steam  3429.0  1365.0   NaN
2   Bliz  3425.1    34.0  11.0
3   Bliz   353.3    34.0  11.0
4  Tesla  2243.3     NaN  18.0
5  Tesla  2243.3     NaN  32.0
6    OYV     NaN    15.0  16.0
7    OYV     NaN    15.0  16.0

If you have blanks:

df.replace(r'^\s*$', np.nan, regex=True)\
  .groupby('Corp').apply(lambda x: x.ffill().bfill())
ansev
  • 30,322
  • 5
  • 17
  • 31