使用多个值制作Python Pandas表 [英] Take more than one value to make a table with Python Pandas

问题描述

使用我的代码，我可以在1中联接两个Excel数据库.问题是它只显示了Revenue列，而没有显示印象.更清楚地说，我留下了代码和示例.我尝试过:

With my code I can join two Excel databases in 1. The problem is that it only shows me the Revenue column and not the column impressions. To be more clear I leave the code and the examples. I tried with:

 df1 = df1.pivot(index = "Cliente", columns='Fecha', values=['Impresiones','Revenue']) 

但是我有一个错误:Exception: Data must be 1-dimensional

代码:

import pandas as pd
import pandas.io.formats.excel

# Leemos ambos archivos y los cargamos en DataFrames
df1 = pd.read_excel("archivo1.xlsx")
df2 = pd.read_excel("archivo2.xlsx")

# Pivotamos ambas tablas
df1 = df1.pivot(index = "Cliente", columns='Fecha', values='Revenue')
df2 = df2.pivot(index = "Cliente", columns='Fecha', values='Revenue')

# Unimos ambos dataframes tomando la columna "Cliente" como clave
merged = pd.merge(df1, df2, right_index =True, left_index = True, how='outer')
merged.sort_index(axis=1, inplace=True)

# Creamos el xlsx de salida
pandas.io.formats.excel.header_style = None

with pd.ExcelWriter("Data.xlsx",
                    engine='xlsxwriter',
                    date_format='dd/mm/yyyy',
                    datetime_format='dd/mm/yyyy') as writer:

    merged.to_excel(writer, sheet_name='Sheet1')

archivo1:

archivo2:

结果:

必要:

以下是文本的数据框:

archivo1:
Fecha   Cliente Impresiones Revenue
21/12/17    Jose    12345   $989
21/12/17    Martin  3245    $10
21/12/17    Pedro   645     $879
21/12/17    Esteban 2345    $899
21/12/17    Mauro   654     $98

archivo2:
Fecha   Cliente Impresiones Revenue
20/12/17    Esteban 12345   $150
20/12/17    Martin  3245    $20
20/12/17    Pedro   645     $3000
20/12/17    Mauro   2345    $50
20/12/17    Jose    654n    $667

推荐答案

您可以使用:

• 将两个df结合在一起
• 重塑类别为Impresiones和Revenue
的列
• 排序索引，第二级后代
• 按掩码更改第一级索引并设置为索引

• join together both df
• reshape for column with categories Impresiones and Revenue
• sorting index, second level descendent
• change first level of index by mask and set to index

df = (pd.concat([df1,df2])
        .set_index(["Cliente",'Fecha'])
        .stack()
        .unstack(1)
        .sort_index(ascending=(True, False)))

m = df.index.get_level_values(1) == 'Impresiones'
df.index = np.where(m, 'Impresiones', df.index.get_level_values(0))
print (df)
Fecha       20/12/17 21/12/17
Esteban         $150     $899
Impresiones    12345     2345
Jose            $667     $989
Impresiones     654n    12345
Martin           $20      $10
Impresiones     3245     3245
Mauro            $50      $98
Impresiones     2345      654
Pedro          $3000     $879
Impresiones      645      645

这篇关于使用多个值制作Python Pandas表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！