Simple Pandas Groupby/Pivot?-CodePudding

I have the following DataFrame

    Value     Date
0       1  2022-01-01
1       2  2022-01-01
2       3  2022-01-01
3       4  2022-01-02
4       5  2022-01-02
5       6  2022-01-02
6       7  2022-01-03
7       8  2022-01-03
8       9  2022-01-03

I would like to obtain the following DataFrame, by grouping all the values associated with a given date:

Date    2022-01-01    2022-01-02    2022-01-03
0                1             4             7
1                2             5             8
2                3             6             9

I know this should be a really simply task, but I'm struggling to figure it out. I have used df.groupby('Date')['Value].apply(list).to_frame.T, but that didn't work. Any help in solving this would be greatly appreciated.

CodePudding user response：

One option can be to use pandas.DataFrame.pivot and then drop nan values.

df_new = df.pivot(columns='Date', values='Value').apply(
    lambda x: pd.Series(x.dropna().values)).astype('int')

print(df_new)

Output:

Date  2022-01-01  2022-01-02  2022-01-03
0              1           4           7
1              2           5           8
2              3           6           9

CodePudding user response：

Another possible solution, based on pivot_wider of package pyjanitor:

# pip install pyjanitor
import janitor

(df.set_index(df.index % 3)
 .pivot_wider(names_from = 'Date', values_from = 'Value')
 .rename_axis('Date', axis=1))

Output:

Date  2022-01-01  2022-01-02  2022-01-03
0              1           4           7
1              2           5           8
2              3           6           9