I have the following data:
plate,part,posX,posY,rotation
1,FSHN01-R-58.stl,14.5,9.5,180
1,FSHN01-R-58.stl,14.5,9.5,180
1,FSHN01-E-2.stl,44.5,6.5,270
1,FSHN01-N-3.stl,88,7,0
2,FSHN01-N-7.stl,70.5,70.5,90
2,FSHN01-N-1.stl,128.5,64.5,180
2,FSHN01-N-1.stl,113.5,69.5,90
7,FSHN01-R-58.stl,14.5,9.5,180
7,FSHN01-R-58.stl,14.5,9.5,180
7,FSHN01-E-2.stl,44.5,6.5,270
7,FSHN01-N-3.stl,88,7,0
I want to group plates by part and find same plates. For example on this data 1,7 have same parts. posX, posY, rotation its not important for me and i need to find part counts of each plates.
For this, i wrote this code:
import pandas as pd
df = pd.read_csv("public/33/stls/plates.csv")
result = df.groupby(['plate', 'part']).size()
print(result)
and i got.
plate part
1 FSHN01-E-2.stl 1
FSHN01-N-3.stl 1
FSHN01-R-58.stl 2
2 FSHN01-N-1.stl 2
FSHN01-N-7.stl 1
7 FSHN01-E-2.stl 1
FSHN01-N-3.stl 1
FSHN01-R-58.stl 2
so, 1. and 7. plates are same, how can i drop 7. plate from table and increase 1. plate plate count variable.
i need this result;
plate part part count plate count
1 FSHN01-E-2.stl 1 2
FSHN01-N-3.stl 1 2
FSHN01-R-58.stl 2 2
2 FSHN01-N-1.stl 2 1
FSHN01-N-7.stl 1 1
CodePudding user response:
IIUC, you need two groupby
, one with the default plate/part, and one in which you have substituted the plate values 7 by 1:
synonyms = {7: 1}
(df.groupby(['plate', 'part']).size().to_frame(name='part_count')
.join(df.assign(plate2=df['plate'].replace(synonyms))
.groupby(['plate2', 'part'])['plate'].agg(plate_count='nunique')
.rename_axis(['plate', 'part']),
how='inner'
)
.reset_index()
)
or in a more linear form:
synonyms = {7: 1}
df2 = df.groupby(['plate', 'part']).size().to_frame(name='part_count')
df2['plate_count'] = (df.assign(plate2=df['plate'].replace(synonyms))
.groupby(['plate2', 'part'])['plate'].nunique()
)
df2 = df2.dropna().reset_index()
output:
plate part part_count plate_count
0 1 FSHN01-E-2.stl 1 2
1 1 FSHN01-N-3.stl 1 2
2 1 FSHN01-R-58.stl 2 2
3 2 FSHN01-N-1.stl 2 1
4 2 FSHN01-N-7.stl 1 1
NB. Note that you voluntarily lose the information on the part counts of plate 7 here. If you want to keep it, map 7 to 1 as shown above and perform a single GroupBy.agg
CodePudding user response:
import pandas as pd
df = pd.read_csv("plates.csv")
df = df.groupby(['plate', 'part']).size().reset_index(name='count')
df_dict = df.groupby('plate')[['part', 'count']].apply(lambda x: x.to_dict('records')).to_dict()
result, plate_count = {}, {}
for key, value in df_dict.items():
if value not in result.values():
result[key] = value
plate_count[key] = 1
else:
plate_count[list(result.keys())[list(result.values()).index(value)]] = 1
print(result)
print(plate_count)
and the output I got:
{1: [{'part': 'FSHN01-E-2.stl', 'count': 1}, {'part': 'FSHN01-N-3.stl', 'count': 1}, {'part': 'FSHN01-R-58.stl', 'count': 2}], 2: [{'part': 'FSHN01-N-1.stl', 'count': 2}, {'part': 'FSHN01-N-7.stl', 'count': 1}]}
{1: 2, 2: 1}
I found a way for fix this issue but i could not fix my problem with pandas and i used dict. I wonder about the better way, anyone can find it ?