CODEWITHSUNDEEP
pythonpandasnumpy
wayneloo
wayneloo

Reputation: 251

Incremental assignment in pandas dataframe to determine month from week number without date element

I'm having week numbers in the dataframe from 1 to 52 e.g. [1,2,3,4,5,6,7,8,..52]

I'm trying to create a new column for month but it would mean an incremental assignment like [1,2,3,4] = 1, [5,6,7,8] = 2, .. [49,50,51,52] = 12

I tried getting the records by multiple of 4 using df[df["week"]%4==0] and then ffill it but seems like we can only assign it all to the same number which is not what I want. Instead I want to assign [1..12] accordingly. Is there another way to do this?

Upvotes: 3

Views: 120

Answers (1)

jezrael
jezrael

Reputation: 862591

Subtract 1 first and then use integer division by 4:

df = pd.DataFrame({'week':range(1,53)})

df['new'] = (df["week"] - 1)//4 
print (df.head(10))
   week  new
0     1    0
1     2    0
2     3    0
3     4    0
4     5    1
5     6    1
6     7    1
7     8    1
8     9    2
9    10    2

print (df.tail(10))
    week  new
42    43   10
43    44   10
44    45   11
45    46   11
46    47   11
47    48   11
48    49   12
49    50   12
50    51   12
51    52   12

If want starting by 1 it is possible, but last value is 13:

df['new'] = ((df["week"] - 1)//4) + 1
print (df.head(10))
   week  new
0     1    1
1     2    1
2     3    1
3     4    1
4     5    2
5     6    2
6     7    2
7     8    2
8     9    3
9    10    3

print (df.tail(10))
    week  new
42    43   11
43    44   11
44    45   12
45    46   12
46    47   12
47    48   12
48    49   13
49    50   13
50    51   13
51    52   13

If want values between 1 and 12 (but some groups has more like 4 values) use, solution by @Aryerez, thank you:

df['new'] = ((df["week"] - 1) // (52 / 12)).astype(int) + 1

print (df.head(10))
   week  new
0     1    1
1     2    1
2     3    1
3     4    1
4     5    1
5     6    2
6     7    2
7     8    2
8     9    2
9    10    3

print (df.tail(10))
    week  new
42    43   10
43    44   10
44    45   11
45    46   11
46    47   11
47    48   11
48    49   12
49    50   12
50    51   12
51    52   12

EDIT: For 5 values in each 3rd group use:

df['new'] = ((df["week"] + 4) // (52 / 12)).astype(int)

print (df.head(15))
    week  new
0      1    1
1      2    1
2      3    1
3      4    1
4      5    2
5      6    2
6      7    2
7      8    2
8      9    3
9     10    3
10    11    3
11    12    3
12    13    3
13    14    4
14    15    4

print (df.tail(15))
    week  new
37    38    9
38    39    9
39    40   10
40    41   10
41    42   10
42    43   10
43    44   11
44    45   11
45    46   11
46    47   11
47    48   12
48    49   12
49    50   12
50    51   12
51    52   12

Upvotes: 3

Related Questions