Adding a time column to a DataFrame based on another DataFrame

Question

I have DataFrame DataA, whose rows represent the value of an item

DataA
row  item_id  value
0    x        V1
1    y        V2
2    z        V3
3    y        V4
4    z        V5
5    x        V6
6    y        V7
7    z        V8
8    z        V9

Furthermore there is another DataFrame called DataA_mapper that maps a time value to a sequence of rows in DataA

DataA_mapper
time  start_row  num_rows
0     0          3
1     3          2
3     5          2
5     8          1

For a given row in DataA_mapper the rows in DataA for the range [start_row, start_row + num_row) will all be given DataA_mapper.time.

DF definition in python:

dataA = [
    [x, 'V1'], [y, 'V2'], [z, 'V3'], [y, 'V4'],
    [z, 'V5'], [x, 'V6'], [y, 'V7'], [z, 'V8'], [z, 'V9']]


DataA_mapper = [[0, 0, 3], [1, 3, 2], [3, 5, 2], [5, 8, 1]]


dataA_df = pd.DataFrame(dataA, columns = ['item_id', 'value'])
DataA_mapper_df = pd.DataFrame(DataA_mapper, columns = ['time', 'start_row', 'num_rows'])

I would like to generate the following DataFrame, however I'm not sure where to begin:

time  item_id   value
0     x         V1
0     y         V2
0     z         V3
1     y         V4
1     z         V5
3     x         V6
3     y         V7
5     z         V9

ansev · Accepted Answer · 2020-01-21 18:01:10Z

10

I think you need Series.repeat.

dataA_df.index = DataA_mapper_df.time.repeat(DataA_mapper_df.num_rows)
dataA_df = dataA_df.reset_index()
print(dataA_df)

Output

   time item_id value
0     0       x    V1
1     0       y    V2
2     0       z    V3
3     1       y    V4
4     1       z    V5
5     3       x    V6
6     3       y    V7
7     3       z    V8
8     5       z    V9

answered Jan 21, 2020 at 18:01

ansev

31k5 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lucinda Rigetti Over a year ago

This is an excellent answer! Though I made 1 minor change, if there is a row in DataA (eg: row 7) that is not covered by any of the ranges defined in DataA_mapper_df that it should not be present in the final DF - would you happen to know how that could be done?

Collectives™ on Stack Overflow

Adding a time column to a DataFrame based on another DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related