current position:Home>Using pandas to implement SQL group_ concat

Using pandas to implement SQL group_ concat

2022-01-31 13:20:05 PI dada

official account : Youer cottage
author :Peter
edit :Peter

Hello everyone , I am a Peter~

This article is a comparison SQL Study Pandas The third article of , It mainly explains how to use pandas To achieve SQL Medium group_concat operation .

group_concat

SQL perhaps MySQL Medium group_concat What function does it realize ? Look at an example to illustrate .

Here's the table information A simple piece of data stored in , Two fields id and name:

+----+-----+
| id | name |
+------+---+
|1  | 10   |
|1  | 20   |
|1  | 20   |
|2  | 20   |
|3  | 200  |
|3  | 500  |
 Copy code 

The way 1: By default

We use id To group , take name On the same line , Separated by commas :

select 
    id
    ,group_concat(name)  as name
from information 
group by id;
 Copy code 

The result is :

|id|  name|
|1 |10,20,20|
|2 |20      |
|3 |200,500|
 Copy code 

The way 2: The specified symbol

The default in the above result is comma ( English comma ) spaced , We can also specify our own symbols :

select 
    id
    ,group_concat(name separator ';')  as name
from information 
group by id;
 Copy code 

The result is displayed as :

|id|  name|
|1 |10;20;20|   --  Semicolons separate 
|2 |20      |
|3 |200;500|
 Copy code 

The way 3: To redisplay

We can also use id grouping , Will be redundant ( Repetitive ) Data removal , Then put the rest together ; such as id=1 The data is repeated 20, We want to show only one 20:

Add the key words distinct :

select 
    id
    ,group_concat(distinct name)  as name
from information 
group by id;
 Copy code 

The corresponding results are shown as :

|id|  name|
|1 |10,20|   --  Only one 20
|2 |20    |
|3 |200,500|
 Copy code 

The way 4: Descending order

In all the above cases , The data are arranged in ascending order , We can also descending :

select 
    id
    ,group_concat(name order by name desc)  as name  
from information 
group by id;
 Copy code 

So the result is :

--  The results are in descending order 

|id|  name|
|1 |20,20,10|
|2 |20    |
|3 |500,200|
 Copy code 

The above is a variety of group_concat To achieve the effect of , The following use pandas To achieve .

Analog data

import pandas as pd
import numpy as np
 Copy code 
df = pd.DataFrame({
  "name":[" Xiao Ming "," Xiao Ming "," Xiao Ming "," Xiaohong "," Xiao Zhang "," Xiao Zhang "],
  "score":[10,20,20,20,200,500]
})

df
 Copy code 

Very clear , We need to bring Xiao Ming 、 Xiaohong 、 Zhang's score Group together .

The way 1: Default group

Implement the default grouping scenario , Arrange in ascending order without de duplication . Mainly 3 A step :

1、 adopt groupby Grouping

2、 After grouping, pass list take score All in one list

3、 The third step is just index rearrangement

The way 2: The specified symbol

Specify a specific symbol , What we use join function . Because this function can only operate on strings , So we need to put df Convert numeric data in to a string :

df.astype(str)
 Copy code 

The way 3: To redisplay

adopt name Fields are grouped , Right again score use unique function . Here's just a rearrangement

The way 4: Descending order

1、 Let's first implement the default ascending arrangement

2、 Yes score Field is used again apply function , By using... On the list sorted Function to implement descending arrangement

dear friend , Have you learned ?

copyright notice
author[PI dada],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/01/202201311320040714.html

Random recommended