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

user_6401403 0
Viewed 54

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?

pandas python
2 Answers

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
user_92095460

Use:

#generate values between
v = [np.arange(a,b+1) for a, b in df[['num_from', 'num_to']].values]
#get lengths of arrays
lens = [len(x) for x in v]

from itertools import chain

df = pd.DataFrame({
    #flatten arrays 
    'num' : list(chain.from_iterable(v)), 
    #repeat columns
    'var1' : df['var1'].values.repeat(lens),
    'var2' : df['var2'].values.repeat(lens)
})

print (df)
   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
user_29010020
Related