How to calculate daily user difference and reshape pandas dataframe?

user_3526226 0
Viewed 104

I'm working with a Pandas DataFrame in Python, currently I have the following schema:

>>> import pandas as pd
>>> d = {'date': ['15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep'],
...      'user': ['A','A','A','A','B','B','B','B','C','C','C','C'],
...      'sales': [5,8,6,7,9,12,11,11,11,15,8,6]}
>>> df = pd.DataFrame(data=d)
>>> df
      date user  sales
0   15-Sep    A      5
1   16-Sep    A      8
2   17-Sep    A      6
3   18-Sep    A      7
4   15-Sep    B      9
5   16-Sep    B     12
6   17-Sep    B     11
7   18-Sep    B     11
8   15-Sep    C     11
9   16-Sep    C     15
10  17-Sep    C      8
11  18-Sep    C      6

current dataset

And would like to transform (transpose?) to get the daily difference per user, in reference to last day, following the above example, would like to get this:

>>> d = {'user': ['A','B','C'],
...      '16-Sep': [3,3,4],
...      '17-Sep': [-2,-1,-7],
...      '18-Sep': [1,0,-2]}
>>> df = pd.DataFrame(data=d)
>>> df
  user  16-Sep  17-Sep  18-Sep
0    A       3      -2       1
1    B       3      -1       0
2    C       4      -7      -2

goal_table

This goal table means that User A sold -2 items on Sep 17th, compared to Sep 16th.

What would be the best way to do this? Any example on how I could do this? I couldn't find a similar problem.

pandas transpose dataset date python
2 Answers
  • .sort_values on the dataframe by user and date
  • .groupby('user', as_index=False) and aggregate difference.
    • A TypeError: rename() got an unexpected keyword argument 'columns' will occur if as_index=False is not included.
  • df.join with the .groupby result.
  • .pivot the dataframe and .dropna.
import pandas as pd

# setup test dataframe
d = {'date': ['15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep'], 'user': ['A','A','A','A','B','B','B','B','C','C','C','C'], 'sales': [5,8,6,7,9,12,11,11,11,15,8,6]}
df = pd.DataFrame(data=d)

# groupby and join to df
dfg = df.sort_values(['user', 'date']).join(df.groupby('user', as_index=False)['sales'].diff().rename(columns={'sales': 'sales_diff'}))

# pivot the dataframe into the correct shape
dfp = dfg.pivot(columns='date', index='user', values='sales_diff').reset_index().dropna(axis=1)

# remove the name of the columns (e.g. date)
dfp.columns.name = None

# display(dfp)
  user  16-Sep  17-Sep  18-Sep
0    A     3.0    -2.0     1.0
1    B     3.0    -1.0     0.0
2    C     4.0    -7.0    -2.0
user_77588040

Following Trenton valid answer, I am getting an error with the .rename() function, therefore I added an extra step to overcome this.

Following code is working for me:

import pandas as pd

d = {'date': ['15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep','15-Sep','16-Sep','17-Sep','18-Sep'],
     'user': ['A','A','A','A','B','B','B','B','C','C','C','C'],
     'sales': [5,8,6,7,9,12,11,11,11,15,8,6]}
df = pd.DataFrame(data=d)
#print("Original dataset\n",df,"\n")

# Sort values on user and date (to obtain proper differences)
df = df.sort_values(['user', 'date'])

# Add column sales_diff that groups by user, ad .diff() in sales 
df['sales_diff'] = df.groupby(['user'])['sales'].diff()
#print("Added difference sales column\n",df,"\n")

#Pivot table on user and sales_diff
dfp = df.pivot(columns='date', index='user', values='sales_diff').reset_index().dropna(axis=1)
#print("Pivot dataset on user\n",dfp,"\n")

# Remove the name of the columns (e.g. date)
dfp.columns.name = None

#print("Goal dataset obtained\n")
print(dfp)
  user  16-Sep  17-Sep  18-Sep
0    A     3.0    -2.0     1.0
1    B     3.0    -1.0     0.0
2    C     4.0    -7.0    -2.0
user_35262260
Related