How to transform multiple columns of days into week columns-CodePudding

I would like to know how can I transform the day columns into week columns.

I tryed groupby.sum() but there is no column name pattern, I dont know what to groupby for.

So the result should be column name like 'weekX' - "week1(Sum of 7 first days) - week2 - week3" and so on.

Thanks in advance.

CodePudding user response：

You can try:

idx = pd.RangeIndex(len(df.columns[4:])) // 7
out = df.iloc[:, 4:].groupby(idx, axis=1).sum().rename(columns=lambda x:f'Week{x 1}')
out = pd.concat([df.iloc[:, :4], out], axis=1)
print(out)

# Output
    Province/State         Country/Region         Lat  ...  Week26  Week27  Week28
0              NaN            Afghanistan   3.393.911  ...  247210  252460  219855
1              NaN                Albania     411.533  ...   28068   32671   32113
2              NaN                Algeria     280.339  ...  157675  187224  183841
3              NaN                Andorra     425.063  ...    6147    6283    5552
4              NaN                 Angola    -112.027  ...    4741    6341    6978
..             ...                    ...         ...  ...     ...     ...     ...
261            NaN  Sao Tome and Principe       1.864  ...    5199    5813    5231
262            NaN                  Yemen  15.552.727  ...   11089   11717   10363
263            NaN                Comoros    -116.455  ...    2310    2419    2292
264            NaN             Tajikistan      38.861  ...   47822   50032   44579
265            NaN                Lesotho      -29.61  ...    2259    3011    3922

[266 rows x 32 columns]

CodePudding user response：

You can use the melt method to combine all your date columns into a single 'Date' column:

df = df.melt(id_vars=['Province/State', 'Country/Region', 'Lat', 'Long'], var_name='Date', value_name='Value')

From this point it should be straightforward to group by the 'Date' column by week, and then unstack it if you want to have it as multiple columns again.