CODEWITHSUNDEEP
pythonpandasdataframe
DataScienceNovice
DataScienceNovice

Reputation: 502

Simple Pandas Groupby/Pivot?

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.

Upvotes: 1

Views: 39

Answers (2)

PaulS
PaulS

Reputation: 25353

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

Upvotes: 1

I'mahdi
I'mahdi

Reputation: 24059

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

Upvotes: 2

Related Questions