Combine multiple rows in a CSV using Pandas

Question

I have over a hundred CSV files that I will join with selections of a shapefile to represent visually in QGIS.

However, in these CSV files, the data is sometimes split into rows of 1A and 1B, or 13A and 13B, for example:

I can't perform the join with rows like this, because in the shapefile the data is never split into 1A and 1B:

For my purposes, I would like to add the content of 1B to 1A (which is different fields of vote numbers) and have a resulting single row (feature) called '1' where the number attributes are now the sums of the respective numbers in 1A and 1B. Another issue with the join of course is that 1A is a 'string', when I want them to be 'int'.

I don't think the 'one-to-many' join works because of the As and Bs.

I'm hoping there is a solution using Pandas that can run through a CSV file and accomplish this merge/summation for every instance of a row split into A and B. Then, I would have this run over a whole folder to perform the same thing on all my CSV files, like this:

for file in os.scandir(src_folder):
    if file.name.endswith('.csv'):
        #perform the merge-row code

Answer 1

You can do all this with Virtual Tables in QGIS.

Here's my test data, a table with no geometry:

Then I make a new Virtual Layer, and all I do is add this as the Query. It is SQL that converts the Numero field to a number (conveniently discarding any trailing letters) and adds the two vote columns grouping by the number.

That gives me this table which you can check has added the votes for the groups:

I notice your data doesn't seem to have any unique ID except the number so you probably don't want to include the Name in the output fields like I have. You'll end up with a table of Numero2 and the sums of the vote columns you put in the query.

But then you should be able to join to your spatial data on Numero2 in this with the corresponding item in the spatial data.

This might not be convenient if you have 100 of them though, in which case off-line processing and merging of the CSVs into one CSV would be best.

Answer 2

This is an example for one csv file. Then loop and merge the other csv files to gdf2

import re, os
import pandas as pd
import geopandas as gpd

csvfolder = r'/home/bera/Desktop/tempgis/'

for root, folder, files in os.walk(csvfolder):
    for file in files:
        fullpath = os.path.join(root, file)
        if os.path.isfile(fullpath) and fullpath.endswith('.csv'): 
            df = pd.read_csv(fullpath, delimiter=';')

df.fillna(0, inplace=True)
s = df.Numero.str.extract(r'(\d+)', expand=False) #Groupby first number in string: https://stackoverflow.com/questions/55001289/pandas-groupby-based-on-matching-substring-in-pandas-column
df2 = df.groupby(s)['A','B','C'].sum()
df2 = df2.reset_index()
df2.Numero = df2.Numero.astype(int)

gdf = gpd.read_file(r'/home/bera/GIS/Data/testdata/ak_riks.shp')

gdf2 = pd.merge(left=gdf, right=df2, how='left',on='Numero')

gdf2[['Numero','A','B','C']]
Out[12]: 
   Numero   A   B     C
0       1  30   3   3.0
1       2   1   2   5.0
2       3   2   3  43.0
3       4   2   5  54.0
4       5   4  13  48.0

You can also create one big csv file, then merge to your shapefile dataframe like above. I assume all your csv file have the same columns. To create the csv file:

import os
print('Start')
inpath=r'/home/bera/GIS/csvfolder/'
outfile=os.path.join(inpath,'bigcsvfile.csv')
filelist=[]
for root, dirs, files in os.walk(inpath):
    for file in files:
    fullpath = os.path.join(root, file)
        if os.path.isfile(fullpath) and fullpath.endswith('.csv'):
            filelist.append(fullpath)

fout=open(outfile,"a",encoding='utf8')
# first file:
for line in open(filelist[0]):
    fout.write(line)
# now the rest:
filelist.pop()
for csv in filelist:
    print('Proccessing file: {0}'.format(csv))
    f = open(csv)
    f.readline() # skip the header
    for line in f:
        fout.write(line)
fout.close()
print('Stop')