Is there a better way to join two dataframes in Pandas?-CodePudding

I have to convert data from a SQL table to pandas and display the output. The data is a sales table:

      cust     prod  day  month  year state  quant
0    Bloom    Pepsi    2     12  2017    NY   4232
1    Knuth    Bread   23      5  2017    NJ   4167
2    Emily    Pepsi   22      1  201    CT   4404
3    Emily   Fruits   11      1  2010    NJ   4369
4    Helen     Milk    7     11  2016    CT    210

I have to convert this to find average sales for each customer for each state for year 2017:

CUST   AVG_NY   AVG_CT AVG_NJ
Bloom  28923    3241   1873
Sam    4239     872    142

Below is my code:

import pandas as pd
import psycopg2 as pg
engine = pg.connect("dbname='postgres' user='postgres' host='127.0.0.1' port='8800' password='sh'")
df = pd.read_sql('select * from sales', con=engine) 

df.drop("prod", axis=1, inplace=True)
df.drop("day", axis=1, inplace=True)
df.drop("month", axis=1, inplace=True)
df_main = df.loc[df.year == 2017]

#df.drop(df[df['state'] != 'NY'].index, inplace=True) 
df2 = df_main.loc[df_main.state == 'NY']
df2.drop("year",axis=1,inplace=True)

NY = df2.groupby(['cust']).mean()




df3 = df_main.loc[df_main.state == 'CT']
df3.drop("year",axis=1,inplace=True)

CT = df3.groupby(['cust']).mean()


df4 = df_main.loc[df_main.state == 'NJ']
df4.drop("year",axis=1,inplace=True)

NJ = df4.groupby(['cust']).mean()
 

NY = NY.join(CT,how='left',lsuffix = 'NY', rsuffix = '_right')
NY = NY.join(NJ,how='left',lsuffix = 'NY', rsuffix = '_right')

print(NY)

This give me an output like:

           quantNY  quant_right        quant
cust
Bloom  3201.500000       3261.0  2277.000000
Emily  2698.666667       1432.0  1826.666667
Helen  4909.000000       2485.5  2352.166667

I found a question where I can change the column names to the output I need but I am not sure if the below two lines of the code are the right way to join the dataframes:

NY = NY.join(CT,how='left',lsuffix = 'NY', rsuffix = '_right')
NY = NY.join(NJ,how='left',lsuffix = 'NY', rsuffix = '_right')

Is there a better way of doing this with Pandas?

CodePudding user response：

Use pivot_table:

df.pivot_table(index=['year', 'cust'], columns='state',
               values='quant', aggfunc='mean').add_prefix('AVG_')