I have read many, many similar answers but all solutions have sadly returned no results.
I am importing many CSV files from a folder using Glob and hoping to combine them into one Pandas Dataframe. However, with my current solution, although the columns are perfect, many duplicate rows (duplicate in terms of Player Name & Team) exist.
My current code is as follows:
# Imports
import pandas as pd
from glob import glob
# Concat Code
df = pd.concat(map(pd.read_csv, sorted(glob(f"{Year}*.csv"), key=len)), join='outer', ignore_index=True, axis=0)
My current output is:
| Player Name | Team | Round 1 | Round 2 |
|---|---|---|---|
| Player A | Team A | 100 | NaN |
| Player B | Team B | 200 | NaN |
| Player C | Team A | NaN | NaN |
| Player A | Team A | NaN | 110 |
| Player B | Team B | NaN | 210 |
| Player C | Team A | NaN | NaN |
My ideal output:
| Player Name | Team | Round 1 | Round 2 |
|---|---|---|---|
| Player A | Team A | 100 | 110 |
| Player B | Team B | 200 | 210 |
| Player C | Team A | NaN | NaN |
I have tried using the 'groupby', 'agg', 'merge' instead of 'concat' and setting the index but with no success. I will need to keep the original intended NaN values and distinguish them from 0 for the data application.
Thank you for taking the time to help me with this!