4

Let's assume that I have the following data-frame:

df_raw = pd.DataFrame({"id": [102, 102, 103, 103, 103], "val1": [9,2,4,7,6], "val2": [np.nan, 3, np.nan, 4, 5], "val3": [4, np.nan, np.nan, 5, 1], "date": [pd.Timestamp(2002, 1, 1), pd.Timestamp(2002, 3, 3), pd.Timestamp(2003, 4, 4), pd.Timestamp(2003, 8, 9), pd.Timestamp(2005, 2, 3)]})

I want to have access to the rows where the first occurrence of each id is. So these rows would be:

df_first = pd.DataFrame({"id": [102, 103], "val1": [9, 4], "val2": [np.nan, np.nan], "val3": [4, np.nan], "date": [pd.Timestamp(2002, 1, 1), pd.Timestamp(2003, 4, 4)]})

Basically, at the end what I would like to achieve is fill up the NaNs that appear in the first occurrence of each id. So the final data frame might be:

df_processed = pd.DataFrame({"id": [102, 102, 103, 103, 103], "val1": [9,2,4,7,6], "val2": [-1, 3, -1, 4, 5], "val3": [4, np.nan, -1, 5, 1], "date": [pd.Timestamp(2002, 1, 1), pd.Timestamp(2002, 3, 3), pd.Timestamp(2003, 4, 4), pd.Timestamp(2003, 8, 9), pd.Timestamp(2005, 2, 3)]})

An important note is that the rows are already grouped by id and date and sorted in a ascending manner. So they appear exactly as in the provided example.

U13-Forward
  • 69,221
  • 14
  • 89
  • 114
gorjan
  • 5,405
  • 2
  • 20
  • 40

2 Answers2

2

IIUC using drop_duplicates then concat

df1=df_raw.drop_duplicates('id').fillna(-1)
target=pd.concat([df1,df_raw.loc[~df_raw.index.isin(df1.index)]]).sort_index()
target
        date   id  val1  val2  val3
0 2002-01-01  102     9  -1.0   4.0
1 2002-03-03  102     2   3.0   NaN
2 2003-04-04  103     4  -1.0  -1.0
3 2003-08-09  103     7   4.0   5.0
4 2005-02-03  103     6   5.0   1.0
BENY
  • 317,841
  • 20
  • 164
  • 234
2

You can use pd.Series.duplicated with Boolean row indexing:

mask = ~df_raw['id'].duplicated()
val_cols = ['val2', 'val3']

df_raw.loc[mask, val_cols] = df_raw.loc[mask, val_cols].fillna(-1)

print(df_raw)

    id  val1  val2  val3       date
0  102     9  -1.0   4.0 2002-01-01
1  102     2   3.0   NaN 2002-03-03
2  103     4  -1.0  -1.0 2003-04-04
3  103     7   4.0   5.0 2003-08-09
4  103     6   5.0   1.0 2005-02-03
jpp
  • 159,742
  • 34
  • 281
  • 339