0
$\begingroup$

I've got one Dataframe like:

id value block
1 a 1
2 a 1
3 a 2
4 a 2
5 b 3
6 c 4

And I want to change the value column to the next value based where the series changes. Like below. The change must be defined by the block column.

id value block
1 a 1
2 a 1
3 b 2
4 b 2
5 c 3
6 None 4

I thought about using shift but there are different lengths of continuity in the value column.

I think this should work somehow by using cumsum to detect the change.

$\endgroup$
0
$\begingroup$

For this particular case in which difference between consecutive block elements is 0 or unitary, you could use diff() and something like this:

import pandas as pd, numpy as np
df = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6], 'block': [1, 1, 2, 2, 3, 4]})
values = ['a', 'b', 'c', 'None']
indexs = np.insert(np.cumsum(np.diff(df.block)), 0, 0)
df['value'] = [values[np.asscalar(x)] for x in indexs]

When value is already part of your dataframe, you could simply replace the values list given above by the following:

df = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6], 'value': ['a', 'a', 'a', 'a', 'b', 'c'], 'block': [1, 1, 2, 2, 3, 4]})
values = np.append(df['value'].unique(), 'None')
indexs = np.insert(np.cumsum(np.diff(df.block)), 0, 0) # unchanged
df['value'] = [values[np.asscalar(x)] for x in indexs] # unchanged

which conducts to the same result.

$\endgroup$
3
  • $\begingroup$ This seems the right direction, but how can I assign the correct position to the values, if they are already part of the df, as shown in the example ? $\endgroup$
    – Felix Z.
    Jan 24 '19 at 12:25
  • $\begingroup$ You could use unique() to extract the unique elements in column value of your df, and replace the list values I used above. Check the second piece of code I added. $\endgroup$
    – datariel
    Jan 24 '19 at 13:39
  • $\begingroup$ Thank you very much this is the perfect answer for the given case, but in fact I was looking for something more adaptable, for example, the values might be repeating like ['a', 'a', 'a', 'a', 'b', 'a'] and the block index does not have to start at 0 ... I should have pointed that out better. $\endgroup$
    – Felix Z.
    Jan 24 '19 at 13:51
0
$\begingroup$

So I figured out a way do produce what I need, but I am not happy with the code and think there should be a way to do it with nice pandas functions. Anyway the code works also if the values are repeating and the block does not have to be continuous.

import pandas as pd, numpy as np

df = pd.DataFrame({'block':[2, 2, 4, 4, 5, 6, 6, 6], 'value': ['a','a','a','a','c','d','d','d']})

#get a list of lists with the value groups
val_groups = []
for g_id, group in df.groupby('block'):
    val_groups.append(group.value.values)

#assign the last value of a group to the next group
new_vals = None
for vals in reversed(val_groups):
    last_val = vals[0]
    vals.fill(new_vals)
    new_vals = last_val

#flatten the values
vals = [item for group in val_groups for item in group]

#assign
df['new_vals'] = vals
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.