reproduce/break rows based on field value

user_9437787 0
Viewed 53

I have my dataframe as:

id        date     value
1         2016     3
2         2016     1
1         2018     1
1         2016     1.1

Now I want to reproduce rows for some weird reason with logic as:

if value > 1
    reproduce row value times - 1
    with value = 1
    or fraction left for last unit

for better understanding consider only 1st row of dataframe i.e. :

id        date     value
1         2016     3

which I have broken down into 3 rows as:

id        date     value
1         2016     1
1         2016     1
1         2016     1

but consider last row i.e.:

id        date     value
1         2016     1.1

Which is broken as:

id        date     value
1         2016     1
1         2016     0.1

i.e. if any fraction is there, this is broken separately, else in one unit

and then group by id and sort by date is obviously easy.

i.e. new dataframe will look like:

id        date     value
1         2016     1
1         2016     1
1         2016     1
1         2016     1
1         2016     0.1
1         2018     1
2         2016     1

The main problem is reproducing rows.

UPDATED

A sample dataframe code:

df = pd.DataFrame([[1,2018,5.1],[2,2018,2],[1,2016,1]], columns=["id", "date", "value"])
pandas-groupby pandas python-3.x
2 Answers

generator

def f(df):
  for i, *t, v in df.itertuples():
    while v > 0:
      yield t + [min(v, 1)]
      v -= 1

pd.DataFrame([*f(df)], columns=df.columns)

   id  date  value
0   1  2018    1.0
1   1  2018    1.0
2   1  2018    1.0
3   1  2018    1.0
4   1  2018    1.0
5   1  2018    0.1
6   2  2018    1.0
7   2  2018    1.0
8   1  2016    1.0
user_23366540

Using // and % with pandas repeat 

s1=df.value//1
s2=df.value%1
s=pd.concat([s1.loc[s1.index.repeat(s1.astype(int))],s2[s2!=0]]).sort_index()
s.loc[s>=1]=1
newdf=df.reindex(df.index.repeat((s1+(s2).ne(0)).astype(int)))  
newdf['value']=s.values
newdf
Out[236]:
   id  date  value
0   1  2016    1.0
0   1  2016    1.0
0   1  2016    1.0
1   2  2016    1.0
2   1  2018    1.0
3   1  2016    1.0
3   1  2016    0.1
user_79645270
Related