Replicate row in Pandas dataframe based on condition and change values for a specific column

Question

Start_Year   End_Year   Opp1              Opp2          Duration
1500         1501       ['A','B']        ['C','D']      1
1500         1510       ['P','Q','R']    ['X','Y']      10
1520         1520       ['A','X']        ['C']          0
...          ....        ........        .....          ..
1809         1820       ['M']            ['F','H','Z']  11

My dataset(csv file format) is of armed wars fought between different entities(countries, states, and factions represented by Capital letters A, B, P, Q etc as lists in Opp1(opposition) and Opp2 columns. Start_Year and End_Year are the years about when the war started and when it ended. The Duration column is created by subtracting values of End_Year to Start_Year.

I want to replicate those rows with Duration greater than 0 by the factor of the Duration of war i.e if duration is 6 years then replicate that row 6 times and decrease the Duration values by 1 and increase the Start_Year by 1 for every replication in replicated rows and keep the values in other columns same.(if duration is 1 year then it should replicate the row 2 times so that duration becomes 0 years for every war after replication to last step). My desired output column is like this:

I have no clue how to proceed with something like this as I am a beginner in data science and analysis. So pardon me for not showing any trial codes here.

Start_Year   End_Year   Opp1              Opp2          Duration
1500         1501       ['A','B']        ['C','D']      1
1501         1501       ['A','B']        ['C','D']      0
1500         1510       ['P','Q','R']    ['X','Y']      10
1501         1510       ['P','Q','R']    ['X','Y']      9
1502         1510       ['P','Q','R']    ['X','Y']      8
1503         1510       ['P','Q','R']    ['X','Y']      7
1504         1510       ['P','Q','R']    ['X','Y']      6
1505         1510       ['P','Q','R']    ['X','Y']      5
....         ....       .............    ........       ..
1510         1510       ['P','Q','R']    ['X','Y']      0
1520         1520       ['A','X']        ['C']          0
...          ....        ........        .....          ..
1809         1820       ['M']            ['F','H','Z']  11
1810         1820       ['M']            ['F','H','Z']  10
....         ....       .....            .............. ..
1820         1820       ['M']            ['F','H','Z']  0

Edit:1 Some example dataset The Dataset

user_10216112 · Answer

You can use pandas.Index.repeat to repeat the rows [Duration times] based on column Duration and then using pandas.core.groupby.GroupBy.cumcount you can add increasing cumulative values to the start_year column.

Reading data

data = [[1500, 1501, ['A','B'], ['C','D'], 1],
        [1500, 1510, ['P','Q','R'], ['X','Y'], 10],
        [1520, 1520, ['A','X'], ['C'], 0],
        [1809, 1820, ['M'], ['F','H','Z'], 11]]
df = pd.DataFrame(data, columns = ['Start_Year', 'End_Year', 'Opp1', 'Opp2', 'Duration'])

Repeating the values

mask = df['Duration'].gt(0)
df1 = df[mask].copy()
df1 = df1.loc[df1.index.repeat(df1['Duration'] + 1)]

Assigning increasing values to each group

df1['Start_Year'] += df1[['Start_Year', 'End_Year', 'Opp1', 'Opp2']].astype(str).groupby(['Start_Year', 'End_Year', 'Opp1', 'Opp2']).cumcount()

Generating output

df1['Duration'] = df1['End_Year'] - df1['Start_Year']
df = pd.concat([df1, df[~mask]]).sort_index(kind = 'mergesort').reset_index(drop=True)

This gives us the expected output :

    Start_Year  End_Year       Opp1       Opp2  Duration
0         1500      1501     [A, B]     [C, D]         1
1         1501      1501     [A, B]     [C, D]         0
2         1500      1510  [P, Q, R]     [X, Y]        10
3         1501      1510  [P, Q, R]     [X, Y]         9
4         1502      1510  [P, Q, R]     [X, Y]         8
5         1503      1510  [P, Q, R]     [X, Y]         7
6         1504      1510  [P, Q, R]     [X, Y]         6
7         1505      1510  [P, Q, R]     [X, Y]         5
8         1506      1510  [P, Q, R]     [X, Y]         4
9         1507      1510  [P, Q, R]     [X, Y]         3
10        1508      1510  [P, Q, R]     [X, Y]         2
11        1509      1510  [P, Q, R]     [X, Y]         1
12        1510      1510  [P, Q, R]     [X, Y]         0
13        1520      1520     [A, X]        [C]         0
14        1809      1820        [M]  [F, H, Z]        11
15        1810      1820        [M]  [F, H, Z]        10
16        1811      1820        [M]  [F, H, Z]         9
17        1812      1820        [M]  [F, H, Z]         8
18        1813      1820        [M]  [F, H, Z]         7
19        1814      1820        [M]  [F, H, Z]         6
20        1815      1820        [M]  [F, H, Z]         5
21        1816      1820        [M]  [F, H, Z]         4
22        1817      1820        [M]  [F, H, Z]         3
23        1818      1820        [M]  [F, H, Z]         2
24        1819      1820        [M]  [F, H, Z]         1
25        1820      1820        [M]  [F, H, Z]         0

Alternatively

You can also try the other way around after Repeating the values by assigning Duration in first decreasing cumulatively. And then calculating the 'Start_Year' again

df1['Duration'] = df1[['Start_Year', 'End_Year', 'Opp1', 'Opp2']].astype(str).groupby(['Start_Year', 'End_Year', 'Opp1', 'Opp2']).cumcount(ascending=False)
df1['Start_Year'] = df1['End_Year'] - df1['Duration']
df = pd.concat([df1, df[~mask]]).sort_index(kind = 'mergesort').reset_index(drop=True)

Output :

This gives you same expected output:

    Start_Year  End_Year       Opp1       Opp2  Duration
0         1500      1501     [A, B]     [C, D]         1
1         1501      1501     [A, B]     [C, D]         0
2         1500      1510  [P, Q, R]     [X, Y]        10
3         1501      1510  [P, Q, R]     [X, Y]         9
4         1502      1510  [P, Q, R]     [X, Y]         8
5         1503      1510  [P, Q, R]     [X, Y]         7
6         1504      1510  [P, Q, R]     [X, Y]         6
7         1505      1510  [P, Q, R]     [X, Y]         5
8         1506      1510  [P, Q, R]     [X, Y]         4
9         1507      1510  [P, Q, R]     [X, Y]         3
10        1508      1510  [P, Q, R]     [X, Y]         2
11        1509      1510  [P, Q, R]     [X, Y]         1
12        1510      1510  [P, Q, R]     [X, Y]         0
13        1520      1520     [A, X]        [C]         0
14        1809      1820        [M]  [F, H, Z]        11
15        1810      1820        [M]  [F, H, Z]        10
16        1811      1820        [M]  [F, H, Z]         9
17        1812      1820        [M]  [F, H, Z]         8
18        1813      1820        [M]  [F, H, Z]         7
19        1814      1820        [M]  [F, H, Z]         6
20        1815      1820        [M]  [F, H, Z]         5
21        1816      1820        [M]  [F, H, Z]         4
22        1817      1820        [M]  [F, H, Z]         3
23        1818      1820        [M]  [F, H, Z]         2
24        1819      1820        [M]  [F, H, Z]         1
25        1820      1820        [M]  [F, H, Z]         0

You can reset the index using pandas.DataFrame.reset_index.

Summary :

Basically, what we have done here is duplicated rows based on value from column Duration with condition.

We saved the rows which could have got vanished on using pandas.Index.repeat to repeat the rows [Duration value times] and once we replicated and applied logic on the rows with Duration > 0 replacing column values by subsequent increasing/decreasing cumulative values using pandas.core.groupby.GroupBy.cumcount we concatenated both the dataframe and sorted them on index using pandas.DataFrame.sort_index since the index was also supposed to be repeated when we used pandas.Index.repeat to repeat the rows [Duration value times]. Hence the sort on index would give us the dataframe in same order as it was in the original dataframe.

user_1410303 · Answer

Almost the same method as the other answer posted. But I think its a bit simplified:

df2 = df.apply(lambda x: x.repeat(df['Duration'].iloc[x.index]+1))
counts = df2.loc[df.Duration>1].groupby(['Start_Year', 'End_Year']).cumcount()
df2.loc[df.Duration>1,'Duration'] -= counts
df2.loc[df.Duration>1,'Start_Year'] += counts
df2.drop_duplicates(subset=['Start_Year', 'Duration'], ignore_index=True, inplace=True)

user_13802115 · Answer

Try this:

(df.assign(Duration = df['Duration'].map(lambda x: np.arange(0,x+1)[::-1])) #create a list of decending numbers from duration and replace duration column
.explode('Duration') #use duration column to create additional rows
.assign(Start_Year = lambda x: x['Start_Year']
    .add(x.groupby(level=0)
    .cumcount()))
    .reset_index(drop=True)) #use groupby cumcount which creates list of ascending numbers and add to year to increase year by one for each row