1

Say I have the following dataframe,

ID  StationID  Date         ParamName  ParamValue
0   A          1990-01-08   metal      0.5
1   A          1990-01-08   wood       1.4
2   A          1990-01-08   glass      9.7
3   B          1990-01-08   metal      0.8
4   B          1990-01-08   wood       4.8
5   C          1990-01-08   metal      0.6
6   A          1990-02-03   metal      0.5
7   A          1990-03-01   metal      1.2
8   B          1990-03-01   metal      0.9
9   C          1990-03-01   metal      1.1

How would you reindex the ID column of the dataframe to increment only when the date or StationID are different. How can the above dataframe be reindexed to the dataframe below (assume the date contains datetime objects)?

ID  StationID  Date         ParamName  ParamValue
0   A          1990-01-08   metal      0.5
0   A          1990-01-08   wood       1.4
0   A          1990-01-08   glass      9.7
1   B          1990-01-08   metal      0.8
1   B          1990-01-08   wood       4.8
2   C          1990-01-08   metal      0.6
3   A          1990-02-03   metal      0.5
4   A          1990-03-01   metal      1.2
5   B          1990-03-01   metal      0.9
6   C          1990-03-01   metal      1.1
Char
  • 1,635
  • 7
  • 27
  • 38

1 Answers1

2

Is this what you need ? 

df.assign(ID=(df.StationID!=df.StationID.shift()).cumsum()-1)
Out[151]: 
   ID StationID        Date ParamName  ParamValue
0   0         A  1990-01-08     metal         0.5
1   0         A  1990-01-08      wood         1.4
2   0         A  1990-01-08     glass         9.7
3   1         B  1990-01-08     metal         0.8
4   1         B  1990-01-08      wood         4.8
5   2         C  1990-01-08     metal         0.6
6   3         A  1990-02-03     metal         0.5
7   3         A  1990-02-03      wood         1.2
8   4         B  1990-02-03     metal         0.9
9   5         C  1990-02-03     metal         1.1

Update :-) 

df['ID']=df.StationID+df.Date.astype(str)
df.assign(ID=(df.ID!=df.ID.shift()).cumsum()-1)
Out[163]: 
   ID StationID        Date ParamName  ParamValue
0   0         A  1990-01-08     metal         0.5
1   0         A  1990-01-08      wood         1.4
2   0         A  1990-01-08     glass         9.7
3   1         B  1990-01-08     metal         0.8
4   1         B  1990-01-08      wood         4.8
5   2         C  1990-01-08     metal         0.6
6   3         A  1990-02-03     metal         0.5
7   4         A  1990-03-01     metal         1.2
8   5         B  1990-03-01     metal         0.9
9   6         C  1990-03-01     metal         1.1
BENY
  • 317,841
  • 20
  • 164
  • 234