CODEWITHSUNDEEP
pythonpandas
dwtorres
dwtorres

Reputation: 309

Pandas and complicated filtering and merge/join multiple sub-data frames

I have a seemingly complicated problem and I have a general idea of how I should solve it but I am not sure if it is the best way to go about it. I'll give the scenario and would appreciate any help on how to break this down. I'm fairly new with Pandas so please excuse my ignorance.

The Scenario

I have a CSV file that I import as a dataframe. My example I am working through contains 2742 rows × 136 columns. The rows are variable but the columns are set. I have a set of 23 lookup tables (also as CSV files) named per year, per quarter (range is 2020 3rd quarter - 2015 1st quarter) The lookup files are named as such: PPRRVU203.csv. So that contains values from the 3rd quarter of 2020. The lookup tables are matched by two columns ('Code' and 'Mod') and I use three values that are associated in the lookup.

I am trying to filter sections of my data frame, pull the correct values from the matching lookup file, merge back into the original subset, and then replace into the original dataframe.

Thoughts

I can probably abstract this and wrap in a function but not sure how I can place back in. My question, for those that understand Pandas better than myself, what is the best method to filter, replace the values, and write the file back out.

The straight forward solution would be to filter the original dataframe into 23 separate dataframes, then do the merge on each individual file, then concat into a new dataframe and output to CSV.

This seems highly inefficient?

I can post code but I am looking for more of any high-level thoughts?

Upvotes: 0

Views: 274

Answers (1)

Cmagelssen
Cmagelssen

Reputation: 670

Not sure exactly how your DataFrame looks like but Pandas.query() method will maybe prove useful for the selection of data.

name = df.query('columnname == "something"')

Upvotes: 1

Related Questions