Pandas: aggregate several columns of different dataframes using partial string matching

user_9876904 0
Viewed 85

EDIT: I've now been able to solve it

I want to aggregate observations from one dataframe using a second dataframe, and need to take into account weights and partial string matching.

I've made a dataframe that shows me how often a patent in a certain category has been applied for in a year, which looks like this:

    IPC_four    count_year_IPC_four
Year        
1955    A01B    9
1955    G01P    3
1955    B23D    4
1955    G01R    28
1955    B23C    1
...     ...     ...
1990    A21D    1
1990    G01F    17
1990    G06K    8
1990    F21P    0
1990    H05K    23

23868 rows × 2 columns

(I also have a dataframe of all the individual patents but I've aggregated that one in a slightly inelegant manner by using pd.crosstab() and pd.unstack())

I want to aggregate these using my second table, which is a correspondence matrix from IPC classes to industry branches. Importantly, because some IPC classes correspond to more than one economic sector, there is a "Factor" column with which the instances of the first column need to be multiplied.

df2:
        Branch  Code    Factor
0       20      E21     1.0
1       21      E01     1.0
2       21      E02     1.0
3       2       A21     1.0
4       2       A22     1.0
...     ...     ...     ...
210     7       G10     1.0
211     7       A47B    1.0
212     7       A47C    1.0
213     7       A47D    1.0
214     7       A47F    1.0

215 rows × 3 columns

The new dataframe should be a weighted sum of my numbers from df1 with the corresponding Year and Industry Branch. It should look like like this:

        Branch  count_Branch_IPC_four
Year        
1955    2       9
1955    3       3
1955    4       4
1955    5       28
1955    6       1
...     ...     ...
1990    2       1
1990    3       17
1990    4       8
1990    5       0
1990    6       23

576 rows x 3 columns

In the following, I've illustrated my thought process for the different steps that I thing would be necessary to fill one row of the dataframe that I want to get in the end:

  1. Since everything needs to be aggregated by year I need to first get my value of df1[Year] (I think?)

  2. I want go look at the df2[Branch] from my second dataframe, and consider what values of df2[Code] correspond to that branch.

  3. Then I want to take each value in df2[Code] that corresponds to that value of df2[Branch] and check what values of df1[IPC_four] (in the given year) fit into this.

    • Sometimes the Code has three digits, sometimes it has four. If it has only three digits, I need to check this using some kind of partial string matching.

    • Since the codes denote classes and subclasses, and a shorter code just selects a larger set of all subclasses than a longer one. Hence why I want to do partial string matching. If that's what breaks me I could also consider adding extra rows with the possible permutations of four-digit strings, but this is also not easy

  4. For each of the fitting df1[IPC_four], (it could be more than one) I want to create the sum of all the the corresponding df1[count_year_IPC_four] values. This sum of count_year_IPC_four then has to be multiplied by Factor that corresponds to the Code we've been using. (from DF2).

Of course the iteration is a bit confusing, but I'm mostly puzzled on how I can do these complicated operations by essntially using the row values from different dataframes.

I tried splitting up the dataframes by year and branch, by creating a dictionary of dataframes.

df = pd.read_pickle("count_year_IPCfour.pkl")
b = pd.read_pickle("branchlist.pkl")

branches = (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 20, 21)
year_range = range(1955, 1990)
year_collection = {}
branch_collection = {}


for year in year_range:
    new_df = df.loc[df['Year'] == year ]
    year_collection[year] = pd.DataFrame(new_df, columns = [ "Branch", "IPC_four", "Code", "count_year_IPC_four", "Factor"])

for branch in branches:
    new_df = b.loc[b['Branch'] == branch ]
    branch_collection[branch] = pd.DataFrame(new_df, columns = [ "Branch", "IPC_four", "Code", "count_year_IPC_four", "Factor"])

But now I'm still stumped about what to do, because fundamentally I cannot wrap my head about the operations required in terms of moving along rows and across columns and across dataframes.

Note: because of the duplication (i.e. some IPC_four codes belong to more than one Branch) and the Partial String Matching issue (some Codes are three-digit, some are four) I can't just do a pd.join() operation - I've tried, but the duplicates meant the df grew a lot.

How should I implement this? If someone has already answered this or this can be split into multiple parts, I would be happy to look at those. Thank you very much.

pandas aggregation matching dataframe
1 Answers

I managed to solve it.

Since I do my stuff in Jupyter I usually have very short code snippets that I do little tasks with, which is why you see this very weird code structure here. On that note, I would like to ask: is it good practice to keep going up with your dataframe names? or should you just overwrite earlier ones?

  1. Create a variable = 1 that you can .groupby(Year, Branch).sum() with
df = pd.read_pickle("DEPATISclean002.pkl")
df["IPC_four"].astype("str")
df["empty"] = 1 #I can just sum 1 for each year and each sector
df.rename(columns={"Anmeldejahr" : "Year"}, inplace = True)
df = df[["Year", "IPC_four", "count_inventor", "empty"]]
df = df.groupby(["Year", "IPC_four"]).sum() #now I have a df in which I have both the absolute number of patents and absolute no. of inventors - this now needs to be 
df["inv_year_IPC_four"] = df["count_inventor"]/df["empty"] #in addition I have the avg. no of Inventors per patent per IPC_four per year
df.reset_index(inplace = True)
df.to_pickle("Aggregation001.pkl")
  1. Now, we need to create new branch lists based on the length of the IPC code that we want to assign to a branch
df = pd.read_pickle("branchlist.pkl")
df["Code_len"] = df["Code"].str.len()
df = df[["Branch", "Code", "Factor", "Code_len"]]
df.to_pickle("branchlist002.pkl")
##make a len=4 one
df = df[df.Code_len == 4]
df["IPC_four"] = df["Code"].astype("str")
df = df[["Branch", "IPC_four", "Factor"]]
df.to_pickle("branchlist_len4.pkl")

##make a len=3 one
df2 = pd.read_pickle("branchlist002.pkl")
df2 = df2[["Branch", "Code", "Factor", "Code_len"]]
df2 = df2[df2.Code_len == 3]
df2["IPC_three"] = df2["Code"].astype("str")
df2 = df2[["Branch", "IPC_three", "Factor"]]
df2.to_pickle("branchlist_len3.pkl")

With 4 digit codes, it was very easy to do a count

df1 = pd.read_pickle("Aggregation001.pkl")
df2 = pd.read_pickle("branchlist_len4.pkl")
df3 = pd.merge(df1, df2[df2.IPC_four.isin(df1.IPC_four)], how= "left", on="IPC_four")
df4 = df3[df3["Factor"].notnull()
          
#now to create a weighted list of inventors and patents by year and sector

df4["Branch_weighted"] = df4["empty"]*df4["Factor"]
df4["count_inventor_weighted"] = df4["count_inventor"]*df4["Factor"]
df5 = df4.groupby(["Year", "Branch"]).sum()
df5["inv_year_Branch"] = df5["count_inventor_weighted"]/df5["Branch_weighted"]
df5.reset_index(inplace = True)
df6 = df5[["Year", "Branch", "Branch_weighted", "inv_year_Branch", "count_inventor_weighted"]]
df6.to_pickle("Agg4.pkl")

Then I tried my hand at the len=3 stuff, and it turned out relatively simple after finding an article on Medium about it: https://outline.com/VpFnwf

df1 = pd.read_pickle("Aggregation001.pkl")
df2 = pd.read_pickle("branchlist_len3.pkl")
df2 = df2[["IPC_three", "Branch", "Factor"]]

#making sure everything is a string
df1["IPC_four"].astype("str")
df2["IPC_three"].astype("str")

#creating a thing to join on 
df1["join"] = 1
df2["join"] = 1

#merging as a cartesian product
df3 = df1.merge(df2, on = "join").drop("join", axis = 1)
df2.drop('join', axis=1, inplace=True)
df3['match'] = df3.apply(lambda x : x.IPC_four.find(x.IPC_three), axis=1).ge(0)
df3
df4 = df3[df3.match == True]
df4

df4["Branch_weighted"] = df4["empty"]*df4["Factor"]
df4["count_inventor_weighted"] = df4["count_inventor"]*df4["Factor"]
df4.to_pickle("weighted_len3.pkl")
df5 = df4.groupby(["Year", "Branch"]).sum()
df5["inv_year_Branch"] = df5["count_inventor_weighted"]/df5["Branch_weighted"]
df5.reset_index(inplace = True)
df6 = df5[["Year", "Branch", "Branch_weighted", "inv_year_Branch", "count_inventor_weighted"]]
df6.to_pickle("Agg3.pkl")

And then finally it's just a matter of doing pd.groupby.sum()

df1 = pd.read_pickle("Agg3.pkl")
df2 = pd.read_pickle("Agg4.pkl")
df3 = df1.append(df2)
df4 = df3.groupby(["Year", "Branch"]).sum()
df4["inv_year_Branch"] = df4["count_inventor_weighted"]/df4["Branch_weighted"]
df4.reset_index(inplace = True)
df4.to_pickle("Patents_per_Branch_per_Year.pkl")

I really hope someone else can enjoy this answer! Also, if you know of some easier or better ways to do this, please do share! I'm sure this kind of transformation is rather common, so it's good if it can be found on Stackoverflow.

user_98769040
Related