Repeated Values in Pivot

user_13072752 0
Viewed 58

I have two dataframes df1 and df2. One with clients debt, the other with client payments with dates.

I want to create a new data frame with the % of the debt paid in the month of the payment until 01-2017.

The problem is that some clients paid two times in the same month so there are repeated values and the pivot cant run that way and it shows : ValueError: Index contains duplicate entries, cannot reshape

How can I solve this issue?

Thank you for your time!

import pandas as pd 

d1 = {'client number': ['2', '2','3','6','7','7','8','8','8','8','8','8','8','8'],
     'month': [1, 2, 3,1,10,12,3,5,8,1,1,4,5,8],
    'year':[2013,2013,2013,2019,2013,2013,2013,2013,2013,2014,2014,2015,2016,2017],
    'payment' :[100,100,200,10000,200,100,300,500,200,100,200,200,500,50]}

df1 = pd.DataFrame(data=d1).set_index('client number')

d2 = {'client number': ['2','3','6','7','8'],
     'debt': [200, 600,10000,300,3000]}

df2 = pd.DataFrame(data=d2)


df2 = df2.set_index('client number')

df1['pct'] = df1['payment'].div(df1.index.map(df2['debt'])).round(2)
df1['date'] = df1['year'].astype(str) + '-' + df1['month'].astype(str).str.zfill(2)
df3 = df2.join(df1.pivot(columns='date', values='pct').fillna(0)).reset_index()
pandas dataframe pivot python-3.x python
2 Answers

Use, DataFrame.pivot_table with agg_func='sum', as the fractions of monthly payments of debt can be added together:

df3 = (
    df2.join(
        df1.pivot_table(index=df1.index, columns='date', values='pct', aggfunc='sum').fillna(0))
    .reset_index()
)

Result:

# print(df3)

  client number   debt  2013-01  2013-02  2013-03  2013-05  2013-08  2013-10  2013-12  2014-01  2015-04  2016-05  2017-08  2019-01
0             2    200      0.5      0.5     0.00     0.00     0.00     0.00     0.00      0.0     0.00     0.00     0.00      0.0
1             3    600      0.0      0.0     0.33     0.00     0.00     0.00     0.00      0.0     0.00     0.00     0.00      0.0
2             6  10000      0.0      0.0     0.00     0.00     0.00     0.00     0.00      0.0     0.00     0.00     0.00      1.0
3             7    300      0.0      0.0     0.00     0.00     0.00     0.67     0.33      0.0     0.00     0.00     0.00      0.0
4             8   3000      0.0      0.0     0.10     0.17     0.07     0.00     0.00      0.1     0.07     0.17     0.02      0.0
user_128331660

You can use pivot_table instead. This allows you to apply a function to aggregate duplicate entries. For instance, you can take the max of duplicate values. Also, seems like you should include the index parameter in this function call. Replace your original pivot call with this:

df1.pivot_table(columns='date', index='client number', values='pct', aggfunc='max').fillna(0)
user_69425270
Related