CODEWITHSUNDEEP
pythonpandasdataframedata-processing
mlx
mlx

Reputation: 514

Distribute a row over other rows sharing same key

I have a dataframe that looks like this : 

+------+------------+-------+--------------+
| name |    date    | value | replacement |
+------+------------+-------+--------------+
| A    | 20/11/2016 |    10 | NaN          |
| C    | 20/11/2016 |    8  | [A,B]        |
| B    | 20/11/2016 |    12 | NaN          |
| E    | 25/12/2016 |    16 | NaN          |
| F    | 25/12/2016 |    18 | NaN          |
| D    | 25/12/2016 |    11 | [E,F]        |
+------+------------+-------+--------------+

What I would like to do :
For each row that has a list of names in the column 'replacement', I would like its 'value' to be distributed equally over the rows containing those replacements + for the same date.
For the previous example, the output would look like this : 

+------+------------+-------+------------------+
| name |    date    | value | additional value |
+------+------------+-------+------------------+
| A    | 20/11/2016 |    10 |                4 |
| B    | 20/11/2016 |    12 |                4 |
| A    | 25/12/2016 |    16 |              5.5 |
| B    | 25/12/2016 |    18 |              5.5 |
+------+------------+-------+------------------+

I managed to find a way to perform the distribution directly without creating a new column by splitting those rows and grouping by name + date but 1/ It was too slow + 2/I do need to create that additional column and can't find a way to do so.

Upvotes: 1

Views: 410

Answers (2)

jezrael
jezrael

Reputation: 863166

Idea is create new column by lenghts of replacement lists with Series.str.len and then DataFrame.explode (pandas 0.25+) them to scalars. Divide columns value by new and merge by original with another columns names for add original columns:

df1 = df.assign(new=df['replacement'].str.len()).explode('replacement')
df1['new'] = df1['value'].div(df1['new'])

df1 = df1[['name','date','value']].merge(df1[['replacement','date','new']],
                                    left_on=['name','date'],
                                    right_on=['replacement','date'])
df1['replacement'] = df1.pop('new')
print (df1)
  name        date  value  replacement
0    A  20/11/2016     10          4.0
1    B  20/11/2016     12          4.0
2    A  25/12/2016     16          5.5
3    B  25/12/2016     18          5.5

Similar solution with droping instead selecting:

df1 = df.assign(new=df['replacement'].str.len()).explode('replacement')
df1['new'] = df1['value'].div(df1['new'])

df1 = df1.drop(['replacement','new'],1).merge(df1.drop(['name','value'],1),
                                        left_on=['name','date'],
                                        right_on=['replacement','date'])
df1['replacement'] = df1.pop('new')
print (df1)
  name        date  value  replacement
0    A  20/11/2016     10          4.0
1    B  20/11/2016     12          4.0
2    A  25/12/2016     16          5.5
3    B  25/12/2016     18          5.5

Upvotes: 1

anky
anky

Reputation: 75100

Here is another way using explode (requires pandas 0.25+) with groupby:

m = df[[isinstance(i,list) for i in df.replacement]] #df which has lists in replacement col

g = m.explode('replacement').groupby('date') #explode and groupby by date
#drop indices of m and assign the divided value
final = df.drop(m.index).set_index('date').assign(
      replacement=(g['value'].mean()/g.size())).reset_index() 

         date name  value  replacement
0  20/11/2016    A   10.0          4.0
1  20/11/2016    B   12.0          4.0
2  25/12/2016    A   16.0          5.5
3  25/12/2016    B   18.0          5.5

Upvotes: 0

Related Questions