Question:
Creating a pivot table by 6 month interval rather than year

To Create a pivot table by 6 month interval first Try to add the vessel column:

date_rng = pd.date_range(start="2023-01-01", end="2024-01-05", freq="D")

data = np.random.rand(len(date_rng), 3)

df = pd.DataFrame(data, columns=["Column1", "Column2", "Column3"], index=date_rng)


# added Vessel column

df["Vessel"] = np.random.randint(1, 5, size=len(date_rng))


pivot_df = pd.pivot_table(

    df,

    index=[df.index.year, np.where(df.index.month <= 6, "H1", "H2")],

    columns="Vessel",

    values=["Column1", "Column2", "Column3"],

    aggfunc="nunique",

)

print(pivot_df)


Prints:

        Column1                   Column2                   Column3                  

Vessel        1     2     3     4       1     2     3     4       1     2     3     4

2023 H1    39.0  41.0  59.0  42.0    39.0  41.0  59.0  42.0    39.0  41.0  59.0  42.0

     H2    43.0  53.0  34.0  54.0    43.0  53.0  34.0  54.0    43.0  53.0  34.0  54.0

2024 H1     NaN   1.0   3.0   1.0     NaN   1.0   3.0   1.0     NaN   1.0   3.0   1.0


EDIT: To convert index back to dates:


pivot_df.index = [

    pd.to_datetime(f'{year}-{"01-01" if half == "H1" else "06-01"}')

    for year, half in pivot_df.index

]

print(pivot_df)


Prints:

          Column1                   Column2                   Column3                  

Vessel           1     2     3     4       1     2     3     4       1     2     3     4

2023-01-01    48.0  44.0  43.0  46.0    48.0  44.0  43.0  46.0    48.0  44.0  43.0  46.0

2023-06-01    49.0  41.0  48.0  46.0    49.0  41.0  48.0  46.0    49.0  41.0  48.0  46.0

2024-01-01     1.0   1.0   NaN   3.0     1.0   1.0   NaN   3.0     1.0   1.0   NaN   3.0


Answered by: >Andrej Kesely

Credit:> StackOverflow


Suggested blogs:

>How to do wild grouping of friends in Python?

>How to download an entire S3 bucket - Complete Guide

>How to get item details of Woo Commerce in custom email content?

>How to install Laravel using composer and configure?

>How to merge cells with HTML, CSS, and PHP?

>How to Migrate your apps to Android 13

>How to Read a csv file with php using cURL

>How to read frame from ffmpeg subprocess in Python?


Submit
0 Answers
Top Questions
06-01-2024

How to allign two plots in Matplotlib?

12-02-2024

How to track the change of variable in Python?

18-01-2024

Write a code for the function checkEvenOdd() using Python

12-02-2024

How to use Python pattern matching to match class types?

06-01-2024

How to Install mariaDB client efficiently inside docker?

Related Questions

Write a code for the function checkEvenOdd() using Python
img
How to Reverse a sublist of a linked list in Python?
img
How to solve maximum sum problem using Python3
img
How to mock a constant value in Python?
img
Return an array of leaders in order of their appearance using Python3
img

What Our Clients Say About Us

Client satisfaction is our ultimate goal. Here are some kind words of our precious clients they have used to express their satisfaction with our service.

Why Choose Us ?

We endow businesses with flexible engagement models based on their unique needs. Our strength lies in state-of-the-art technology and affordable consulting services. Try us for fast POCs, full-fledged applications, or technology consulting. Always available for your service.