0
$\begingroup$

I would like to transform dataframe and cummulate them using pandas.

  1. year country value
  2. 1999 JAPAN 10
  3. 2000 KOREA 15
  4. 2000 USA 20
  5. 2001 USA 13
  6. 2002 JAPAN 30

* I want to transform dataframe and cummulate value for each country

  1. year country value
  2. 1999 JAPAN 10
  3. 1999 KOREA 0
  4. 1999 USA 0
  5. 2000 JAPAN 10
  6. 2000 KOREA 15
  7. 2000 USA 20
  8. 2001 JAPAN 10
  9. 2001 KOREA 15
  10. 2001 USA 33
  11. 2001 JAPAN 40
  12. 2001 KOREA 15
  13. 2001 USA 33

I need your help.

Thank you.

$\endgroup$
  • $\begingroup$ Is this what you want? ``` df.groupby(["Country"]).sum() ``` $\endgroup$ – e3pk Mar 22 at 12:03
0
$\begingroup$

I think the following lines should give the output you are looking for:

# Create pivot table
df = pd.pivot_table(df, values="value", index="country", columns="year")
# Calculate cumulative sum and forward fill NaN
df = df.cumsum(axis=1).fillna(method="ffill", axis=1)
# Reshape data back into long format
df = df.reset_index().melt("country")

I first create a pivot table with the countries in the rows and the years in the columns to get all the years that may be missing in the long format data. I then calculate the cumulative sum, after which I forward fill the values over the columns. To finally reshape the data back into the original long format I reset the index and melt the dataframe with the country as the id column. Given your input, this returns the following dataframe:

   country  year  value
0    JAPAN  1999   10.0
1    KOREA  1999    NaN
2      USA  1999    NaN
3    JAPAN  2000   10.0
4    KOREA  2000   15.0
5      USA  2000   20.0
6    JAPAN  2001   10.0
7    KOREA  2001   15.0
8      USA  2001   33.0
9    JAPAN  2002   40.0
10   KOREA  2002   15.0
11     USA  2002   33.0
| improve this answer | |
$\endgroup$
  • $\begingroup$ I think it shoud be modifyed from df.pivot_table to pd.pivot_table. right? $\endgroup$ – 윤두현 Mar 22 at 12:33
  • $\begingroup$ Correct, edited in my post. $\endgroup$ – Oxbowerce Mar 22 at 12:34
  • $\begingroup$ It worked perfectly what I want. Thank you so much. $\endgroup$ – 윤두현 Mar 22 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.