时间戳

首页 > 解决方案 > 如何在python中连接两个数据框

python - 如何在python中连接两个数据框

问题描述

我有两个数据框，我想加入它们，以便我可以在单个数据框中检查每年那一周的数量。

df1=  City  Week        qty       Year
      hyd   35          10        2015
      hyd   36          15        2015
      hyd   37          11        2015
      hyd   42          10        2015
      hyd   23          10        2016
      hyd   32          15        2016
      hyd   37          11        2017
      hyd   42          10        2017
      pune  35          10        2015
      pune  36          15        2015
      pune  37          11        2015
      pune  42          10        2015
      pune  23          10        2016
      pune  32          15        2016
      pune  37          11        2017
      pune  42          10        2017

 df2= city  Week         qty       Year
      hyd   23          10        2015
      hyd   32          15        2015
      hyd   35          12        2016
      hyd   36          15        2016
      hyd   37          11        2016
      hyd   42          10        2016
      hyd   43          12        2016
      hyd   44          18        2016
      hyd   35          11        2017
      hyd   36          15        2017
      hyd   37          11        2017
      hyd   42          10        2017
      hyd   51          14        2017
      hyd   52          17        2017
      pune  35          12        2016
      pune  36          15        2016
      pune  37          11        2016
      pune  42          10        2016
      pune  43          12        2016
      pune  44          18        2016
      pune  35          11        2017
      pune  36          15        2017
      pune  37          11        2017
      pune  42          10        2017
      pune  51          14        2017
      pune  52          17        2017

我想加入两个数据框，如结果所示，我想在单个数据框中为每个城市附加每年那一周的数量。

    city   Week  qty   Year   y2016_wk  qty    y2017_wk  qty y2015_week qty
     hyd    35   10    2015    2016_35    12     2017_35   11   nan      nan
     hyd    36   15    2015    2016_36    15     2017_36   15   nan      nan
     hyd    37   11    2015    2016_37    11     2017_37   11   nan      nan
     hyd    42   10    2015    2016_42    10     2017_42   10   nan      nan
     hyd    23   10    2016    nan        nan    2017_23   x    2015_23   10
     hyd    32   15    2016    nan        nan    2017_32   y    2015_32   15
     hyd    37   11    2017    2016_37    11       nan     nan 2015_37   x
     hyd    42   10    2017    2016_42    10       nan     nan 2015_42   y
     pune   35   10    2015    2016_35    12     2017_35     11  nan     nan 
     pune   36   15    2015    2016_36    15     2017_36     15  nan     nan
     pune   37   11    2015    2016_37    11     2017_37     11  nan     nan
     pune   42   10    2015    2016_42    10     2017_42     10  nan     nan

标签： pythonpython-3.xpandasdataframe

解决方案

就我个人而言，我不认为您的示例输出具有那么可读性，因此除非您出于特定原因需要该格式，否则我可能会考虑使用数据透视表。我也认为所需的代码更干净。

import pandas as pd 

df3 = pd.concat([df1, df2], ignore_index=True)
df4 = df3.pivot(index='Week', columns='Year', values='qty')

print(df4)

Year  2015  2016  2017
Week                  
35    10.0  12.0  11.0
36    15.0  15.0  15.0
37    11.0  11.0  11.0
42    10.0  10.0  10.0
43     NaN  12.0   NaN
44     NaN  18.0   NaN
51     NaN   NaN  14.0
52     NaN   NaN  17.0

推荐阅读