Is there a way to loop through and based a single column value and mark a value into multiple new columns in Pandas?

user_8139601 0
Viewed 39

The dataframe would look something similar to this:

start = [0,2,4,5,1]
end = [3,5,5,5,2]
df = pd.DataFrame({'start': start,'end': end})

The result I want look something like this: Basically marking a value from start to finish across multiple columns. So if one that start on 0 and ends on 3 I want to mark new column 0 to 3 with a value(1) and the rest with 0.

start = [0,2,4,5,1]
end = [3,5,5,5,2]
diff = [3,3,1,0,1]
col_0 = [1,0,0,0,0]
col_1=[1,0,0,0,1]
col_2 = [1,1,0,0,1]
col_3=[1,1,0,0,0]
col_4=[0,1,1,0,0]
col_5=[0,1,1,1,0]

df = pd.DataFrame({'start': start,'end': end, 'col_0':col_0, 'col_1': col_1, 'col_2': col_2, 'col_3':col_3, 'col_4': col_4, 'col_5': col_5})

start   end  col_0  col_1   col_2   col_3   col_4   col_5
0        3    1      1        1      1        0       0
2        5    0      0        1      1        1       1
4        5    0      0        0      0        1       1
5        5    0      0        0      0        0       1
1        2    0      1        1      0        0       0
pandas dataframe iteration
2 Answers

Convert your range from start to stop to a list of indices then explode it. Finally, use indexing to set values to 1:

import numpy as np

range_to_ind = lambda x: range(x['start'], x['end']+1)
(i, j) = df.apply(range_to_ind, axis=1).explode().astype(int).reset_index().values.T

a = np.zeros((df.shape[0], max(df['end'])+1), dtype=int)
a[i, j] = 1

df = df.join(pd.DataFrame(a).add_prefix('col_'))

Output:

>>> df
   start  end  col_0  col_1  col_2  col_3  col_4  col_5
0      0    3      1      1      1      1      0      0
1      2    5      0      0      1      1      1      1
2      4    5      0      0      0      0      1      1
3      5    5      0      0      0      0      0      1
4      1    2      0      1      1      0      0      0
user_152399510

Use dict.fromkeys in list comprehension for each row in DataFrame and pass to DataFrame constructor if perfromance is important:

L = [dict.fromkeys(range(s, e + 1), 1) for s, e in zip(df['start'], df['end'])]

df = df.join(pd.DataFrame(L, index=df.index).add_prefix('col_').fillna(0).astype(int))
print (df)
   start  end  col_0  col_1  col_2  col_3  col_4  col_5
0      0    3      1      1      1      1      0      0
1      2    5      0      0      1      1      1      1
2      4    5      0      0      0      0      1      1
3      5    5      0      0      0      0      0      1
4      1    2      0      1      1      0      0      0

If possible some range value is missing like in changed sample data add DataFrame.reindex:

#missing column 6
start = [0,2,4,7,1]
end = [3,5,5,8,2]
df = pd.DataFrame({'start': start,'end': end})

L = [dict.fromkeys(range(s, e + 1), 1) for s, e in zip(df['start'], df['end'])]

df1 = (pd.DataFrame(L, index=df.index)
         .reindex(columns=range(df['start'].min(), df['end'].max() + 1), fill_value=0)
         .add_prefix('col_')
         .fillna(0)
         .astype(int))

df = df.join(df1)
print (df)
   start  end  col_0  col_1  col_2  col_3  col_4  col_5  col_6  col_7  col_8
0      0    3      1      1      1      1      0      0      0      0      0
1      2    5      0      0      1      1      1      1      0      0      0
2      4    5      0      0      0      0      1      1      0      0      0
3      7    8      0      0      0      0      0      0      0      1      1
4      1    2      0      1      1      0      0      0      0      0      0

EDIT: For counts hours use:

start = pd.to_datetime([0,2,4,5,1], format='%H')
end = pd.to_datetime([3,5,5,5,2], format='%H')
df = pd.DataFrame({'start': start,'end': end})
df.loc[[0,1], 'end'] += pd.Timedelta(1, 'day')

#list for hours datetimes
L = [dict.fromkeys(pd.date_range(s, e, freq='H'), 1) for s, e in zip(df['start'], df['end'])]

df1 = pd.DataFrame(L, index=df.index)

#aggregate sum by hours in columns
df1 = df1.groupby(df1.columns.hour, axis=1).sum().astype(int)

print (df1)
   0   1   2   3   4   5   6   7   8   9   ...  14  15  16  17  18  19  20  \
0   2   2   2   2   1   1   1   1   1   1  ...   1   1   1   1   1   1   1   
1   1   1   2   2   2   2   1   1   1   1  ...   1   1   1   1   1   1   1   
2   0   0   0   0   1   1   0   0   0   0  ...   0   0   0   0   0   0   0   
3   0   0   0   0   0   1   0   0   0   0  ...   0   0   0   0   0   0   0   
4   0   1   1   0   0   0   0   0   0   0  ...   0   0   0   0   0   0   0   

   21  22  23  
0   1   1   1  
1   1   1   1  
2   0   0   0  
3   0   0   0  
4   0   0   0  

[5 rows x 24 columns]
user_29010020
Related