Insert additional rows into a dataframe according to its values where it's needed

Question

I have a text file of this kind:

num_from    num_to   var1    var2
1           1        20      30
2           5        40      50
6           7        60      70
8           8        80      90

Here the values are the same for the numbers between num_from and num_to, for example, var1 is 40 and var2 is 50 for numbers 2, 3, 4, 5.

I want to read this data into a dataframe with read_csv() and transform that dataframe into this:

    num    var1    var2
0   1      20      30
1   2      40      50
2   3      40      50
3   4      40      50
4   5      40      50
5   6      60      70
6   7      60      70
7   8      80      90

Is there a way to do it with pandas, or it's better to do it in a loop?

Answer 1

You can use pd.concat with a generator expression:

df = pd.read_csv('file.csv')  # read file into dataframe

gen = (pd.DataFrame({'num': np.arange(row.num_from, row.num_to+1),
                     'var1': row.var1, 'var2': row.var2}) \
       for row in df.itertuples(index=False))

res = pd.concat(gen, ignore_index=True)

print(res)

   num  var1  var2
0    1    20    30
1    2    40    50
2    3    40    50
3    4    40    50
4    5    40    50
5    6    60    70
6    7    60    70
7    8    80    90