3

I'm trying to build a dataframe in python that is filled with 1s and 0s, depending on the number in one column:

Date        Hour
2005-01-01  1
2005-01-01  2
2005-01-01  3
2005-01-01  4

I want to make new columns based on the number in "Hour", and fill each column with a 1 if that row is equal to the value in "Hour", or 0 if not.

Date        Hour HE1 HE2 HE3 HE4
2005-01-01  1    1   0   0   0
2005-01-01  2    0   1   0   0
2005-01-01  3    0   0   1   0
2005-01-01  4    0   0   0   1

I can do it with this code, but it takes a long time:

for x in range(1,5):
    _HE = 'HE' + str(x)
    for i in load.index:
        load.at[i, _HE] = 1 if load.at[i,'Hour']==x else 0

I feel like this is a great application (no pun intended) for .apply(), but I can't get it to work right.

How would you speed this up?

anothermh
  • 9,815
  • 3
  • 33
  • 52
bearcub
  • 313
  • 4
  • 11
  • Of course nested for loop is not preferred – U13-Forward Sep 03 '18 at 05:59
  • 1
    `.apply`, in general, is not faster than a for-loop. `.apply` is a Python interpreter level [for-loop under the hood](https://stackoverflow.com/questions/38938318/why-apply-sometimes-isnt-faster-than-for-loop-in-pandas-dataframe/38938507#38938507) – juanpa.arrivillaga Sep 03 '18 at 06:02

3 Answers3

5

In pandas loops are not recommended because slow if exist some vectorized solution.

Notice: In function apply are loops under the hood too.

So use pandas.get_dummies and DataFrame.add_prefix and join for add to original df:

df = df.join(pd.get_dummies(df['Hour'].astype(str)).add_prefix('HE'))
print (df)
         Date  Hour  HE1  HE2  HE3  HE4
0  2005-01-01     1    1    0    0    0
1  2005-01-01     2    0    1    0    0
2  2005-01-01     3    0    0    1    0
3  2005-01-01     4    0    0    0    1

Similar function have different performance:

df = pd.concat([df] * 1000, ignore_index=True)

In [62]: %timeit df.join(pd.get_dummies(df['Hour'].astype(str)).add_prefix('HE'))
3.54 ms ± 277 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

#U9-Forward solution
In [63]: %timeit df.join(df['Hour'].astype(str).str.get_dummies().add_prefix('HE'))
61.6 ms ± 297 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
3

pandas.factorize and array slice assignment

j, h = pd.factorize(df.Hour)
i = np.arange(len(df))

b = np.zeros((len(df), len(h)), dtype=h.dtype)
b[i, j] = 1

df.join(pd.DataFrame(b, df.index, h).add_prefix('HE'))

         Date  Hour  HE1  HE2  HE3  HE4
0  2005-01-01     1    1    0    0    0
1  2005-01-01     2    0    1    0    0
2  2005-01-01     3    0    0    1    0
3  2005-01-01     4    0    0    0    1
piRSquared
  • 285,575
  • 57
  • 475
  • 624
0

Even tho it's really similar to @jezrael's answer but, this is also much better, (it's just using .str accessor for get_dummies:

print(df.join(df['Hour'].astype(str).str.get_dummies().add_prefix('HE')))

Output:

         Date  Hour  HE1  HE2  HE3  HE4
0  2005-01-01     1    1    0    0    0
1  2005-01-01     2    0    1    0    0
2  2005-01-01     3    0    0    1    0
3  2005-01-01     4    0    0    0    1
U13-Forward
  • 69,221
  • 14
  • 89
  • 114