How to transpose sumcum data in Pandas?

user_5377679 0
Viewed 65

I have a dataset like this:

Year City
1905 New York
1906 New York
1906 Boston
*** ***
2021 Houston

I wanted to add sumcum, so I did the following:

df["Count"]=1 

df['cumsum']=df.groupby(['City'])['Count'].cumsum()

And it is working fine, although not sure if this was the best approach.

What I would like to do next is to transpose the data, but also fill all the gaps. Because occurrence of the cities is not consistent (e.g. Boston occurs in 1924 then again in 1928).

I would like to have this:

enter image description here

How can I make this with Pandas?

Thanks

cumsum pandas transpose python-3.x
1 Answers

Given the following toy dataframe:

import pandas as pd

df = pd.DataFrame(
    {
        "Year": {0: 1905, 1: 1906, 2: 1906, 3: 1907, 4: 1908, 5: 1909},
        "City": {
            0: "New York",
            1: "New York",
            2: "Boston",
            3: "New York",
            4: "Boston",
            5: "New York",
        },
    }
)

You can do it like this:

new_df = (
    pd.DataFrame(df.value_counts())
    .rename(columns={0: "Count"})
    .sort_values(by=["Year", "Count"], ascending=True)
    .assign(cumsum=lambda x: x.groupby(["City"])["Count"].cumsum())
    .drop(columns="Count")
    .reset_index()
    .pipe(lambda df_: pd.pivot(df_, index="Year", columns="City"))
    .fillna(method="ffill")
    .fillna(0)
    .droplevel(0, axis=1)
    .reset_index()
    .rename_axis(None, axis=1)
)

print(new_df)
# Output
   Year  Boston  New York
0  1905     0.0       1.0
1  1906     1.0       2.0
2  1907     1.0       3.0
3  1908     2.0       3.0
4  1909     2.0       4.0
user_112460560
Related