I have a dataset that has two columns: company, and value.
It has a datetime index, which contains duplicates (on the same day, different companies have different values). The values have missing data, so I want to forward fill the missing data with the previous datapoint from the same company.
However, I can't seem to find a good way to do this without running into odd groupby errors, suggesting that I'm doing something wrong.
Toy data:
a = pd.DataFrame({'a': [1, 2, None], 'b': [12,None,14]})
a.index = pd.DatetimeIndex(['2010', '2011', '2012'])
a = a.unstack()
a = a.reset_index().set_index('level_1')
a.columns = ['company', 'value']
a.sort_index(inplace=True)
Attempted solutions (didn't work: ValueError: cannot reindex from a duplicate axis
):
a.groupby('company').ffill()
a.groupby('company')['value'].ffill()
a.groupby('company').fillna(method='ffill')
Hacky solution (that delivers the desired result, but is obviously just an ugly workaround):
a['value'] = a.reset_index().groupby(
'company').fillna(method='ffill')['value'].values
There is probably a simple and elegant way to do this, how is this performed in Pandas?
One way is to use the transform
function to fill the value
column after group by:
import pandas as pd
a['value'] = a.groupby('company')['value'].transform(lambda v: v.ffill())
a
# company value
#level_1
#2010-01-01 a 1.0
#2010-01-01 b 12.0
#2011-01-01 a 2.0
#2011-01-01 b 12.0
#2012-01-01 a 2.0
#2012-01-01 b 14.0
To compare, the original data frame looks like:
# company value
#level_1
#2010-01-01 a 1.0
#2010-01-01 b 12.0
#2011-01-01 a 2.0
#2011-01-01 b NaN
#2012-01-01 a NaN
#2012-01-01 b 14.0
You can add 'company'
to the index, making it unique, and do a simple ffill
via groupby
:
a = a.set_index('company', append=True)
a = a.groupby(level=1).ffill()
From here, you can use reset_index
to revert the index back to the just the date, if necessary. I'd recommend keeping 'company'
as part of the the index (or just adding it to the index to begin with), so your index remains unique:
a = a.reset_index(level=1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With