对公司名称的DataFrame进行非规范化处理[第2部分] [英] Denormalizing a DataFrame of company names [Part 2]

问题描述

这是我的上一篇文章的续篇公司名称.

This is the continuation of my previous post on denormalizing a DataFrame of company names.

我现在使用的修订表如下:

The revised table I'm now working with is the following:

import numpy as np
import pandas as pd

df = pd.DataFrame({'name' : ['Nitron', 'Pulset', 'Rotaxi'], 
                   'postal_code' : [1410, 1020, 1310], 
                   'previous_name1' : ['Rotory', np.NaN, 'Datec'],
                   'previous_name2' : [ np.NaN, 'Cmotor', np.NaN],
                   'previous_name3' : ['Datec', np.NaN, np.NaN],
                   'country' : ['BEL', 'ENG', 'JPN'], 
                   'city' : ['Brussels', np.NaN, np.NaN]
                  })

print(df)

| name   | postal_code | previous_name1 | previous_name2 | previous_name3 | country | city     |
|--------|-------------|----------------|----------------|----------------|---------|----------|
| Nitron | 1410        | Rotory         | NaN            | Datec          | BEL     | Brussels |
| Pulset | 1020        | NaN            | Cmotor         | NaN            | ENG     | NaN      |
| Rotaxi | 1310        | Cyclip         | NaN            | NaN            | JPN     | NaN      |

与我以前的文章相比，上面的DataFrame现在有另外两个列，分别是country和city系列.

Comparing to my previous post, the above DataFrame has now two additional columns, namely the country and city Series.

我的目标保持不变:使用country和city列，为所有以前的公司名称不丢失的实例添加新行，然后删除之前的名称系列.在外观上，非规范化"版本应如下所示:

My objective remains the same: add a new row for all instances where the previous company names is non-missing with the country and city columns and delete the previous names Series afterwards. Visually, the "denormalized" version should look like this:

| name   | postal_code | country | city     |
|--------|-------------|---------|----------|
| Nitron | 1410        | BEL     | Brussels |
| Rotory | 1410        | BEL     | Brussels |
| Datec  | 1410        | BEL     | Brussels |
| Pulset | 1020        | ENG     | NaN      |
| Cmotor | 1020        | ENG     | NaN      |
| Rotaxi | 1310        | JPN     | NaN      |
| Cyclip | 1310        | JPN     | NaN      |

花一些时间了解提供的代码对于上一个问题，我尝试修改/调整此新问题的解决方案，但没有成功.由于我对Python/Pandas生态系统还很陌生，因此我们将不胜感激任何其他帮助.

After spending some time understanding the the code provided by jezrael for my previous question, I tried to modify/adjust the solution for this new problem without success. Since I'm fairly new to the Python/Pandas ecosystem, any additional help would be greatly appreciated.

推荐答案

您可以在set_index中添加多列，并将level=1更改为level=3以删除MultiIndex的第四级:

You can add multiple columns in set_index and change level=1 to level=3 for remove forth level of MultiIndex:

df1 = (df.set_index(['postal_code','country','city'])
         .stack()
         .reset_index(level=3, drop=True)
         .reset_index(name='name')
         )
print (df1)
   postal_code country      city    name
0         1410     BEL  Brussels  Nitron
1         1410     BEL  Brussels  Rotory
2         1410     BEL  Brussels   Datec
3         1020     ENG       NaN  Pulset
4         1020     ENG       NaN  Cmotor
5         1310     JPN       NaN  Rotaxi
6         1310     JPN       NaN   Datec

第二种解决方案是在melt中添加多列:

And for second solution add multiple columns to melt:

df1 = (df.melt(['postal_code','country','city'], value_name='name')
         .drop('variable', axis=1)
         .dropna(subset=['name'])
         .reset_index( drop=True)
)
print (df1)
   postal_code country      city    name
0         1410     BEL  Brussels  Nitron
1         1020     ENG       NaN  Pulset
2         1310     JPN       NaN  Rotaxi
3         1410     BEL  Brussels  Rotory
4         1310     JPN       NaN   Datec
5         1020     ENG       NaN  Cmotor
6         1410     BEL  Brussels   Datec

这篇关于对公司名称的DataFrame进行非规范化处理[第2部分]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！